Re: weird cursor error with dba_*

bplegend_at_yahoo.com (bplegend_at_yahoo.com) wrote in message news:<5d0822ae.0107291100.252188b0_at_posting.google.com>...
> If I removed the stored procedure heading, it worked perfectly fine
> with DBA_*, ALL_* and USER_*. So... I must be missing some rules with
> PL/SQL with DBA_*. Can anyone shed some light on this mistery?
>
> benny
>
> bplegend_at_yahoo.com (bplegend_at_yahoo.com) wrote in message news:<5d0822ae.0107282234.6fdb171b_at_posting.google.com>...
> > Hi all,
> >
> > Sorry for the long post.
> >
> > I have a weird PL/SQL problem and I wish someone can shed some light
> > on. I was writing a procedure using cursor to read dba_*. I had no
> > clue why it worked for all_*, USER_* but not dba_*. As you see at the
> > bottom, I can read the dba_* tables for sure. Someone suggested me
> > look at the base table and I did re-grant select on those base tables
> > to 'SYSTEM' and the stored procedure did sucessfully create. Now what
> > I don't understand is both ALL_* and DBA_* are based on the same set
> > of tables. It is still a mistery why it worked with ALL_* but not
> > DBA_* unless I grant select on the base tables.
> >
> > Perhaps I am missing something here? or if you want, could you try
> > this on your machine? I have done on 7.3.4 and 816. Both are not
> > working with dba_*.
> >
> > Thank you very much in advance,
> >
> > benny
> >
> >
> > ---- cut here ----
> > % sqlplus system/...
> > Connected to:
> > Oracle8i Enterprise Edition Release 8.1.6.0.0 -
> > Production
> > With the Partitioning option
> > JServer Release 8.1.6.0.0 - Production
> >
> > SQL> create or replace procedure test
> > as
> > cursor ctabs is select table_name from SYS.all_tables;
> > begin
> > for rtabs in ctabs loop
> > dbms_output.put_line(rtabs.table_name);
> > end loop;
> > end;
> > / 2 3 4 5 6 7 8 9
> >
> > Procedure created.
> >
> > SQL> 3
> > 3* cursor ctabs is select table_name from
> > SYS.all_tables;
> > SQL> c/all/dba
> > 3* cursor ctabs is select table_name from
> > SYS.dba_tables;
> > SQL> /
> >
> > Warning: Procedure created with compilation errors.
> >
> > SQL> show errors
> > Errors for PROCEDURE TEST:
> >
> > LINE/COL ERROR
> > --------
> > -----------------------------------------------------------------
> > 3/17 PL/SQL: SQL Statement ignored
> > 3/40 PLS-00201: identifier 'SYS.DBA_TABLES' must
> > be declared
> > 6/7 PL/SQL: Statement ignored
> > 6/28 PLS-00364: loop index variable 'RTABS' use is
> > invalid
> > SQL> list
> > 1 create or replace procedure test
> > 2 as
> > 3 cursor ctabs is select table_name from
> > SYS.dba_tables;
> > 4 begin
> > 5 for rtabs in ctabs loop
> > 6 dbms_output.put_line(rtabs.table_name);
> > 7 end loop;
> >
> > SQL> select * from dba_tables where 0=1;
> >
> > no rows selected

This caused by the fact roles are not being used when you compile a pl/sql procedure, as roles are volatile and can change on the fly. As an anonymous block will execute immediately, roles are being used during compilation of anynomous block.
The difference between dba_ and all_ is that the former has been granted to the role dba, and the latter to the special user PUBLIC (Public is not a role).

In 7.3.4 the only way around this is having sys granting directly to the owner of your procedure.
In 8i and beyond you can create the procedure with invokers rights (authid invoker), and it will use the privileges of the schema running the procedure.
So, yes your procedures are working as designed, and the behavior is well documented both by Oracle and in this newsgroup.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Mon Jul 30 2001 - 03:02:51 CDT