| 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.
Sybrand Bakker <oradba_at_sybrandb.demon.nl> wrote in message news:<to0n2u4a38mclvaak9kbvdl9s9020i0tdn_at_4ax.com>...
> On 27 Dec 2001 11:57:15 -0800, tommyumuc_at_aol.com (JR) wrote:
>
> >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
> > -- 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;
> >/
>
>
> You must have privilege to your table by means of a *role* and not
> *directly*.
> By default, roles are ignored during the compilation of packages, as
> roles are volatile and there is no guarantee at all it will work at
> runtime.
> In Oracle 8.0 and before the only way to get around this is to make
> sure you have direct privileges. Usually making sure the owner of the
> tables also owns the package also work, but there are situations in
> which this isn't possible.
> In Oracle 8i and higher you have the authid definer (which is the
> default behavior described above) and the authid invoker. In the
> latter case Oracle will check at runtime against the privileges of the
> calling user.
> Further details should be available either in the pl/sql manual or in
> the Oracle Application Developers Guide. You can of course always use
> the searchable docs at http://tahiti.oracle.com
>
> Hth
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Thank you for replying. I will check to see whether I have priviledge to the package I created by role; if not, I (or the DBA in our office) will set the priviledges accordingly.
This message was cancelled from within Mozilla...not Received on Fri Dec 28 2001 - 07:05:02 CST
 |
 |