CODEWITHSUNDEEP
c#oracle-databaseplsqloracle-manageddataaccess
anon
anon

Reputation: 87

Oracle package invalid after C# call to replace

I assume that I'm doing something wrong in how I run SQL scripts from C#, but after significant searching on the Internet, I still don't know what is wrong...

I am having problems in loading an Oracle package and package body via C#. When I load the package in SQL*PLUS via the script, subsequent calls to functions in the script work correctly. And when I call it from C# it also works. However, when I load the same script from C#, the running of the script appears to work, but subsequent calls to the package's functions (both from C# and SQL*PLUS) fail with a PLS-00905 error ("object ANON.MY_PKG is invalid").

The contents of the SQL script ("simple.sql") is:

CREATE OR REPLACE PACKAGE my_pkg IS
    FUNCTION my_function (
        p_1 IN VARCHAR2
    )RETURN VARCHAR2;
END my_pkg;
/
CREATE OR REPLACE PACKAGE BODY my_pkg IS
    FUNCTION my_function (
        p_1 IN VARCHAR2
    ) RETURN VARCHAR2 AS p_result VARCHAR2(2000);
        BEGIN
        RETURN p_1;
    END my_function;
END my_pkg;
/

Running it in SQL*PLUS works fine...

SQL> SET SERVEROUTPUT ON
SQL> @"D:\_temp\simple.sql"

Package created.


Package body created.

SQL> EXEC DBMS_OUTPUT.PUT_LINE(my_pkg.my_function('hello'));
hello

PL/SQL procedure successfully completed.

Then calling the function in a C# program (as the same Oracle user as used in SQL*PLUS) works fine until the SQL script is re-run within the program.

using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestApplication
{
    class Program
    {
        static void Main(string[] args)
        {

                OracleConnection conn = getConnection();

                Debug.WriteLine("first call to my_function:");
                callMyFunction(conn);
                loadMyPackage(conn);

                try
                {
                Debug.WriteLine("second call to my_function:");
                callMyFunction(conn);
            }
            catch (Exception ex)
            {
                Debug.WriteLine("ex = " + ex.ToString());
            }

        }

        private static OracleConnection getConnection()
        {
            string connStr = "redacted...";
            OracleConnection conn = new OracleConnection();
            conn.ConnectionString = connStr;
            conn.Open();
            return conn;
        }

        private static void loadMyPackage(OracleConnection conn)
        {
            OracleCommand command = new OracleCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = File.ReadAllText(@"D:\_temp\simple.sql");
            command.Connection = conn;
            int response = command.ExecuteNonQuery();
            Debug.WriteLine("response = " + response);
        }

        private static void callMyFunction(OracleConnection conn)
        {
            int RETURN_BUFFER_SIZE = 32767;
            OracleCommand cmd = new OracleCommand();

            cmd.Connection = conn;
            cmd.CommandText = "my_pkg.my_function";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("returnVal", OracleDbType.Varchar2, RETURN_BUFFER_SIZE);
            cmd.Parameters["returnVal"].Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add("p_1", OracleDbType.Varchar2);
            cmd.Parameters["p_1"].Value = "hello";

            cmd.ExecuteNonQuery();
            string result = cmd.Parameters[0].Value.ToString();
            Debug.WriteLine("function result = " + result);
        }
    }
}

After the reload ("loadMyPackage") it errors. Specifically:

first call to my_function:
function result = hello
response = -1
second call to my_function:
A first chance exception of type 'Oracle.ManagedDataAccess.Client.OracleException' occurred in Oracle.ManagedDataAccess.dll
ex = Oracle.ManagedDataAccess.Client.OracleException (0x00001996): ORA-06550: line 1, column 15:
PLS-00905: object ANON.MY_PKG is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
   at TestApplication.Program.callMyFunction(OracleConnection conn) in c:\Users\ANON\Documents\Visual Studio 2013\Projects\myProject\TestApplication\Program.cs:line 73
   at TestApplication.Program.Main(String[] args) in c:\Users\ANON\Documents\Visual Studio 2013\Projects\myProject\TestApplication\Program.cs:line 29
The thread 0x2838 has exited with code 259 (0x103).
The thread 0x2d9c has exited with code 259 (0x103).
The program '[11268] TestApplication.vshost.exe' has exited with code 0 (0x0).

How can I get C# to run the script properly?

Update: Based on feedback, I split the SQL script into two parts. The first part ("simple_A.sql") is now:

CREATE OR REPLACE PACKAGE my_pkg IS
    FUNCTION my_function (
        p_1 IN VARCHAR2
    )RETURN VARCHAR2;
END my_pkg;
/

And the second part ("simple_B.sql") is:

CREATE OR REPLACE PACKAGE BODY my_pkg IS  -- body
    FUNCTION my_function (
        p_1 IN VARCHAR2
    ) RETURN VARCHAR2 AS p_result VARCHAR2(2000);
        BEGIN
        RETURN p_1;
    END my_function;
END my_pkg;
/

After this, change, I verified that it still works in SQL*PLUS via:

