Page 1 of 1
How to use Ref Cursor as out parameter in SPs?
Posted: Fri 03 Apr 2009 19:58
by koopz
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!
Posted: Mon 06 Apr 2009 08:12
by Shalex
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?
Posted: Mon 06 Apr 2009 13:28
by koopz
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!
Posted: Mon 06 Apr 2009 14:13
by AndreyR
Support for Stored Procedures will be available only in the upcoming Beta 2 of dotConnect for Oracle.
Posted: Mon 06 Apr 2009 15:15
by koopz
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.
Posted: Tue 07 Apr 2009 12:49
by AndreyR
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.
Posted: Tue 07 Apr 2009 16:01
by koopz
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, "");
Posted: Wed 08 Apr 2009 09:05
by AndreyR
This problem is fixed in the upcoming Beta 2 of dotConnects.
Posted: Wed 08 Apr 2009 14:32
by koopz
Is the one that was released on March Beta 2? If its not, when is the planned release for this?
Thanks!
Posted: Thu 09 Apr 2009 06:17
by AndreyR
dotConnect 5.20.24 Beta was released in March.
The dotConnect Beta 2 will be available this week.