Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PLSQL Cursors

Re: PLSQL Cursors

From: GF <please.reply_at_to.the.newsgroup>
Date: Fri, 18 Jul 2003 09:19:10 +0200
Message-ID: <newscache$7nl7ih$yfm$1@is-net.bs.noc.onion.it>


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 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;
> /
>
>
>
Received on Fri Jul 18 2003 - 02:19:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US