SQL> create or replace procedure proc_dba
  2  as
  3  v_obj dba_objects.OBJECT_NAME%type;
  4  cursor cur_dba is select object_name from dba_objects where wner='SCOTT' and object_name='BASE_BILL';
  5  begin
  6  open cur_dba;
  7  fetch cur_dba into v_obj;
  8  exit when cur_dba%notfound;
  9  close cur_dba;
 10  end;
 11  /
 
Warning: Procedure created with compilation errors

---存储过程调用dba_objects报错
SQL> show err
Errors for PROCEDURE SCOTT.PROC_DBA:
 
LINE/COL ERROR
-------- ------------------------------------------------------------------------------------
3/7      PLS-00201: identifier 'DBA_OBJECTS' must be declared
3/7      PL/SQL: Item ignored
4/43     PL/SQL: ORA-00942: table or view does not exist
4/19     PL/SQL: SQL Statement ignored
7/20     PLS-00320: the declaration of the type of this expression is incomplete or malformed
7/1      PL/SQL: SQL Statement ignored
8/1      PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside a loop
8/1      PL/SQL: Statement ignored
 
SQL> ed
SQL>
SQL>
SQL>
SQL>
--把dba_objects改为user_objects则可以正确运行
SQL> create or replace procedure proc_dba
  2  as
  3  v_obj user_objects.OBJECT_NAME%type;
  4  cursor cur_dba is select object_name from user_objects where wner='SCOTT' and object_name='BASE_BILL';
  5  begin
  6  open cur_dba;
  7  fetch cur_dba into v_obj;
  8  exit when cur_dba%notfound;
  9  close cur_dba;
 10  end;
 11  /
 
Warning: Procedure created with compilation errors
 
SQL> show err
Errors for PROCEDURE SCOTT.PROC_DBA:
 
LINE/COL ERROR
-------- ------------------------------------------------------------------------
4/62     PL/SQL: ORA-00904: "OWNER": invalid identifier
4/19     PL/SQL: SQL Statement ignored
8/1      PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside a loop
8/1      PL/SQL: Statement ignored
 
SQL> ed
SQL>
SQL> create or replace procedure proc_dba
  2  as
  3  v_obj user_objects.OBJECT_NAME%type;
  4  cursor cur_dba is select object_name from user_objects where  object_name='BASE_BILL';
  5  begin
  6  open cur_dba;
  7  fetch cur_dba into v_obj;
  8  exit when cur_dba%notfound;
  9  close cur_dba;
 10  end;
 11  /
 
Warning: Procedure created with compilation errors
--exit必须包括在loop及end loop中
SQL> show err
Errors for PROCEDURE SCOTT.PROC_DBA:
 
LINE/COL ERROR
-------- ------------------------------------------------------------------------
8/1      PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside a loop
8/1      PL/SQL: Statement ignored
 
SQL> ed
SQL>
SQL>
SQL> create or replace procedure proc_dba
  2  as
  3  v_obj user_objects.OBJECT_NAME%type;
  4  cursor cur_dba is select object_name from user_objects where  object_name='BASE_BILL';
  5  begin
  6  open cur_dba;
  7  loop
  8  fetch cur_dba into v_obj;
  9  exit when cur_dba%notfound;
 10  close cur_dba;
 11  end loop;
 12  end;
 13  /
 
Procedure created
 
SQL> ed
SQL>
SQL>
SQL> create or replace procedure proc_dba
  2  authid current_user is
  3  v_obj dba_objects.OBJECT_NAME%type;
  4  cursor cur_dba is select object_name from dba_objects where  object_name='BASE_BILL' and wner='SCOTT';
  5  begin
  6  open cur_dba;
  7  loop
  8  fetch cur_dba into v_obj;
  9  exit when cur_dba%notfound;
 10  close cur_dba;
 11  end loop;
 12  end;
 13  /
 
Warning: Procedure created with compilation errors
--添加选项authid current_user is依旧报dba_objects不存在
SQL> show err
Errors for PROCEDURE SCOTT.PROC_DBA:
 
LINE/COL ERROR
-------- ------------------------------------------------------------------------------------
3/7      PLS-00201: identifier 'DBA_OBJECTS' must be declared
3/7      PL/SQL: Item ignored
4/43     PL/SQL: ORA-00942: table or view does not exist
4/19     PL/SQL: SQL Statement ignored
8/20     PLS-00320: the declaration of the type of this expression is incomplete or malformed
8/1      PL/SQL: SQL Statement ignored

---以sysdba授权select any table
C:\Users\123>sqlplus sys/system@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 28 14:40:12 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant select any table to scott;

Grant succeeded.

SQL> create or replace procedure proc_dba
  2  authid current_user is
  3  v_obj dba_objects.OBJECT_NAME%type;
  4  cursor cur_dba is select object_name from dba_objects where  object_name='BASE_BILL' and wner='SCOTT';
  5  begin
  6  open cur_dba;
  7  loop
  8  fetch cur_dba into v_obj;
  9  exit when cur_dba%notfound;
 10  close cur_dba;
 11  end loop;
 12  end;
 13  /
 
Warning: Procedure created with compilation errors
--授权select any table依旧报错
SQL> show err
Errors for PROCEDURE SCOTT.PROC_DBA:
 
LINE/COL ERROR
-------- ------------------------------------------------------------------------------------
3/7      PLS-00201: identifier 'DBA_OBJECTS' must be declared
3/7      PL/SQL: Item ignored
4/43     PL/SQL: ORA-00942: table or view does not exist
4/19     PL/SQL: SQL Statement ignored
8/20     PLS-00320: the declaration of the type of this expression is incomplete or malformed
8/1      PL/SQL: SQL Statement ignored

--再把select on dba_objects授权
SQL> grant select on dba_objects to scott;

Grant succeeded.

这下可以了
SQL> /
 
Procedure created

小结：1,在存储过程调用dba前缀的数据字典，必须要显式授权

            grant select on dba_objects to scott;

             2,授权dba前缀的字典仅sysdba具备此权力,dba亦不够

            3,以sysdba授权select any table亦不可