"Stef" <stephan.parrot@mittalsteel.com> wrote in message
news:1133377108.195457.307970@g43g2000cwa.googlegroups.com...
> 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!
>