How to use Ref Cursor as out parameter in SPs?
How to use Ref Cursor as out parameter in SPs?
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!
Here is a script:
Here is a C# code:
Is that what you mean?
Code: Select all
PROCEDURE TestProc (cur out sys_refcursor)
AS
BEGIN
open cur for select * from dept;
END;
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);
}
}
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!
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!
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.
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.
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, "");
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, "");