Re: Packaged cursor problem

From: <pbolduc_at_my-deja.com>
Date: Sat, 27 Nov 1999 07:35:33 GMT
Message-ID: <81o1k5$ktg$1@nnrp1.deja.com>

You have at least 2 options. I prefer the first, however it does not hide the cursor SQL as well as the second. Note: I could not test compile these as I do not have Oracle installed at home. Good luck.

Option 1: try...

CREATE OR REPLACE PACKAGE PAC1 AS
  CURSOR charge_ban_cur IS

      SELECT ban
        FROM charge;

END PAC1;
-- you do not need a body

DECLARE
   charge_ban_rec charge_ban_cur%ROWTYPE; BEGIN
   OPEN pac1.charge_ban_cur;
   LOOP

       FETCH pac1.charge_ban_cur INTO charge_ban_rec;
       EXIT WHEN pac1.charge_ban_cur%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE(charge_ban_rec.ban);

   END LOOP;
   CLOSE pac1.charge_ban_cur;
END; Opion 2: You could also use reference cursors...

CREATE OR REPLACE PACKAGE PAC1 AS   TYPE t_charge_ban_rec IS RECORD (

      ban charge.ban%TYPE );

  TYPE t_charge_ban_ref IS REF CURSOR

     RETURN t_charge_ban_rec;

  charge_ban_cur t_charge_ban_ref;

  PROCEDURE OPEN_CURSOR;
END PAC1; CREATE OR REPLACE PACKAGE BODY PAC1 AS   PROCEDURE OPEN_CURSOR AS
  BEGIN

     OPEN charge_ban_cur FOR
         SELECT ban FROM charge;

  END; END PAC1; DECLARE
   charge_ban_rec t_charge_ban_rec;
BEGIN
   pac1.OPEN_CURSOR;
   LOOP

       FETCH pac1.charge_ban_cur INTO charge_ban_rec;
       EXIT WHEN pac1.charge_ban_cur%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE(charge_ban_rec.ban);

   END LOOP;
   CLOSE pac1.charge_ban_cur;
END; Phil Bolduc
North Vancouver, BC Canada

In article <81ntc2$i5g$1_at_nnrp1.deja.com>,   alienn_at_my-deja.com wrote:
> I'm trying to declare Packaged cursor which will retrieve only one
> field from the table. Can anybody tell me what am I missing
>
> I will really appreciate any suggestion
>
> Paul
>
> ###### FIRST TRY ####################################################
>
> CREATE OR REPLACE PACKAGE pac1 AS
> CURSOR cur1 RETURN charge.ban%TYPE;
> END pac1;
> /
> CREATE OR REPLACE PACKAGE BODY pac1 AS
> CURSOR cur1 RETURN charge.ban%TYPE IS select ban from charge;
> END pac1;
> /
>
> SQL> @pp7
>
> Warning: Package created with compilation errors.
>
> Warning: Package Body created with compilation errors.
>
> SQL> show error
> Errors for PACKAGE BODY PAC1:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 0/0 PL/SQL: Compilation unit analysis terminated
> 1/14 PLS-00905: object TSTAPP1.PAC1 is invalid
> 1/14 PLS-00304: cannot compile body of 'PAC1' without its
> specification
>
> #####################################################################
>
> ###### SECOND TRY ###################################################
> /* Commenting the cursor declaration in specification */
>
> CREATE OR REPLACE PACKAGE pac1 AS
> -- CURSOR cur1 RETURN charge.ban%TYPE;
> END pac1;
> /
> CREATE OR REPLACE PACKAGE BODY pac1 AS
> CURSOR cur1 RETURN charge.ban%TYPE IS select ban from charge;
> END pac1;
> /
>
> SQL> @pp7
>
> Package created.
>
> Warning: Package Body created with compilation errors.
>
> SQL> show error
> Errors for PACKAGE BODY PAC1:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 2/3 PL/SQL: Item ignored
> 2/22 PLS-00320: the declaration of the type of this expression is
> incomplete or malformed
>
> #####################################################################
>
> ###### THIRD TRY ####################################################
>
> CREATE OR REPLACE PACKAGE pac1 AS
> -- CURSOR cur1 RETURN charge.ban%TYPE;
> END pac1;
> /
> CREATE OR REPLACE PACKAGE BODY pac1 AS
> CURSOR cur1 RETURN tstapp1.charge.ban%TYPE IS select ban from
charge;
> END pac1;
> /
>
> /* Using dbname with the type decl, the result remains the same as in
> SECOND TRY */
>
> #####################################################################
>
> ######## ONE MORE TRY ###############################################
>
> CREATE OR REPLACE PACKAGE pac1 AS
> TYPE charge_rec IS RECORD (charge.ban%TYPE);
> -- CURSOR cur1 RETURN charge_rec;
> END pac1;
> /
> CREATE OR REPLACE PACKAGE BODY pac1 AS
> CURSOR cur1 RETURN charge_rec IS select ban from charge;
> END pac1;
> /
>
> SQL> @pp7
>
> Warning: Package created with compilation errors.
>
> Warning: Package Body created with compilation errors.
>
> SQL> show error
> Errors for PACKAGE BODY PAC1:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 0/0 PL/SQL: Compilation unit analysis terminated
> 1/14 PLS-00905: object TSTAPP1.PAC1 is invalid
> 1/14 PLS-00304: cannot compile body of 'PAC1' without its
> specification
>
> #####################################################################
>
> # LAST TRY ##########################################################
>
> CREATE OR REPLACE PACKAGE pac1 AS
> -- TYPE charge_rec IS RECORD (charge.ban%TYPE);
> -- CURSOR cur1 RETURN NUMBER;
> END pac1;
> /
> CREATE OR REPLACE PACKAGE BODY pac1 AS
> CURSOR cur1 RETURN NUMBER IS select ban from charge;
> END pac1;
> /
>
> SQL> @pp7
>
> Package created.
>
> Warning: Package Body created with compilation errors.
>
> SQL> show error
> Errors for PACKAGE BODY PAC1:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 2/3 PL/SQL: Item ignored
> 2/22 PLS-00320: the declaration of the type of this expression is
> incomplete or malformed
>
> #####################################################################
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Nov 27 1999 - 01:35:33 CST