Return multiple ref cursors

Topics: Data Access Application Block
Feb 21, 2007 at 12:48 PM
Hello all,

We have an Oracle Database stored procedure that returns multiple cursors, obvioulsy, when you return 1 cursor it must be called cur_OUT, but how would you do this if you need to return 3 or 4 ref cursors?

Also, in c# do you have to add the cursors in the same order as they are defined in the stored procedure, and/or does cur_OUT always need to be the first parameter in the stored proc?

Thanks,

Jon
Feb 21, 2007 at 11:05 PM
Hi Jon,

I don't use Oracle, so I don't have an answer. The answer will be the same whether you use the DAAB or not, however, so you might get a quicker answer to this question if you try asking it in one of the Oracle Forums:

http://forums.oracle.com/

Regards,

Dave

__________________________

David Hayden
Microsoft MVP C#
Mar 8, 2007 at 8:27 PM
My answers are based on my understanding of an older, 1.1 based DAAB. That said...

At that time and version, Oracle did not see the names of parameters in the .NET command structure. It relied on the position of the parameter and they must be the same in .NET code, the Oracle Package and the Oracle Procedure.

I did not reach a limit on the number of cursors and name was relevent only within the name's scope (Oracle package, procedure or .NET method). On the Oracle side, cursors are listed as a parameter, just like any int or varchar (So long as the Ref Cursor name was defined in the Package).

The actual procedure code would be :

open cursor_name1 for SELECT this from that;

Open cursor_name2 for SELECT that from this;

etc.

I have never used a predefined/required name for a cursor. Is that required now?

BTW - what is the size of a cursor for the AddParameter call?

Thanks,
Mar 8, 2007 at 9:48 PM
My own labors with a newer DAAB have borne fruit and I must correct my answer above...

Parameter names are visible. The Oracle Parameter must have the same name as the DbCommand Parameter.
Mar 9, 2007 at 5:06 PM
Edited Mar 9, 2007 at 5:07 PM
I did it like this in version 2.0 Jan 2006 ....

Created Helper Static Class Function .... The Static Class name is AppHelper

public static OracleParameter CreateCursorParameter(String cursorName)
{
OracleParameter orParam = new OracleParameter(cursorName, OracleType.Cursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
return orParam;
}

Then it works something like this (in my case)...

OracleDatabase db = new OracleDatabase(AppHelper.AppConfig("ConnectionString").ToString());
using (DbCommand objComm = db.GetStoredProcCommand("GETWHATEVERPRC"))
{
db.AddInParameter(objComm, "PWHATEVERID", DbType.VarNumeric, Convert.ToInt32(AppHelper.QString("WHATEVER_ID")));
objComm.Parameters.Add(AppHelper.CreateCursorParameter("CUR_CURSOR1"));
objComm.Parameters.Add(AppHelper.CreateCursorParameter("CUR_CURSOR2"));
objComm.Parameters.Add(AppHelper.CreateCursorParameter("CUR_CURSOR3"));
objComm.Parameters.Add(AppHelper.CreateCursorParameter("CUR_CURSOR4"));

objDS = db.ExecuteDataSet(objComm);

}

Now you have a dataset with 4 datatables to do whatever.

NOTE: By default the cursor name (if not defined) is CUROUT. If you create them on your own and don't leave it to the DAAB you can name it whatever you want and CUROUT will not be created.


Hope this helps.