Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PLSQL Cursors
Thanx to both for the reply...
In the end I found a way to achieve the desired behaviour:
CREATE OR REPLACE PACKAGE UPD_FOOBAR_PKG IS /* Defined the cursor at package scope instead of a Ref Cursor
TYPE refCursor IS REF CURSOR;
*/
CURSOR rsRecordset
IS
SELECT * FROM MAINTABLE FOR UPDATE;
PROCEDURE UPD_MAIN_PRC;
PROCEDURE UPD_CHILD_PRC(
crsSomeCursor IN rsRecordset%ROWTYPE /* declared the input cursor as
ROWTYPE from the main cursor*/
);
....
....
END UPD_FOOBAR_PKG;
/
CREATE OR REPLACE PACKAGE BODY UPD_FOOBAR_PKG IS
/*Removed definition in package body as I found out definitions need not
be specified in the body
TYPE refCursor IS REF CURSOR;
*/
PROCEDURE UPD_MAIN_PRC
IS
crsCursor rsRecordset %ROWTYPE;
BEGIN
FOR crsCursor IN rsRecordset LOOP
BEGIN
(do other stuff...)
UPD_CHILD_PRC(crsCursor);
END LOOP;
END UPD_MAIN_PRC;
PROCEDURE UPDATE_CHILD(
crsSomeCursor IN rsRecordset%ROWTYPE
)
IS
BEGIN
UPDATE FOOBAR
SET FOOBAR.FOO = crsSomeCursor.BAR
WHERE
FOOBAR.CONDITION = crsSomeCursor.MYCONDITION
END UPDATE_CHILD;
END UPD_FOOBAR_PKG;
/
GF
"GF" <please.reply_at_to.the.newsgroup> wrote in message news:newscache$osj4ih$klf$1_at_is-net.bs.noc.onion.it...
> Hi everyone, > > I have a master procedure which opens a cursor and then calls anotherCHILD
> procedure that has to do some other work. > I need to pass a cursor to the CHILD stored procedure but I'm having > difficulties doing it. The reason for passing an entire cursor is that soI
> don't have to pass too many IN parameters in the CHILD procedure. > > I get a the following compilation error though: > > (1):PLS-00306: wrong number or types of arguments in call to'UPD_CHILD_PRC'
> > I know I'm doing something wrong in the cursor variable declaration. > I looked up some docs but none give indications on how I can achieve whatI
> want to do. > Anyone Have any ideas? > > Thanx in advance. > GF > > To clarify what I'm trying to do, here's the code: > > > CREATE OR REPLACE PACKAGE UPD_FOOBAR_PKG IS > > TYPE refCursor IS REF CURSOR; > > PROCEDURE UPD_MAIN_PRC; > > PROCEDURE UPD_CHILD_PRC( > crsSomeCursor IN refCursor > ); > > .... > .... > > END UPD_FOOBAR_PKG; > / > > CREATE OR REPLACE PACKAGE BODY UPD_FOOBAR_PKG IS > > TYPE refCursor IS REF CURSOR; > > PROCEDURE UPD_MAIN_PRC > IS > > CURSOR rsRecordset > IS > SELECT * FROM MAINTABLE > FOR UPDATE; > > BEGIN > > FOR crsCursor IN rsRecordset LOOP > BEGIN > > (do other stuff...) > > UPD_CHILD_PRC(crsCursor); > > END LOOP; > > END UPD_MAIN_PRC; > > > PROCEDURE UPDATE_CHILD( > crsSomeCursor IN refCursor > ) > IS > BEGIN > > UPDATE FOOBAR > SET FOOBAR.FOO = crsSomeCursor.BAR > WHERE > FOOBAR.CONDITION = crsSomeCursor.MYCONDITION > > END UPDATE_CHILD; > > END UPD_FOOBAR_PKG; > / > > >Received on Fri Jul 18 2003 - 02:19:10 CDT