I'm using a product that provides a database API based on Oracle functions and I'm able to call functions via ODP.NET in general. However, I can't figure out, how to call a function that includes a Ref Cursor as Out-parameter. All the samples I found so far either call a procedure with Out-parameter or a function with the Ref Cursor as return value. I tried to define the parameters similiarly, but keep getting the error that the wrong number or type of parameters is supplied.
Here is the function header (obviously obfuscated):
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 that I have 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;
The I tried the following different ways to call the function (of course only one at a time):
var result = getXYZCmd.ExecuteNonQuery();
var reader = getXYZCmd.ExecuteReader();
var scalarResult = getXYZCmd.ExecuteScalar();
But each of them fails with the 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 generally possible to call a function with a Ref Cursor as Out-parameter from C# with ODP.NET? I can call a function with the same structure with a Varchar2-Out-parameter instead of the Ref Cursor without problems...
Btw, I'm using ODP.NET version 2.112.2.0 from C#.NET 3.5 in Visual Studio 2008.
Thanks in advance for your help!
You sure can. There are a few gotchas to be wary of but here is 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;
Otherwise it is quite 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 look @ the Ref cursor samples in ODP.NET
for instance: %oracle client home%\odp.net\samples\4\RefCursor
hth