Page 1 of 1
stored procedure ref cursor
Posted: Wed May 09, 2007 12:41 pm
by belaruska
i am using ODBC stage to call a stored procedure. it has 2 input parameters and an output ref cursor. i used manager to define metadata, but it only shows two input parameters. when i call the procedure with just 2 input parameters, it gives me error: ORA-06550: PLS-00306: wrong number or types of arguments. when i manually add an output parameter and call it in sp, it gives me the error: ORA-01008: not all variables bound.
i've also defined the columns for the output based on the table definition.
am i missing something?
Posted: Wed May 09, 2007 12:46 pm
by DSguru2B
What kind of variables are these? I think ODBC stage can only support IN and IN/OUT parameters for stored procedures. Do you have STP stage?
Posted: Wed May 09, 2007 1:01 pm
by belaruska
DSguru2B wrote:What kind of variables are these? I think ODBC stage can only support IN and IN/OUT parameters for stored procedures. Do you have STP stage?
there are two IN variables, and ref cursor is OUT
Posted: Wed May 09, 2007 1:02 pm
by DSguru2B
Read about stored procedure calls in ODBC help guide. I am pretty sure OUT parameter is not supported. But I can be wrong.
Posted: Wed May 09, 2007 1:09 pm
by belaruska
DSguru2B wrote:Read about stored procedure calls in ODBC help guide. I am pretty sure OUT parameter is not supported. But I can be wrong.
but even when i use STP stage I get the same errors.
Posted: Wed May 09, 2007 1:14 pm
by DSguru2B
Are you able to execute the stored proc. successfully and capture the OUT parameter from your favourite sql tool.
Posted: Wed May 09, 2007 1:26 pm
by belaruska
DSguru2B wrote:Are you able to execute the stored proc. successfully and capture the OUT parameter from your favourite sql tool.
when i execute the sp from sqlplus, i have to define a variable:
sql> variable v refcursor
sql> exec stor_proc (in_par1, in_par2, :v)
it works; and to see the result:
sql> print v