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:
CREATE OR REPLACE PROCEDURE writeLog(line varchar2) iS file utl_file.file_type; BEGIN file := utl_file.fopen('/tmp', 'test.txt', 'A'); utl_file.put_line(file, line); utl_file.fflush(file); utl_file.fclose(file); dbms_output.put_line(line); 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:
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