How to execute anonymous PL/SQL in C# using ODP.NET 4.0?

Question

User-1415763228 posted

I have had no luck.  I googled and most of them seems to be talking about creating a stored procedure, but I don't have this option, I have to use plain SQL against my Oracle 10g database.  

namespace MyNameSpace
{
    using System.Data;
    using Oracle.DataAccess.Client;
    using System;

    public class MyClass
    {
        private static string connectionString = "my connection string to my oracle 10g database";
        private static string query = @"BEGIN 
                                    OPEN :p1 FOR SELECT FirstName, LastName FROM Customers;
                                    OPEN :p2 FOR SELECT SSN, FirstName, LastName, ManagerId FROM Employees;
                                    OPEN :p3 FOR SELECT EquipmentId, Name, Location, CheckoutDate FROM Resources;
                                    END;";

        public static DataSet ExecuteAnonymousPlSql
        {
            DataSet data = new DataSet();
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                OracleCommand command = new OracleCommand(query, connection);
                command.CommandType = CommandType.Text;

                command.Parameters.Add("p1", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
                command.Parameters.Add("p2", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
                command.Parameters.Add("p3", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);

                OracleDataAdapter adapter = new OracleDataAdapter(command);
                adapter.Fill(data); 
                connection.Close();
            }

            return data;
        }
    }
}

At this line : adapter.Fill(Data), I get an error which says:

ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "" when expecting one of the following:

   begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe
The symbol "" was ignored.
ORA-06550: line 2, column 175:
PLS-00103: Encountered the symbol "" when expecting one of the following:

   begin case declare end exception exit for goto if loop mod
   null pragma raise return select update while with
   <an identifier> <a double-quoted delimited-i
ORA-06550: line 3, column 101:
PLS-00103: Encountered the symbol "" when expecting one of the following:

   begin case declare end exception exit for goto if loop mod
   null pragma raise return select update while with
   <an identifier> <a double-quoted delimited-i
ORA-06550: line 4, column 141:
PLS-00103: Encountered the symbol

Oracle's error message doesn't help much.  I am curious that I can't even dig out a working example online about executing an anonymous pl/sql in C# using ODP.NET 4.0.  Any hint?

Also, how do I correctly add the parameters? Given the PL/SQL I have above, which of the following is correct?

  command.Parameters.Add("p1", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);   

  command.Parameters.Add(":p1", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);

Answer 1

User-2005691517 posted

Try this

private static string query = @"BEGIN 
                                    OPEN :1 FOR SELECT FirstName, LastName FROM Customers;
                                    OPEN :2 FOR SELECT SSN, FirstName, LastName, ManagerId FROM Employees;
                                    OPEN :3 FOR SELECT EquipmentId, Name, Location, CheckoutDate FROM Resources;
                                    END;";
This is correct

command.Parameters.Add("p1", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);

Answer 2

User-1415763228 posted

Apparently, after many hours of test, I found out that the Oracle ODP for .NET doesn't like the C# @ sign for string literals.  Try it and you will know.  Data access fails if you define a string literal with the @ sign, otherwise, it succeeds.  Dumb Oracle. It totally wasted me about a day.  

Answer 3

User-1415763228 posted

Try this

private static string query = @"BEGIN 
                                    OPEN :1 FOR SELECT FirstName, LastName FROM Customers;
                                    OPEN :2 FOR SELECT SSN, FirstName, LastName, ManagerId FROM Employees;
                                    OPEN :3 FOR SELECT EquipmentId, Name, Location, CheckoutDate FROM Resources;
                                    END;";
This is correct

command.Parameters.Add("p1", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);

Thanks, but your answer does not solve the problem.  If you could, please test before you answer. Thanks.

Answer 4

User1681169739 posted

I know it's an old question, but just for someone else's reference, the problem there is the new line character inserted in the script by the use of @.

Oracle 10g only recognizes the \n character as newLine. This problem has been solved in 11g.

Try adding something like this to fix your code:

string cmdQuery = query.Replace(Environment.NewLine, "\n");
OracleCommand command = new OracleCommand(cmdQuery, connection);

Reference: http://www.intertech.com/Blog/executing-sql-scripts-with-oracle-odp/

Answer 5

User364663285 posted

I suggest you run an Oracle SP for doing the same.