Calling Oracle SP with cursor (EntLib2)

Topics: Data Access Application Block
Jun 8, 2007 at 9:35 AM
Hi,

I didn't succeed in calling a Oracle stored proc with Entlib 2, January 2006.

My Oracle stored procedure (in a package) is :

.............................................................................................................................

create or replace package "XXX".pck_yyy as
type T_CURSOR is ref cursor;
function GetPersonalData(in_cardId in card.cardid%type, outpersonCursor out T_CURSOR) return boolean;
end;
create or replace package body "XXX".pck_yyy as

function GetPersonalData(incardId in card.cardid%type, outpersonCursor out T_CURSOR) return boolean
as
...
begin
open out_personCursor
for
select p.personid,
p.firstname,
p.name,
lang.languagename,
cat.categoryname
from ...
where ...;

return true;
end;

.............................................................................................................................


And my C# code is :


.............................................................................................................................

DbCommand dbCommand = database.GetStoredProcCommand("pck_yyy.GetPersonalData");

database.AddParameter(dbCommand, "return", DbType.Boolean, 0, ParameterDirection.Output, false, 0, 0, String.Empty, DataRowVersion.Current, Convert.DBNull);
database.AddParameter(dbCommand, "in_cardId", DbType.String, 128, ParameterDirection.Input, false, 0, 0, String.Empty, DataRowVersion.Current, cardId);

OracleParameter op = new OracleParameter("outp_personCursor", OracleDbType.Cursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Current, Convert.DBNull); // CoreLab provider
dbCommand.Parameters.Add(op);

DataSet ds = database.ExecuteDataSet(dbCommand);

--------------------------------------------------------------------------------------------------------------------


I got the following error :

CoreLab.Oracle.OracleException: ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'GETPERSONALDATA'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
at CoreLab.Oracle.OracleCommand.a(CommandBehavior A0, IDisposable A1, Int32 A2, Int32 A3)
at CoreLab.Common.DbCommandBase.c(CommandBehavior A_0)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command)
at Polyright.TPM.DataAccess.DatabaseAccess.GetPersonalData(String cardId) in ...

I tried lots of variants in my code and looked for lots of web page in search of a solution, also on this site.

I'm probably wrong somewhere as a work with EntLib since a pair of weeks only.

Could someone give me some hints about how to get it work?
Thanks,
Adriano
Jun 8, 2007 at 1:10 PM
Hi,

I finally found a solution that I put here, hoping this will help others.

In my previous example, I used:
DataSet ds = database.ExecuteDataSet(dbCommand); 
In fact, ExecuteDataSet() only works with a procedure, not a function. For example:
procedure GetPersonalData(in_cardId in card.cardid%type, out_personCursor out T_CURSOR);
Now if I have a stored proc with a OUT cursor parameter and I also want to return a value, for example:
function GetPersonalData(in_cardId in card.cardid%type, out_personCursor out T_CURSOR) return boolean;
I have to use ExecuteNonQuery() as in the following code:
DbCommand dbCommand = database.GetStoredProcCommand("pck_yyy.GetPersonalData");
 
database.AddParameter(dbCommand, "in_cardId", DbType.String, 128, ParameterDirection.Input, false, 0, 0, String.Empty, DataRowVersion.Current, cardId);
 
dbCommand.Parameters.Add(CreateCursorParameter("out_personCursor"));
database.AddParameter(dbCommand, "out_result", DbType.Boolean, 0, ParameterDirection.ReturnValue, true, 0, 0, String.Empty, DataRowVersion.Current, Convert.DBNull);
 
database.ExecuteNonQuery(dbCommand);
 
IDataReader reader = CreateDataReaderFromCursor(database.GetParameterValue(dbCommand, "out_personCursor"));
bool result = (bool)database.GetParameterValue(dbCommand, "out_result");
As I use CoreLab OraDirect as database provider, I have 2 static methods that can handle cursors:
public static DbParameter CreateCursorParameter(string cursorName) {
    OracleParameter param = new OracleParameter(cursorName, OracleDbType.Cursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Current, Convert.DBNull);
    return param;
}
 
public static IDataReader CreateDataReaderFromCursor(object cursor) {
    OracleCursor oracleCursor = (OracleCursor) cursor;
    return oracleCursor.GetDataReader();
}

This solves my current problem.
Adriano
Mar 16, 2009 at 7:03 PM
Edited Mar 16, 2009 at 7:04 PM
Any one able to implement it for EntLib 4.0, ODP 10.2 and .net 3.5..??

I would appreiciate if any one can share thier experiences over it...

Thanks...