weird cursor error with dba

From: <bplegend_at_yahoo.com>
Date: 28 Jul 2001 23:34:35 -0700
Message-ID: <5d0822ae.0107282234.6fdb171b@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 - 01:34:35 CDT

weird cursor error with dba_*