PL/SQL: ORA-00942: table or view does not exist

단순한 프로시져를 만드는데 오류가 생긴다. 

 

 PL/SQL: ORA-00942: table or view does not exist  에러내용은 이것이지만

실제로 테이블이 존재한다. 

 

sqlplus / as sysdba 

grant dba to TDBA;

 

 

CREATE OR REPLACE PROCEDURE TDBA.test

     (v_str_dt    IN    date, v_end_dt IN date) 

     IS 

     aa varchar2(20) :=null;

     BEGIN 

 

        select OWNER into aa

        from dba_objects

        where created between to_date(v_str_dt,'yyyymmdd') and to_date(v_end_dt,'yyyymmdd') 

        and owner not like 'SYS%';

 

     END test; 

     /

 

Warning: Procedure created with compilation errors.

 

SQL> SHOW ERROR

Errors for PROCEDURE TDBA.TEST:

 

LINE/COL ERROR

-------- -----------------------------------------------------------------

6/9      PL/SQL: SQL Statement ignored

7/14     PL/SQL: ORA-00942: table or view does not exist

 

 

 

이것저것 삽질을 해보고 설마 했는데, select 권한을 주니까 정상적으로 created 됨.

 

SQL> grant select on dba_objects to tdba;

 

 

SQL> CREATE OR REPLACE PROCEDURE tdba.test

     (v_str_dt    IN    date, v_end_dt IN date)

  2    3       IS

     aa varchar2(20) :=null;

     BEGIN

 

        select OWNER into aa

        from dba_objects  4    5    6    7    8

  9          where created between to_date(v_str_dt,'yyyymmdd') and to_date(v_end_dt,'yyyymmdd')

        and owner not like 'SYS%';

 

     END test;

     / 10   11   12   13

 

Procedure created.

 

 

헐.. DBA 권한이 있어도 일일이 테이블 select 권한을 줘야하다니..