Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> REPOST: Question about cursors within a package.
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
This message was cancelled from within Mozilla...not Received on Thu Dec 27 2001 - 13:57:15 CST