| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Question about cursors within a package.
In article <319333f5.0112271157.5fb43e82_at_posting.google.com>, tommyumuc_at_aol.com
says...
>
>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);
>
In your code (that works) you have:
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);
so, apparently PUMA%ROWTYPE has 9 attributes -- your cursor with type2 on the end has 10, it is NOT of type puma%rowtype.
no worry however, you should not code the way you are coding. Don't do in a loop what is more easily and readily done in a single statement. Just code:
insert into tb11 (type,state,county,mcd,tract,part,area,puma,pop) SELECT *
FROM puma
WHERE mcd IS NOT NULL AND
puma IS NOT NULL AND
type = 'T';
instead of:
FOR r_puma_t1 IN p_puma.c_puma_t1 LOOP
INSERT INTO tbl1(type,state,county,mcd,tract,part,area,puma,pop)
VALUES(r_puma_t1.type,r_puma_t1.state,r_puma_t1.county,
r_puma_t1.mcd,r_puma_t1.tract,r_puma_t1.part,
r_puma_t1.area,r_puma_t1.puma,r_puma_t1.pop);
END LOOP;
you don't need all of these cursors - it'll be much slower to
iterate/fetch/insert/fetch/insert -- then to just do it with an insert/select.
If you want to "hide" the query -- use a VIEW
insert into tb11 (type,state,county,mcd,tract,part,area,puma,pop) select * from V_PUMA_T1;
instead of creating a packaged cursor, create a VIEW.
>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
> -- Load tbl1
> FOR r_puma_t1 IN p_puma.c_puma_t1 LOOP
> INSERT INTO tbl1(type,state,county,mcd,tract,part,area,puma,pop)
> VALUES(r_puma_t1.type,r_puma_t1.state,r_puma_t1.county,
> r_puma_t1.mcd,r_puma_t1.tract,r_puma_t1.part,
> r_puma_t1.area,r_puma_t1.puma,r_puma_t1.pop);
> END LOOP;
> COMMIT; -- "Should" be implicit with the end of the for loop
> -- (but the commit wasn't automatically executed for
> -- first few runs of the code, which is why I
>included
> -- it explicitly).
>
> -- Load tbl2
> FOR r_puma_t2 IN p_puma.c_puma_t2 LOOP
> INSERT INTO tbl2(type2,state2,county2,mcd2,tract2,part2,area2,puma2,pop2)
> VALUES(r_puma_t2.type,r_puma_t2.state,r_puma_t2.county,
> r_puma_t2.mcd,r_puma_t2.tract,r_puma_t2.part,
> r_puma_t2.area,r_puma_t2.puma,r_puma_t2.pop);
> END LOOP;
> COMMIT;
>
> -- Load tr (table result set 1)
> FOR r_puma_tr IN p_puma.c_puma_tr LOOP
> INSERT INTO tr(type,state,county,mcd,tract,part,area,puma,pop)
> VALUES(r_puma_tr.type,r_puma_tr.state,r_puma_tr.county,
> r_puma_tr.mcd,r_puma_tr.tract,r_puma_tr.part,
> r_puma_tr.area,r_puma_tr.puma,r_puma_tr.pop);
> END LOOP;
> COMMIT;
>
> -- Display any output from p_puma.c_puma_fr
> FOR r_puma_fr IN p_puma.c_puma_fr LOOP
> dbms_output.put_line(r_puma_fr.type||' '||r_puma_fr.state||'
>'||r_puma_fr.county||
> r_puma_fr.mcd ||' '||r_puma_fr.tract||' '||r_puma_fr.part ||
> r_puma_fr.area||'
>'||r_puma_fr.puma ||' '||r_puma_fr.pop);
> END LOOP;
> COMMIT;
>
>END;
>/
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Thu Dec 27 2001 - 14:53:35 CST
 |
 |