단순한 프로시져를 만드는데 오류가 생긴다.
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 권한을 줘야하다니..
