Tuesday, February 23, 2021

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error

ORA-00942: table or view does not exist 


I got it in while compiling procedure, because eventually I found it became invalid state.


I tried to create new directory and give read/write permission on it and change inside procedure, but it still was INVALID. But instead


inside procedure I put hard code path of that directory it  was success.


Like:

create or replace procedure `procedure_name`
(
`variable` IN varchar2
)
AS
`variable` varchar2(2000) := '/../../dir';

begin

    select ... into `variable` from dba_directories whhere directnory_name = '...';
    ....
    ....

end;



But it is not make sense. Then after google I found to overcome that issue, all I need to grant ALL on dba_directories to user not only READ, WRITE.


As SYS

grant all on dba_diretories to `user`'

then I was able to compile successfully original procedure, no longer need to manipulate procedure.

SQL> set linesize 300
SQL> col object_name for a50
SQL> col owner for a30
SQL> select owner, object_name, object_type from dba_objects where status = 'INVALID';  2

OWNER       OBJECT_NAME OBJECT_TYPE
----------------------       --------------------------------                          -----------------
`USER`            `PROCEDURE_NAME`  PROCEDURE

SQL> alter procedure `user`.`precedure_name` compile;
Procedure altered.

SQL> select owner, object_name, object_type from dba_objects where status = 'INVALID';
no rows selected



Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...