How to use an Oracle Ref Cursor from C# ODP.NET as a ReturnValue Parameter, without using a Stored Function or Procedure?

Question

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.

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.

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:

CREATE TABLE WorkerStatus
(
    Id                  NUMERIC(38)         NOT NULL
    ,StateId            NUMERIC(38)         NOT NULL
    ,StateReasonId      NUMERIC(38)         NOT NULL
    ,CONSTRAINT PK_WorkerStatus PRIMARY KEY ( Id )
)

I pre-populate the table with three test values like so:

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;

The existing SQL statement, loaded from a script file named Oracle_UpdateWorkerStatus2, and contained in the OracleCommand.CommandText looks like so:

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;

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:

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

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 (How to call an Oracle function with a Ref Cursor as Out-parameter from C#?), but that still uses a Stored Function as far as I can tell.

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:

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:

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.

Answer 1

I will try an answer instead of another comment.

As I said in one comment, a pure/simple select-statement does not work in PL/SQL. But I was wrong in stating, that you need a stored function to return a ref cursor.

But first things first: The type "id_array" you declare in your PL/SQL-block is a PL/SQL type. It cannot be used in a ref cursor select statement. Instead you will need a SQL type:

create type id_array as table of number;

This needs to be executed only once, just like a "create table".

Your PL/SQL-block could then look like this:

DECLARE
    t_ids   id_array;
BEGIN
    UPDATE WorkerStatus
    SET
         StateId = :StateId
        ,StateReasonId = :StateReasonId
    WHERE
        StateId = :CurrentStateId
    RETURNING Id BULK COLLECT INTO t_Ids;

    OPEN :rcursor FOR SELECT * FROM TABLE(cast(t_Ids as id_array));    
END;

PS:
While assembling this post, I realized where the ORA-00942 might come from. The array t_ids was based on a PL/SQL type, which is not known/available on the SQL side.