PLSQL : Open file in write mode

Question

It is probably something simple but I ask it anyway :

I want to popen a file in write mode (not appending), it starts from the beginning. If the file does not exist, it should be created it.

I tried this :

ficErrors := UTL_FILE.FOPEN(myDirectory, errorsFilename, 'W');

I have this error :

ORA-29283 invalid file operation ORA-06512 at "SYS.UTL_FILE"

ty

Answer 1

Do it like:

Declare

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

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

  UTL_FILE.FCLOSE(fHandle);
END;

where BDUMP = is the directory where my file will be created. It's on the server where Oracle is installed.

See Demo:

SQL> Declare
        fHandle  UTL_FILE.FILE_TYPE;
      begin
          fHandle := UTL_FILE.FOPEN('BDUMP', 'test_file', 'w');

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

          UTL_FILE.FCLOSE(fHandle);
      END;  
  /

PL/SQL procedure successfully completed.

Output:

unixterminal$ ls -lrt test_file
-rw-r--r--   1 oracle   dba           70 Mar 10 15:21 test_file

Note:

If the oracle OS user does not have the appropriate privileges on the OS directory, or if the path specified in the database does not match to an actual path, the program will hurl this exception:

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation