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

• • Reply
  

  antonyliu200...

  Member

  168 Points

  310 Posts

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

  Dec 29, 2011 03:41 PM|LINK

  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);

• • Reply
  

  Prashant Kum...

  Star

  12346 Points

  1993 Posts

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

  Dec 29, 2011 11:38 PM|LINK

  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);

  Prashant
• • Reply
  

  antonyliu200...

  Member

  168 Points

  310 Posts

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

  Dec 30, 2011 02:22 PM|LINK

  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.  

• • Reply
  

  antonyliu200...

  Member

  168 Points

  310 Posts

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

  Dec 30, 2011 02:42 PM|LINK

  Prashant Kumar

  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.