Home » SQL & PL/SQL » SQL & PL/SQL » Can REF CURSORS be used as IN parameters in STORED procedures
Can REF CURSORS be used as IN parameters in STORED procedures [message #256818] Mon, 06 August 2007 15:27 Go to next message
mala
Messages: 18
Registered: March 2001
Junior Member
HI,

Just wanted to know if REF CURSORS can be used as input parameters (IN parameter) while creating stored procedures. CAn anyone let me know how to test this from SQL prompt .

Thanks
Mala
Re: Can REF CURSORS be used as IN parameters in STORED procedures [message #256853 is a reply to message #256818] Mon, 06 August 2007 22:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yes they can, but to make sense of the refcursor it would (probably) have to be strongly typed to use it in FETCH statements. This means that a definition of the refcursor would have to exist in a package somewhere, and it would be used by the external process to define the refcursor, and by the procedure that receives it.

It is probably possible to use weakly typed refcursors and reference them with DBMS_SQL, but I've never tried this.

Ross Leishman
Re: Can REF CURSORS be used as IN parameters in STORED procedures [message #257025 is a reply to message #256853] Tue, 07 August 2007 07:21 Go to previous messageGo to next message
mala
Messages: 18
Registered: March 2001
Junior Member
Ross,

Thanks for the reply .

I have used a strongly typed cursor. I wanted to test this in
standalone mode from SQL prompt. I am not sure how to go about this for the ref cursor as the input parameter.

Mala.Kaushik


Re: Can REF CURSORS be used as IN parameters in STORED procedures [message #257257 is a reply to message #257025] Tue, 07 August 2007 22:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What do you mean by 'test' it? Tell us exactly what you are tryng to do, because we have no idea.

Ross Leishman
Re: Can REF CURSORS be used as IN parameters in STORED procedures [message #257552 is a reply to message #257257] Wed, 08 August 2007 11:06 Go to previous messageGo to next message
mala
Messages: 18
Registered: March 2001
Junior Member
By 'testing' I meant to check if the stored procedure is working
correctly .

We normally use TOAD as a tool to test this. The version of TOAD that we have does not support testing for REF CURSORS.So I end up testing the Stored procedure from SQL prompt .

I was not sure how to set up the data for REF CURSOR when it is an used as an IN parameter in a stored procedure.

Eg: I would go about testing the following way from SQL prompt, when we use REFCUSROR as an OUT parameter

sql >set server output on ;
sql>variable v1 refcursor ;
sql>execute XI_proc_AQ_get_quote_data ( :v1,349676);
sql>print v1

I was looking for ways to populate the multi row data for REF CURSOR as an IN parameter.

Hope this explains what I am looking for.

Thanks
Mala.Kaushik
Re: Can REF CURSORS be used as IN parameters in STORED procedures [message #257787 is a reply to message #257552] Thu, 09 August 2007 03:07 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
variable v1 refcursor

begin
    open :v1 for select * from dual;
end;
/

exec my_proc(:v1);


Or perhaps:
DECLARE
    v1 my_pack.my_refcursortype;
BEGIN
    open v1 for select * from dual;
    my_proc(v1);
END;
/
Re: Can REF CURSORS be used as IN parameters in STORED procedures [message #257839 is a reply to message #257787] Thu, 09 August 2007 05:44 Go to previous message
mala
Messages: 18
Registered: March 2001
Junior Member
Thanks. That worked.

Really appreciate the effort you took to reply.

Mala
Previous Topic: I need a design idea / querying hierarchical model (merged by LF)
Next Topic: Showing Databases in Server Machine
Goto Forum:
  


Current Time: Sat Dec 10 16:30:30 CST 2016

Total time taken to generate the page: 0.09001 seconds