| 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 -> REPOST: Re: Question about cursors within a package.
Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<a0g1of02j33_at_drn.newsguy.com>...
> 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;
> >/
Thanks for replying. Your right--there isn't a reason to use the loop for inserting the data into the tables. I'll change that part of my code.
This message was cancelled from within Mozilla...not Received on Fri Dec 28 2001 - 07:09:45 CST
 |
 |