How to Solve ORA-29280: invalid directory path

Last updated on April 4th, 2019 at 02:04 am

ORA-29280: invalid directory path  is a common error when doing file handling operation in Oracle database

Reason and Resolution for the error

1.This occur 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 directory in UTL_FILE_DIr

Steps would be 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;
/
  2    3    4    5    6
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. Please do like and provide the feedback

Related Articles

How to solve ORA-29283 : invalid file operation

ORA-01652: unable to extend temp segment: Solutions

How to resolve the ORA-00936 missing expression

ORA-00257: archiver error. Connect internal only, until freed.

Leave a Reply