ORA-29280: invalid directory path is a common error when doing file handling operations in the Oracle database
Reason and Resolution for the error
(1) This occurs if the directory specified does not exists in utl_file_dir init.ora parameter
sqlplus / as sysdba
show parameter utl_file_dir
/tmp, /usr/tmp/
SQL> declare
F_LOG utl_file.file_type;
begin
F_LOG := utl_file.fopen('/u500','j', 'w');
end;
/ 2 3 4 5 6
declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 4
Resolutions
a. Change the directory which is defined in UTL_FILE_DIR
b. If the directory cannot be changed and we have to use the same code, we will need to add the directory in UTL_FILE_DIr
Steps would be the first change in spfile, then recycle oracle database and then check
sqlplus / as sysdba
alter system set utl_file_dir='/tmp/','/usr/tmp','/u500' scope =spfile;
shutdown immediate
startup
SQL> declare
F_LOG utl_file.file_type;
begin
F_LOG := utl_file.fopen('/u500','y', 'w');
end;
/
2 3 4 5 6
PL/SQL procedure successfully completed.
c. If we can change the code, we can start using oracle directories. In that case, we dont need to specify utl_file_dir
SQL> create directory TESTDIR as '/u500';
SQL> grant read,write on directory TESTDIR to public;
SQL> declare
F_LOG utl_file.file_type;
begin
F_LOG := utl_file.fopen('TESTDIR','y', 'w');
end;
/
PL/SQL procedure successfully completed.
2. You specify the wrong oracle directory name into it
SQL> declare
F_LOG utl_file.file_type;
begin 2
F_LOG := utl_file.fopen('TESTDIR','y', 'w');
end;
3 4 5 6 /
declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 4
SQL> select * from dba_directories where DIRECTORY_NAME='TESTDIR';
no rows selected
Resolutions
Specify the correct directory name or create the new one
SQL> create directory TESTDIR as '/u500';
SQL> grant read,write on directory TESTDIR to public;
SQL> declare
F_LOG utl_file.file_type;
begin
F_LOG := utl_file.fopen('TESTDIR','y', 'w');
end;
/
2 3 4 5 6
PL/SQL procedure successfully completed.
I hope you like the detailed explanation of the ORA-29280 error. Please do like and provide the feedback
Related Articles
ORA-29283 : invalid file operation : check out this post on how to resolve ORA-29283 : invalid file operation. What permission need to be checked
ORA-29285: file write error :ORA-29285: file write error is the common error while doing file handling operation.Check out this post on various reason and solution on how to solve it
ORA-01652: unable to extend temp segment :ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database
ORA-00936 missing expression :Learn troubleshooting ORA-00936 missing expression in oracle SQL.what are various solution, how we can avoid it, Oracle bugs for this errors
ORA-00257: archiver error. Connect internal only, until freed. : Learn how to troubleshoot for ORA-00257 archiver error. Connect internal only error.Various resolution and example provided in step by step manner.
ORA-00904 : This post for the description and possible solutions of ORA-00904: invalid identifier.troubleshooting tips is also provided
FND_FILE in oracle apps
https://docs.oracle.com/database/121/ARPLS/u_file.htm