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

Question

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

Solution 1

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;
/

Solution 2

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