How to call an Oracle function using the Ref cursor as an Out parameter from C #?

Question

I am using a product that provides Oracle API-based database APIs, and I can call functions through ODP.NET in general. However, I cannot figure out how to call a function that includes Ref Cursor as an Out parameter. All the samples I have found so far call a procedure with an Out parameter or a parameter using Ref Cursor as the return value. I tried to define the parameters in the same way, but I continue to receive an error message if the wrong parameter number or type is specified.

Here is the function title (obviously confusing):

FUNCTION GetXYZ( uniqueId IN somepackage.Number_Type, resultItems OUT somepackage.Ref_Type) RETURN somepackage.Error_Type; 

These are the type definitions in "somepackage":

 SUBTYPE Number_Type IS NUMBER(13); TYPE Ref_Type IS REF CURSOR; SUBTYPE Error_Type IS NUMBER; 

And this is the code I tried:

 string sql = "otherpackage.GetXYZ"; var getXYZCmd = OracleCommand oracleConnection.CreateCommand(sql); getXYZCmd.CommandType = CommandType.StoredProcedure; getXYZCmd.Parameters.Add("uniqueId", OracleDbType.Int32).Value = uniqueExplosionId; getXYZCmd.Parameters.Add("resultItems", OracleDbType.RefCursor).Direction = ParameterDirection.Output; getXYZCmd.Parameters.Add("return_value", OracleDbType.Int32).Direction = ParameterDirection.ReturnValue; 

I tried the following different ways to call a function (of course, only one at a time):

 var result = getXYZCmd.ExecuteNonQuery(); var reader = getXYZCmd.ExecuteReader(); var scalarResult = getXYZCmd.ExecuteScalar(); 

But each of them crashes with an error message:

 Oracle.DataAccess.Client.OracleException: ORA-06550: line 1, column 15: PLS-00306: wrong number or types of arguments in call to 'GETXYZ' ORA-06550: line 1, column 15: PLS-00306: wrong number or types of arguments in call to 'GETXYZ' ORA-06550: line 1, column 7: PL/SQL: Statement ignored. 

So is it even possible to call a function with the Ref cursor as an Out parameter from C # with ODP.NET? I can call a function with the same structure with the Varchar2-Out parameter instead of the Ref Cursor without any problems ...

Btw, I am using ODP.NET version 2.112.2.0 from C # .NET 3.5 in Visual Studio 2008.

Thanks in advance for your help!

Answer 1

Are you sure you can. There are a few errors to worry about, but here's a test case.

 create or replace function testodpRefCursor( uniqueId IN NUMBER ,resultItems OUT NOCOPY SYS_REFCURSOR) RETURN NUMBER IS BEGIN OPEN resultItems for select level from dual connect by level < uniqueId ; return 1; END testodpRefCursor; 

• I found that functions like to have ReturnValue as the FIRST parameter in the collection
• BindByName is FALSE by default, so BIND BY POSITION is by default

Otherwise, it's pretty straight forward:

  OracleCommand cmd = new OracleCommand("TESTODPREFCURSOR", con); cmd.CommandType = CommandType.StoredProcedure; cmd.BindByName = true; // Bind OracleParameter oparam = cmd.Parameters.Add("ReturnValue", OracleDbType.Int64); oparam.Direction = ParameterDirection.ReturnValue ; OracleParameter oparam0 = cmd.Parameters.Add("uniqueId", OracleDbType.Int64); oparam0.Value = 5 ; oparam0.Direction = ParameterDirection.Input; OracleParameter oparam1 = cmd.Parameters.Add("resultItems", OracleDbType.RefCursor); oparam1.Direction = ParameterDirection.Output; // Execute command OracleDataReader reader; try { reader = cmd.ExecuteReader(); while(reader.Read() ){ Console.WriteLine("level: {0}", reader.GetDecimal(0)); } } ... 

Now for more samples, go to your Oracle home directory and see @ Ref cursor samples in ODP.NET

for example:% home oracle \ odp.net \ samples \ 4 \ RefCursor

Hth