Call SP with application block (daab) & multiple out REF CURSOR parameters

[Stef]

Hi,
I'm using Microsoft's data application blocks to execute a stroed
procedure that look like this: (Note that I have not included the
package declaration nor the T_CURSOR type declaration since it's
working fine when I execute this procedure in a tool like TOAD or else)

PROCEDURE qryTest(EMPLOYEE_ID IN VARCHAR, EMP_CUR OUT T_CURSOR,
TASK_CUR OUT T_CURSOR)
IS
BEGIN

OPEN EMP_CUR FOR
SELECT DISTINCT EMP_ID,
FIRSTNAME || ' ' || LASTNAME AS FULLNAME
FROM EMPLOYEE_TABLE WHERE EMP_ID = EMPLOYEE_ID;


OPEN TASK_CUR FOR
SELECT DISTINCT T1.TASK_ID,
T1.TASKNAME
FROM TASK_TABLE T1 INNER JOIN EMPLOYEE_TABLE T2 ON T1.EMP_ID =
T2.EMP_ID WHERE T2.EMP_ID = EMPLOYEE_ID;

END qryTest;

Then, I try to execute this SP from the data app blocks this way:

First, I have a function that acts as a definition block to call the
generic method calling the SP (VB.NET):

Private Function getEmployees() As DataSet

Dim prms(2) As OracleParameter

prms(0) = New OracleParameter("EMPLOYEE_ID", OracleType.VarChar, 20,
ParameterDirection.Input, False, 0, 0, "", DataRowVersion.Default,
"6586")
prms(1) = New OracleParameter("EMP_CUR", OracleType.Cursor, 30000,
ParameterDirection.Output, True, 0, 0, "", DataRowVersion.Default,
Nothing)
prms(2) = New OracleParameter("TASK_CUR", OracleType.Cursor, 30000,
ParameterDirection.Output, True, 0, 0, "", DataRowVersion.Default,
Nothing)

ds = DataBaseLAyer.ExecuteOracleProcedure("qryTest", False, prms)

....

Return ds

End Function

And this is the function that execute the SP using the app blocks (C#):
public static DataSet ExecuteOracleProcedure(string procName, bool
createEmptyRow, params OracleParameter[] parameters)
{

Database db = DatabaseFactory.CreateDatabase("DBPACKAGENAME");
DBCommandWrapper cmd = db.GetStoredProcCommandWrapper(procName);

if(parameters != null)
{
foreach(OracleParameter param in parameters)
{
if(param.Direction == ParameterDirection.Input)
cmd.AddInParameter(param.ParameterName, param.DbType, param.Value);
else
cmd.AddOutParameter(param.ParameterName, param.DbType, param.Size);
}
}
DataSet ds = db.ExecuteDataSet(cmd);

return ds;

}

When DataSet ds = db.ExecuteDataSet(cmd); is executed, I get this
error:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'QRYTEST' ORA-06550: line 1, column 7: PLS-00306:
wrong number or types of arguments in call to 'QRYTEST'

The procedure runs perfectly in TOAD but in here, it gives me this
error several times in the same message...
I did some tests and if I add another REF CURSOR (T_CURSOR) output
param, I will get the error 3 times in the stack trace and so on...

I know that the app block adds an out param implicitly which has to be
named "cur_OUT" in my SP, which I TRIED without any success...

Someone can help?

Thanks for reading!

 

[Howard Hoffman]

Stef -

We're having a similar problem. Can you share the CommandText of your
(ultimate) DbCommand? Perhaps there's some syntax that's incorrect ?

Thanks,

Howard Hoffman

Hi,
I'm using Microsoft's data application blocks to execute a stroed
procedure that look like this: (Note that I have not included the
package declaration nor the T_CURSOR type declaration since it's
working fine when I execute this procedure in a tool like TOAD or else)

PROCEDURE qryTest(EMPLOYEE_ID IN VARCHAR, EMP_CUR OUT T_CURSOR,
TASK_CUR OUT T_CURSOR)
IS
BEGIN

OPEN EMP_CUR FOR
SELECT DISTINCT EMP_ID,
FIRSTNAME || ' ' || LASTNAME AS FULLNAME
FROM EMPLOYEE_TABLE WHERE EMP_ID = EMPLOYEE_ID;


OPEN TASK_CUR FOR
SELECT DISTINCT T1.TASK_ID,
T1.TASKNAME
FROM TASK_TABLE T1 INNER JOIN EMPLOYEE_TABLE T2 ON T1.EMP_ID =
T2.EMP_ID WHERE T2.EMP_ID = EMPLOYEE_ID;

END qryTest;

Then, I try to execute this SP from the data app blocks this way:

First, I have a function that acts as a definition block to call the
generic method calling the SP (VB.NET):

Private Function getEmployees() As DataSet

Dim prms(2) As OracleParameter

prms(0) = New OracleParameter("EMPLOYEE_ID", OracleType.VarChar, 20,
ParameterDirection.Input, False, 0, 0, "", DataRowVersion.Default,
"6586")
prms(1) = New OracleParameter("EMP_CUR", OracleType.Cursor, 30000,
ParameterDirection.Output, True, 0, 0, "", DataRowVersion.Default,
Nothing)
prms(2) = New OracleParameter("TASK_CUR", OracleType.Cursor, 30000,
ParameterDirection.Output, True, 0, 0, "", DataRowVersion.Default,
Nothing)

ds = DataBaseLAyer.ExecuteOracleProcedure("qryTest", False, prms)

...

Return ds

End Function

And this is the function that execute the SP using the app blocks (C#):
public static DataSet ExecuteOracleProcedure(string procName, bool
createEmptyRow, params OracleParameter[] parameters)
{

Database db = DatabaseFactory.CreateDatabase("DBPACKAGENAME");
DBCommandWrapper cmd = db.GetStoredProcCommandWrapper(procName);

if(parameters != null)
{
foreach(OracleParameter param in parameters)
{
if(param.Direction == ParameterDirection.Input)
cmd.AddInParameter(param.ParameterName, param.DbType, param.Value);
else
cmd.AddOutParameter(param.ParameterName, param.DbType, param.Size);
}
}
DataSet ds = db.ExecuteDataSet(cmd);

return ds;

}

When DataSet ds = db.ExecuteDataSet(cmd); is executed, I get this
error:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'QRYTEST' ORA-06550: line 1, column 7: PLS-00306:
wrong number or types of arguments in call to 'QRYTEST'

The procedure runs perfectly in TOAD but in here, it gives me this
error several times in the same message...
I did some tests and if I add another REF CURSOR (T_CURSOR) output
param, I will get the error 3 times in the stack trace and so on...

I know that the app block adds an out param implicitly which has to be
named "cur_OUT" in my SP, which I TRIED without any success...

Someone can help?

Thanks for reading!

 

[Stef]

Hi Howard,
The command text of my DbCommand is equal to TestPackage.qryTest
which is seemingly fine...
But by looking further in the object hierarchy, I've noticed a property
named "StatementText" which looks like this:
StatementText "begin TestPackage.qryTest (EMPLOYEE_ID=>:EMPLOYEE_ID,
EMP_CUR=>:EMP_CUR, TASK_CUR=>:TASK_CUR, cur_OUT=>:cur_OUT); end;"

Everything seems fine since DAAB adds a cur_OUT out param with a cursor
type at the end of the command, which I have defined in my stored
proc...

I'm stumped on this one... Really...