Hi everyone,

I have a master procedure which opens a cursor and then calls another CHILD
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 so I
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 what I
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;
/

GF wrote:

You can't. You can pass with a REF CURSOR, you can pass an array, you can pass
primary key values, or best of all, pass ROWID.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

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 (DOT) the.newsgroup> wrote