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;