title: How to use an Oracle Ref Cursor from C# ODP.NET as a ReturnValue Parameter, without using a Stored Function or Procedure?
body: <p>I need help understanding if the way I'm trying to use a Ref Cursor as a ReturnValue Parameter for multiple records/values, with the PL/SQL just being the CommandText of an OracleCommand object and not in a Stored Procedure or Function, is even possible.</p>
  
  <p>If that is not possible, what I'm trying to do is find a way to issue a PL/SQL statement that will Update an unknown number of records (depends on how many match the WHERE clause), and return the Ids of all the records Updated in an OracleDataReader, using a single round-trip to the database, without the use of a Stored Procedure or Function.</p>
  
  <p>I'm working with Oracle 11g using ODP.NET for communication with an existing C# .NET 4.0 code-base that uses the SQL connection to retrieve/modify data. The simplified test table definition I'm using looks like so:</p>
  
  <pre><code>CREATE TABLE WorkerStatus
  (
      Id                  NUMERIC(38)         NOT NULL
      ,StateId            NUMERIC(38)         NOT NULL
      ,StateReasonId      NUMERIC(38)         NOT NULL
      ,CONSTRAINT PK_WorkerStatus PRIMARY KEY ( Id )
  )
  </code></pre>
  
  <p>I pre-populate the table with three test values like so:</p>
  
  <pre><code>BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO WorkerStatus (Id, StateId, StateReasonId)
                          VALUES (1, 0, 0)';
      EXECUTE IMMEDIATE 'INSERT INTO WorkerStatus (Id, StateId, StateReasonId)
                          VALUES (2, 0, 0)';
      EXECUTE IMMEDIATE 'INSERT INTO WorkerStatus (Id, StateId, StateReasonId)
                          VALUES (3, 0, 0)';
  END;
  </code></pre>
  
  <p>The existing SQL statement, loaded from a script file named Oracle_UpdateWorkerStatus2, and contained in the OracleCommand.CommandText looks like so:</p>
  
  <pre><code>DECLARE
      TYPE id_array IS TABLE OF WorkerStatus.Id%TYPE INDEX BY PLS_INTEGER;    
  
      t_ids   id_array;
  BEGIN
      UPDATE WorkerStatus
      SET
           StateId = :StateId
          ,StateReasonId = :StateReasonId
      WHERE
          StateId = :CurrentStateId
      RETURNING Id BULK COLLECT INTO t_Ids;
      SELECT Id FROM t_Ids;
  END;
  </code></pre>
  
  <p>I've created a small C# test program to attempt to isolate where I'm getting an ORA-01036 "illegal variable name/number" error that has a main body that looks like so:</p>
  
  <pre><code>using System;
  using System.Configuration;
  using System.Data;
  using System.Text;
  using Oracle.DataAccess.Client;
  using Oracle.DataAccess.Types;
  namespace OracleDbTest
  {
    class Program
    {
      static void Main(string[] args)
      {
          // Load the SQL command from the script file.
          StringBuilder sql = new StringBuilder();
          sql.Append(Properties.Resources.Oracle_UpdateWorkerStatus2);
  
          // Build and excute the command.
          OracleConnection cn = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleSystemConnection"].ConnectionString);
          using (OracleCommand cmd = new OracleCommand(sql.ToString(), cn))
          {
              cmd.BindByName = true;
              cn.Open();
  
              OracleParameter UpdatedRecords  = new OracleParameter();
              UpdatedRecords.OracleDbType     = OracleDbType.RefCursor;
              UpdatedRecords.Direction        = ParameterDirection.ReturnValue;
              UpdatedRecords.ParameterName    = "rcursor";
  
              OracleParameter StateId         = new OracleParameter();
              StateId.OracleDbType            = OracleDbType.Int32;
              StateId.Value                   = 1;
              StateId.ParameterName           = "StateId";
  
              OracleParameter StateReasonId   = new OracleParameter();
              StateReasonId.OracleDbType      = OracleDbType.Int32;
              StateReasonId.Value             = 1;
              StateReasonId.ParameterName     = "StateReasonId";
  
              OracleParameter CurrentStateId  = new OracleParameter();
              CurrentStateId.OracleDbType     = OracleDbType.Int32;
              CurrentStateId.Value            = 0;
              CurrentStateId.ParameterName    = "CurrentStateId";
  
              cmd.Parameters.Add(UpdatedRecords);
              cmd.Parameters.Add(StateId);
              cmd.Parameters.Add(StateReasonId);
              cmd.Parameters.Add(CurrentStateId);
  
              try
              {
                  cmd.ExecuteNonQuery();
                  OracleDataReader dr = ((OracleRefCursor)UpdatedRecords.Value).GetDataReader();
                  while (dr.Read())
                  {
                      Console.WriteLine("{0} affected.", dr.GetValue(0));
                  }
                  dr.Close();
              }
              catch (OracleException e)
              {
                  foreach (OracleError err in e.Errors)
                  {
                      Console.WriteLine("Message:\n{0}\nSource:\n{1}\n", err.Message, err.Source);
                      System.Diagnostics.Debug.WriteLine("Message:\n{0}\nSource:\n{1}\n", err.Message, err.Source);
                  }
              }
              cn.Close();
          }
          Console.WriteLine("Press Any Key To Exit.\n");
          Console.ReadKey(false);
      }
    }
  }
  </code></pre>
  
  <p>I've tried changing the parameter names, naming and not-naming the UpdatedRecords parameter, changing the order so the UpdatedRecords is first or last.  The closest thing I've found so far is the following StackOverflow question (<a href="https://stackoverflow.com/questions/6360244/how-to-call-an-oracle-function-with-a-ref-cursor-as-out-parameter-from-c">How to call an Oracle function with a Ref Cursor as Out-parameter from C#?</a>), but that still uses a Stored Function as far as I can tell.</p>
  
  <p>Running the Oracle_UpdateWorkerStatus2 PL/SQL script from SQL Developer, it opens the "Enter Binds" dialog where I enter the values for CurentStateId, StateId and StateReasonId  as in the code above, but it gives the following error report:</p>
  
  <pre><code>Error report:
  ORA-06550: line 13, column 17:
  PL/SQL: ORA-00942: table or view does not exist
  ORA-06550: line 13, column 2:
  PL/SQL: SQL Statement ignored
  06550. 00000 -  "line %s, column %s:\n%s"
  *Cause:    Usually a PL/SQL compilation error.
  *Action:
  </code></pre>
  
  <p>I don't really understand why it's telling me the table doesn't exist, when I've defined the WorkerStatus table, and declared the t_Ids variable, of type id_array, to be a table as well.  Any help here is greatly appreciated.</p>