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

  • Thread starter Thread starter Stef
  • Start date Start date
S

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!
 
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

Stef said:
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!
Click to expand...
 
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...
 
Back
Top