How to use Ref Cursor as out parameter in SPs?

koopz · Post by **koopz** » Fri 03 Apr 2009 19:58

The new released version of dotConnect for Oracle can handle ref cursors already. Can anybody tell me how you implement it in the code... im having trouble passing the ref object to an object type variable.. Thanks!

Shalex · Post by **Shalex** » Mon 06 Apr 2009 08:12

Here is a script:

Code: Select all

PROCEDURE TestProc (cur out sys_refcursor)
AS
BEGIN
  open cur for select * from dept;
END;

Here is a C# code:

Code: Select all

using (OracleConnection conn = new OracleConnection(connStr)){
  conn.Open();
  OracleCommand comm = conn.CreateCommand();
  comm.CommandText = "TestProc";
  comm.CommandType = System.Data.CommandType.StoredProcedure;
  comm.ParameterCheck = true;
  comm.ExecuteNonQuery();
  OracleDataReader rdr = ((OracleCursor)comm.Parameters[0].Value).GetDataReader();
  //processing the reader values
  while (rdr.Read()) {
    int deptno = rdr.GetInt32(0);  
  }
}

Is that what you mean?

koopz · Post by **koopz** » Mon 06 Apr 2009 13:28

Thans Shalex, but i know how to implement it like that. I'm actually trying to call it from the DataContext generated by the Entity Developer Tool. Im calling it as :

ISingleResult res_set = context.ACTSSEL(ref obj, 0, "");

where in obj is of type Object and as type Ref Cursor on the SP.

But when I call

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), PACTSRC, PACTID, PACTNAME);

it is throwing an exception...

Thanks!

AndreyR · Post by **AndreyR** » Mon 06 Apr 2009 14:13

Support for Stored Procedures will be available only in the upcoming Beta 2 of dotConnect for Oracle.

koopz · Post by **koopz** » Mon 06 Apr 2009 15:15

Im already using Stored Procedures using dotConnect to Oracle. I am succesfully running it with scalar values as out parameters. The only thing that is giving me a problem is an OUT parameter of type REF CURSOR.

The generated code from Entity Developer already identified it as a DBTYPE=Ref Cursor but when Im calling it on the ExecuteMethod Call, it does not want to accept it as an object.

Im using the new release from March. Which i believe is 5.2.

AndreyR · Post by **AndreyR** » Tue 07 Apr 2009 12:49

Could you please post the message and call stack of the exception?
Also please include the script of your procedure and the generated method for its call, if possible.

koopz · Post by **koopz** » Tue 07 Apr 2009 16:01

Here's the stack trace:

at Devart.Data.Linq.Provider.Query.bg.b(ap A_0, MethodCallExpression A_1, MetaFunction A_2)
at Devart.Data.Linq.Provider.Query.bg.a(MethodCallExpression A_0)
at Devart.Data.Linq.Provider.Query.bg.b(MethodCallExpression A_0)
at Devart.Data.Linq.Provider.Query.bg.i(Expression A_0)
at Devart.Data.Linq.Provider.Query.bg.h(Expression A_0)
at Devart.Data.Linq.Provider.DataProvider.a(Expression A_0)
at Devart.Data.Linq.Provider.DataProvider.h(Expression A_0)
at Devart.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)
at MC_BackOffice.Infrastructure.ActDataContext.ACTSSEL(Object& PACTSRC, Nullable`1 PACTID, String PACTNAME) in C:\Work\MC_BackOffice\MC_BackOffice\MC_BackOffice.Infrastructure\ActDataContext.cs:line 86

it is erroring out on this line:

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), PACTSRC, PACTID, PACTNAME);

in this method

[Function(Name=@"OSDC_CAT.ACTS_SEL")]
public System.Data.Linq.ISingleResult ACTSSEL([Parameter(Name="P_ACTS_RC", DbType="REF CURSOR")] ref object PACTSRC, [Parameter(Name="P_ACT_ID", DbType="NUMBER(12)")] System.Nullable PACTID, [Parameter(Name="P_ACT_NAME", DbType="VARCHAR2(150)")] string PACTNAME)
{
try
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), PACTSRC, PACTID, PACTNAME);
PACTSRC = ((System.Object)(result.GetParameterValue(0)));
return ((System.Data.Linq.ISingleResult)(result.ReturnValue));
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
return null;
}

}

here's how im callin it:

ActDataContext context = new ActDataContext();

object obj = new object();
ISingleResult res_set = context.ACTSSEL(ref obj,18, "");

AndreyR · Post by **AndreyR** » Wed 08 Apr 2009 09:05

This problem is fixed in the upcoming Beta 2 of dotConnects.

koopz · Post by **koopz** » Wed 08 Apr 2009 14:32

Is the one that was released on March Beta 2? If its not, when is the planned release for this?

Thanks!

AndreyR · Post by **AndreyR** » Thu 09 Apr 2009 06:17

dotConnect 5.20.24 Beta was released in March.
The dotConnect Beta 2 will be available this week.