Re: PLSQL Cursors

GF wrote:

> 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;
> /

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/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)