Home » Oracle » Oracle Database » How to Solve ORA-29280: invalid directory path

How to Solve ORA-29280: invalid directory path

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

ORA-29280: invalid directory path

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

See also  What is subqueries in oracle

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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top