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
