RE: SYS_refcursor in pl/sql block

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Fri, 25 Aug 2006 14:48:09 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF681573@EXCNYSM0A1AJ.nysemail.nyenet>

Harvinder,

What tool are you using? A ref cursor really is a result set. So your tool needs to be able to handle a result set - which means it needs to fetch records from that result set and process them.

What do you want to do with the cursor?

Tom

---

This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
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
--

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