Re: PLSQL Cursors

On Wed, 16 Jul 2003 17:45:58 +0200, "GF" <please.reply_at_to.the.newsgroup> 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;
>/
>

Your crscursor in the calling procedure isn't a refcursor. It can be a refcursor but in that case you need to declare it explicitly. However, couldn't you just pass the few primary key columns as parameter?

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jul 16 2003 - 13:16:36 CDT