RE: SYS_refcursor in pl/sql block

I thought, you want to run PL/SQL block from SQL*Plus. Hence, bind variable:
VARIABLE rs REFCURSOR;

which works in SQL*Plus.

-----Original Message-----

From: Harvinder Singh [mailto:Harvinder.Singh_at_MetraTech.com] Sent: Friday, August 25, 2006 2:42 PM
To: Igor Neyman; ORACLE-L
Subject: RE: SYS_refcursor in pl/sql block

We can't use print since this code is in xml and used by UI and somehow refcursor is not working if I try to declare a variable of type refcursor:
PLS-00201: identifier 'REFCURSOR' must be declared

-----Original Message-----

From: Igor Neyman [mailto:ineyman_at_perceptron.com] Sent: Friday, August 25, 2006 2:12 PM
To: Harvinder Singh; ORACLE-L
Subject: RE: SYS_refcursor in pl/sql block

This should work:

VARIABLE rs REFCURSOR;
Begin
open :rs for

        select a from table inner join b.....; End; /

print rs;

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Harvinder Singh Sent: Friday, August 25, 2006 1:37 PM
To: ORACLE-L
Subject: SYS_refcursor in pl/sql block

Hi,

We are using SYS_REFCURSOR in our procedures as out put parameter to return the result of a query, is there any equivalent I can use in pl/sql block without a procedure. Here is what we are trying to do: Begin
Insert into table....;
Select a from table inner join b.....;
End;

Since Oracle don't allow select without into clause in pl/sql, I added a variable of syscursor but now I am not getting error but not getting the result set also:
Declare
A sys_refcursor;
Begin
Insert into table....;
Open a for Select a from table inner join b.....; End;

Is there any option that we can use without converting this to procedure?

Thanks
--Harvinder

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 25 2006 - 13:46:23 CDT