Re: weird cursor error with dba_*

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
Received on Sun Jul 29 2001 - 14:00:57 CDT