Oracle: enable UTL_FILE.PUT_LINE to write output to a file

Today I wanted to log some database output to a text file with UTL_FILE.PUT_LINE but when I called this procedure:

  1.   file UTL_FILE.file_type;
  2.   file := UTL_FILE.fopen(‚/tmp‘, ‚test.txt‘, ‚A‘);
  3.   UTL_FILE.put_line(file, line);
  4.   UTL_FILE.fflush(file);
  5.   UTL_FILE.fclose(file);
  6.   DBMS_OUTPUT.put_line(line);
  7. END writeLog;

it produced an ORA-29280 error: invalid directory path:

ORA-29280: invalid directory path
ORA-06512: in "SYS.UTL_FILE", line 29
ORA-06512: in "SYS.UTL_FILE", line 448
ORA-06512: in "AOM.WRITELOG", line 5

Apparently, you need to set the list of file system directories Oracle is allowed to write to (parameter UTL_FILE_DIR) in the database’s configuration. I found this article describing the needed action: Initialization Parameter files: PFILEs vs. SPFILEs.

After entering this simple command and restarting the database it worked:

  1. ALTER SYSTEM SET utl_file_dir=‚/tmp‘ SCOPE=BOTH;

Über uns Stefan

Polyglot Clean Code Developer

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax