stored procedure ref cursor

belaruska · Post by **belaruska** » Wed May 09, 2007 12:41 pm

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?

DSguru2B · Post by **DSguru2B** » Wed May 09, 2007 12:46 pm

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?

belaruska · Post by **belaruska** » Wed May 09, 2007 1:01 pm

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

DSguru2B · Post by **DSguru2B** » Wed May 09, 2007 1:02 pm

Read about stored procedure calls in ODBC help guide. I am pretty sure OUT parameter is not supported. But I can be wrong.

belaruska · Post by **belaruska** » Wed May 09, 2007 1:09 pm

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.

DSguru2B · Post by **DSguru2B** » Wed May 09, 2007 1:14 pm

Are you able to execute the stored proc. successfully and capture the OUT parameter from your favourite sql tool.

belaruska · Post by **belaruska** » Wed May 09, 2007 1:26 pm

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