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