SQL> drop package my_pkg;

Package dropped.

SQL> @"D:\_temp\simple_A.sql"

Package created.

SQL> @"D:\_temp\simple_B.sql"

Package body created.

SQL> EXEC DBMS_OUTPUT.PUT_LINE(my_pkg.my_function('hello'));
hello

PL/SQL procedure successfully completed.

SQL>

I then update the C# code to use the two scripts via the following change:

private static void loadMyPackage(OracleConnection conn)
{
    OracleCommand command_A = new OracleCommand();
    command_A.CommandType = CommandType.Text;
    command_A.CommandText = File.ReadAllText(@"D:\_temp\simple_A.sql");
    command_A.Connection = conn;
    int response = command_A.ExecuteNonQuery();
    Debug.WriteLine("response A = " + response);

    OracleCommand command_B = new OracleCommand();
    command_B.CommandType = CommandType.Text;
    command_B.CommandText = File.ReadAllText(@"D:\_temp\simple_B.sql");
    command_B.Connection = conn;
    response = command_B.ExecuteNonQuery();
    Debug.WriteLine("response B = " + response); 
}

However, I still get the same error. Specifically, the output is now the following:

first call to my_function:
function result = hello
response A = -1
response B = -1
second call to my_function:
A first chance exception of type 'Oracle.ManagedDataAccess.Client.OracleException' occurred in Oracle.ManagedDataAccess.dll
ex = Oracle.ManagedDataAccess.Client.OracleException (0x00001996): ORA-06550: line 1, column 15:
PLS-00905: object ANON.MY_PKG is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
...
The thread 0x4f0 has exited with code 259 (0x103).
The thread 0x3310 has exited with code 259 (0x103).
The program '[10728] TestApplication.vshost.exe' has exited with code 0 (0x0).

Update #2: Based on Justin's comments, I have split my sql script into two separate files, and removed the '/' characters from them. They now are:

simple_A.sql:

CREATE OR REPLACE PACKAGE my_pkg IS
    FUNCTION my_function (
        p_1 IN VARCHAR2
    )RETURN VARCHAR2;
END my_pkg;

simple_B.sql

CREATE OR REPLACE PACKAGE BODY my_pkg IS  -- body
    FUNCTION my_function (
        p_1 IN VARCHAR2
    ) RETURN VARCHAR2 AS p_result VARCHAR2(2000);
        BEGIN
        RETURN p_1;
    END my_function;
END my_pkg;

With these changes, and the ones from the first update, the code worked correctly.

After getting the code to work correctly, I added one additional bit based on another of Justin's comments, which was a means of identifying the cause of the errors. Specifically I added a printErrors function, and added calls to it in my C# code. The additions and modifications to the code are:

private static void loadMyPackage(OracleConnection conn)
{
    OracleCommand command_A = new OracleCommand();
    command_A.CommandType = CommandType.Text;
    command_A.CommandText = File.ReadAllText(@"D:\_temp\simple_A.sql");
    command_A.Connection = conn;
    int response = command_A.ExecuteNonQuery();
    printErrors(conn);
    Debug.WriteLine("response A = " + response);

    OracleCommand command_B = new OracleCommand();
    command_B.CommandType = CommandType.Text;
    command_B.CommandText = File.ReadAllText(@"D:\_temp\simple_B.sql");
    command_B.Connection = conn;
    response = command_B.ExecuteNonQuery();
    printErrors(conn);
    Debug.WriteLine("response B = " + response); 
}

    private static void printErrors(OracleConnection conn)
    {
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT name, text FROM user_errors";
        cmd.CommandType = CommandType.Text;
        OracleDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            Debug.WriteLine("user_error: " + dr.GetString(0) + ": " + dr.GetString(1) );
        }
    }

With the printErrors code added, the cause of the errors becomes easy to see. The bad SQL version of the first update produces the following output:

...
first call to my_function:
function result = hello
user_error: MY_PKG: PLS-00103: Encountered the symbol "/" The symbol "/" was ignored.

response A = -1
...

[Note: the above error messages is from the final code when given sql scripts with errors. The final version of the sql scripts has no errors.]

So, not only did this code get working, but it now has an ability to provide descriptive error information in the cases where it fails.

Upvotes: 0

Views: 706

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Assuming your file looks something like this with two DDL statements and no SQL*Plus commands

CREATE OR REPLACE PACKAGE package_name
  ...
END;
/

CREATE OR REPLACE PACKAGE BODY package_name
  ...
END;
/

there are two issues.

First, those are two separate DDL statements so they would have to be executed via two separate ExecuteNonQuery calls. You could either split the single file into files, one per DDL statement, or you could parse the individual statements from the file in your C# code.

Second, each DDL statement has a / character at the end. That is a separator for SQL*Plus so that it knows when your statement is complete, it is not part of the DDL statement. You could either remove that in C# before executing the statement or you could remove the statement from the file and create a separate SQL*Plus script that references the individual statement files and includes appropriate separators.

Upvotes: 1

Related Questions