How to use Ref Cursor as out parameter in SPs?

How to use Ref Cursor as out parameter in SPs?

Postby 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!
koopz
 
Posts: 5
Joined: Fri 03 Apr 2009 19:55

Postby 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?
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Postby 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!
koopz
 
Posts: 5
Joined: Fri 03 Apr 2009 19:55

Postby AndreyR » Mon 06 Apr 2009 14:13

Support for Stored Procedures will be available only in the upcoming Beta 2 of dotConnect for Oracle.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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.
koopz
 
Posts: 5
Joined: Fri 03 Apr 2009 19:55

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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, "");
koopz
 
Posts: 5
Joined: Fri 03 Apr 2009 19:55

Postby AndreyR » Wed 08 Apr 2009 09:05

This problem is fixed in the upcoming Beta 2 of dotConnects.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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!
koopz
 
Posts: 5
Joined: Fri 03 Apr 2009 19:55

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle