CODEWITHSUNDEEP
oracle-databaseplsqlsqlplus
O. Sam
O. Sam

Reputation: 178

UTL_FILE: Could not create a new file

I'm using this statement in ly code to create/open à file: create it if does not exist yet/open it if already exists.

   w_file_handle := utl_file.fopen ('SAUV_DIR',
                                'sauv_tab_tbrcs_params.txt' ,
                                'W') ;

The file doesn't exist yet but it couldn't be created.

I got this error:

SQL> @MyScript.sql
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 14

Any ideas to create the file if doesn't exist yet?

Upvotes: 0

Views: 5903

Answers (1)

XING
XING

Reputation: 9886

You can refer this code to create a file in your directory.

I created a tt.sql file with the below code. I checked that i have a working directory 'BDUMP`.

Directory Check:

SQL> SELECT DIRECTORY_NAME , DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'BDUMP';

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ---------------
BDUMP                          /home/fil_test/

Change directory permission. By default it has only Read and execute permission for others.

terminal$ chmod 777 fil_test

Block:

DECLARE
   fHandle   UTL_FILE.FILE_TYPE;
BEGIN
   fHandle := UTL_FILE.FOPEN ('BDUMP', 'test_file', 'w');

   UTL_FILE.PUT (fHandle, 'This is the first line');
   UTL_FILE.PUT (fHandle, 'This is the second line');
   UTL_FILE.PUT_LINE (fHandle, 'This is the third line');

   UTL_FILE.FCLOSE (fHandle);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'Exception: SQLCODE=' || SQLCODE || '  SQLERRM=' || SQLERRM);
      RAISE;
END;
/

Execution:

SQL> @tt.sql

PL/SQL procedure successfully completed.

And i See the file created:

terminal$ ls -lrt test_file*
-rw-r-----   1 oracle   dba           68 Oct 24 14:49 test_file

Upvotes: 1

Related Questions