procedure儲存過程呼叫dba字首的字典dba_objects

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 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亦不可