Re: Question about cursors within a package.

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 Received on Thu Dec 27 2001 - 14:27:34 CST