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 -> Re: Question about cursors within a package.

Re: Question about cursors within a package.

From: JR <tommyumuc_at_aol.com>
Date: 28 Dec 2001 05:09:45 -0800
Message-ID: <319333f5.0112280509.b2da3df@posting.google.com>


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. Received on Fri Dec 28 2001 - 07:09:45 CST

Original text of this message

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