Re: Cursors & Packages

From: Ewa M. <knowacz_NoSpam_at_usa.net>
Date: Thu, 10 Dec 1998 11:39:54 -0500
Message-ID: <366FF95A.3DFF_at_usa.net>

Biju Kurian wrote:
>
> Hi,
> Is there a book which talks in detail about the uses of cursors inside
> packages? I am having trouble when defining the return clause for my
> cursors. I cannot use %ROWTYPE since I have data coming from multiple
> tables. (...)

Hi there,
here is one example:
PROCEDURE XYZ
IS
CURSOR G1
-- get and sort some stuff:

    IS SELECT GNUMBER, AMOUNT, FACE_VALUE, CURRENCY, FUND, FUND_DESC,MAILPIECE,SYSDATE

       FROM CTYG
       ORDER BY FUND, MAILPIECE, GNUMBER;

TYPE REC_TYPE IS RECORD (GNUMBER      CTYG.GNUMBER%TYPE,
                         AMOUNT       CTYG.AMOUNT%TYPE,
                         FACEV        CTYG.FACE_VALUE%TYPE,
                         CURRENCY     CTYG.CURRENCY%TYPE,
                         FUND         CTYG.FUND%TYPE,
                         FUND_DESC    CTYG.FUND_DESC%TYPE,
                         MAILPIECE    CTYG.MAILPIECE%TYPE,
                         DATE_TODAY   CTYG.GIFT_DATE%TYPE);

G1REC REC_TYPE;
-- other calculations:
BEGIN
    OPEN G1;

      LOOP
        FETCH G1 INTO G1REC;
	-- do some other stuff ex:
           IF G1REC.GNUMBER <> ... THEN
 		....
	   END IF;
	   ...
      END LOOP;

    CLOSE G1;
END; You say that you're using cursors inside packages, so the use of cursors is exactly the same as in any procedure. Packages are nothing else but bunch of procedures, functions, whatever, that allow you to compact nicely large pieces of coding in one place. If you are able to use cursors in any procedure, then you should be able to combine them in package.

> I am defining cursors inside packages because I need to use the same cursors
> mutliple times and I can acheive the same by using the dot
> notation(package.cursor).
>

Hmm.. I'm not sure if you can perform the call "package.cursor" - usually cursors are hidden in procedures so any package call is of type "package.my_proc(some_parameters)" where "cursor" is inside "my_proc". Assuming that "my_proc" returns some records, or not, the call will be the same. If "my_proc" would use cursors from "my_proc01" then the call would be inside my_proc and you wouldn't worry about call to my_proc01 while calling my_proc.

> Please give me some ideas. Thanking you all in advance.
>

I hope I didn't confuse you..:-0
If you'll have some more questions, drop me a line. HTH,
Greetings,
Eva
email: knowacz_at_usa.net Received on Thu Dec 10 1998 - 17:39:54 CET