Calling stored proc with Enterprise Library 2

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
labate
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

Calling stored proc with Enterprise Library 2

Post by labate » Fri 08 Jun 2007 09:04

Hi,

I didn't succeed in calling a Oracle stored proc with Entlib 2, January 2006 and CoreLab OraDirect for .NET 2, version 3.55.20.0.

My stored procedure (in a package) is :

Code: Select all

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

create or replace package body "XXX".pck_yyy as

function GetPersonalData(in_cardId in card.cardid%type, out_personCursor 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 :

Code: Select all

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);
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 A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
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.

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

Thanks,
Adriano

labate
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

SOLVED: Calling stored proc with Enterprise Library 2

Post by labate » Fri 08 Jun 2007 12:55

Hi,

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

In my previous example, I used

Code: Select all

DataSet ds = database.ExecuteDataSet(dbCommand);

In fact, ExecuteDataSet() only works with a procedure, not a function. For example:

Code: Select all

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:

Code: Select all

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:

Code: Select all

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:

Code: Select all

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

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 11 Jun 2007 07:57

Well done.

Post Reply