Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to call a stored procedure using a ref cursor in Oracle with squirrel

I'm trying to do the same request I'm using in Toad

(the stored procedure signature is two varchar2 parameter and one REF CURSOR parameter)

Here is what I do with Toad

variable myCursor refcursor;
EXEC myproc('param1','param2',:myCursor );
print myCursor;

I don't know how to write this with Squirrel and I have to use Squirrel.

Thanks a lot for your response

Raphaël

like image 591
rapdum Avatar asked Apr 14 '10 15:04

rapdum


2 Answers

The only syntax I get working in Squirrel SQL is PL/SQL block:

declare
v_param1  varchar2:='param';
v_param2  varchar2:='param';
TYPE ref_cursor IS REF CURSOR;
v_cur_results ref_cursor;
begin
MyProc (v_param1  , v_param2 , v_cur_results)
end;
/
like image 185
dovka Avatar answered Oct 28 '22 11:10

dovka


If the tool does not support this facility the next best thing would be to create a proc that will output your cursor for you.

Luckly it has already been written for you. see rc_to_dbms_sql( in http://www.oracle-developer.net/display.php?id=505

like image 34
Kevin Burton Avatar answered Oct 28 '22 11:10

Kevin Burton