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

Home -> Community -> Usenet -> c.d.o.server -> REPOST: Question about cursors within a package.

REPOST: Question about cursors within a package.

From: JR <tommyumuc_at_aol.com>
Date: 27 Dec 2001 11:57:15 -0800
Message-ID: <5$--$$-$$_%%$-_%-$@news.noc.cabal.int>


I'm relatively new to coding in PL/SQL and have a very basic question concerning cursors within packages. Thanks in advance to anyone who takes the time to read this.

In the below CURSOR, I am unable to attain the information I need.

CURSOR c_puma_fr RETURN puma%ROWTYPE IS
SELECT a.type,a.state,a.county,a.mcd,a.tract,a.part,a.area,a.puma,a.pop,b.type2 FROM tr a, tbl2 b

WHERE  (a.tract = b.tract2)   AND
       (a.county = b.county2) AND
       (a.part = 'P')         AND
       (b.part2 = 'P')        AND
       (a.puma != b.puma2);

The field b.type2 causes a failure when it is included in the above cursor (which is itself in a PACKAGE BODY), but I need this field in the SELECT statement (somehow...).

The cursor fails (in the package body only) because of the last field listed in SELECT statement (I attempted an alias for this field, but to no avail). When I run the SQL block outside of the CURSOR, it works exactly as it should. When I remove from the SELECT statement any fields referencing the second table in the FROM clause, the SQL block runs within the calling program. I don't understand why the same SQL block that is within a CURSOR fails to work only when I attempt to list data from the second table (especially considering how the table alias of b is successfully applied in the WHERE clause). I could run the SQL block without the CURSOR and independent of the package, but I'm trying to keep all of the code I will be using for my project to just one package (I was able to get everything to work fine using VIEWS---but I hope that isn't the only way to do this). Is what I am attempting possible? Thanks.

Below are some possibly relevant data for this question:

SQL> desc tbl1;

 Name                            Null?    Type
 ------------------------------- -------- ----
 TYPE                            NOT NULL CHAR(1)
 STATE                                    CHAR(2)
 COUNTY                                   CHAR(3)
 MCD                                      VARCHAR2(5)
 TRACT                                    VARCHAR2(9)
 PART                                     CHAR(1)
 AREA                                     VARCHAR2(4)
 PUMA                                     VARCHAR2(5)
 POP                                      VARCHAR2(15)

SQL> desc tbl2;
 Name                            Null?    Type
 ------------------------------- -------- ----
 TYPE2                           NOT NULL CHAR(1)
 STATE2                                   CHAR(2)
 COUNTY2                                  CHAR(3)
 MCD2                                     VARCHAR2(5)
 TRACT2                                   VARCHAR2(9)
 PART2                                    CHAR(1)
 AREA2                                    VARCHAR2(4)
 PUMA2                                    VARCHAR2(5)
 POP2                                     VARCHAR2(15)

SQL> desc tr;
 Name                            Null?    Type
 ------------------------------- -------- ----
 TYPE                            NOT NULL CHAR(1)
 STATE                                    CHAR(2)
 COUNTY                                   CHAR(3)
 MCD                                      VARCHAR2(5)
 TRACT                                    VARCHAR2(9)
 PART                                     CHAR(1)
 AREA                                     VARCHAR2(4)
 PUMA                                     VARCHAR2(5)
 POP                                      VARCHAR2(15)

/* Package specification */
CREATE OR REPLACE PACKAGE p_puma AS

	CURSOR c_puma_t1 RETURN puma%ROWTYPE;
	CURSOR c_puma_t2 RETURN puma%ROWTYPE;
	CURSOR c_puma_tr RETURN puma%ROWTYPE;
	CURSOR c_puma_fr RETURN puma%ROWTYPE;

END;
/

/* Package body */
CREATE OR REPLACE PACKAGE BODY p_puma AS

	CURSOR c_puma_t1 RETURN puma%ROWTYPE IS
		SELECT 	*
		FROM   	puma
		WHERE  	mcd  IS NOT NULL AND
		       	puma IS NOT NULL AND
                       	type = 'T';

	CURSOR c_puma_t2 RETURN puma%ROWTYPE IS
		SELECT 	*
		FROM	puma
		WHERE	type = 'T'
		MINUS
		SELECT	*
		FROM	puma
		WHERE	mcd  IS NOT NULL AND
			puma IS NOT NULL AND
			type = 'T';

	CURSOR c_puma_tr RETURN puma%ROWTYPE IS
		SELECT 	          a.type,a.state,a.county,a.mcd,a.tract,a.part,a.area,a.puma,a.pop
		FROM	tbl1 a
		WHERE	a.puma IN (SELECT	b.puma2
				   FROM 	tbl2 b
				   WHERE	a.county=b.county2);

	CURSOR c_puma_fr RETURN puma%ROWTYPE IS
		SELECT a.type,a.state,a.county,a.mcd,a.tract,a.part,a.area,a.puma,a.pop
		FROM   tr a, tbl2 b
		WHERE  (a.tract = b.tract2)   AND
		       (a.county = b.county2) AND
                       (a.part = 'P')        AND
                       (b.part2 = 'P')        AND
                       (a.puma != b.puma2);

END;
/

/* Calling program */
BEGIN

END;
/

This message was cancelled from within Mozilla...not Received on Thu Dec 27 2001 - 13:57:15 CST

Original text of this message

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