PROBLEM:

One of our procedure using utl_file to create a file and it is failing with below error.

SQL> declare
file_open utl_file.file_type;
begin
file_open:=utl_file.fopen(‘TEST1′,’dbaclassa.txt’,’w’);
utl_file.fclose(file_open);
end;
/ 2 3 4 5 6 7
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

SOLUTION:

1. Check the dba_directories, whether the directory TEST1 is present in database or not.

SQL> select * from dba_directories where directory_name='TEST1';

no rows selected

2. Create the directory as below:

SQL> create directory TEST1 as '/export/home/oracle';

Directory created.

3. Retry the operation

SQL> declare
 file_open utl_file.file_type;
begin
 file_open:=utl_file.fopen('TEST1','dbaclassa.txt','w');
 utl_file.fclose(file_open);
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.