Testing whether utl_file_dir is set

I found some sample code provided by Oracle for testing utl_file_dir

Metalink Note 45327.1 gives some good procedures to test whether pl/sql code calling utl_file package is able to write to the directories defnied in utl_file_dir

Metalink Note 1016653.4 gives this code to test to verify setup for UTL_FILE Package
DECLARE
fHandle UTL_FILE.FILE_TYPE;
vText varchar2(10);
BEGIN
fHandle := UTL_FILE.FOPEN('/usr/tmp','utlfile.txt','w');
vText := 'TEST'; UTL_FILE.PUTF(fHandle,vText);
UTL_FILE.FCLOSE(fHandle);
EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
WHEN UTL_FILE.INVALID_OPERATION then
RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');
WHEN UTL_FILE.INVALID_FILEHANDLE then
RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');
WHEN UTL_FILE.WRITE_ERROR then
RAISE_APPLICATION_ERROR(-20104,'Write Error');
WHEN UTL_FILE.READ_ERROR then
RAISE_APPLICATION_ERROR(-20105,'Read Error');
WHEN UTL_FILE.INTERNAL_ERROR then
RAISE_APPLICATION_ERROR(-20106,'Internal Error');
WHEN OTHERS THEN
UTL_FILE.FCLOSE(fHandle);
END;
/

Minimize usage of utl_file_dir

I found a very good metalink note 206272.1 about utl_file package. They have recommended using CREATE DIRECTORY feature instead of using utl_file_dir directories which needs the DB to be bounced every time a new directory is added. Here's a copy paste from the note:

PURPOSE
Introduces the Oracle 9i Release 2 way of using the UTL_FILE package.

SCOPE & APPLICATION

This article is intended for programmers who are familiar with the usage of the
UTL_FILE package.

NEW METHOD OF USING UTL_FILE PACKAGE IN 9.2 9iR2

In the past(i.e. pre 9iR2), accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However ,UTL_FILE_DIR access is not recommended in 9iR2. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools.CREATE DIRECTORY
privilege is granted only to SYS and SYSTEM by default.

EXAMPLE
-------
Step 1) Connect as sys

Step 2) Create a directory:

SQL> CREATE DIRECTORY log_dir AS 'C:\NEW';

Directory created.

Step 3) Grant read and write on the directory

SQL> grant read on directory log_dir to public;
SQL> grant write on directory log_dir to public;

Grant succeeded.

Step 4) Create function as a NON-SYS USER

SQL>CONNECT SCOTT/

create or replace function f1 return number is
v_output_file1 utl_file.file_type;
begin
v_output_file1 := utl_file.fopen('LOG_DIR', 'NEW.txt', 'a');
utl_file .put_line(v_output_file1, 'NATURE and Beauty');
utl_file.fclose_all;
return 1;
end;
/

Step 6) Create a bind variable

SQL>var x number

Step 7) Execute the function

SQL> exec :x:=f1;

PL/SQL procedure successfully completed.

NOTE
Care must be taken to use uppercase for the DIRECTORY object in the call to
utl_file.fopen, else you may encounter the ORA-29280: invalid directory path
error.

DBA_DIRECTORIES is the dictionary table which can give you details about all the directory objects created till now.

select directory_name,directory_path

from dba_directories;

The above query will give you the details of all directory object names and the paths associated with them.

There is no command called ALTER DIRECTORY yet. To modify a directory path you need to give the command:

CREATE OR REPLACE DIRECTORY directory_name as 'path'

In which directory does FND_FILE write ?

Just now I was asked this question. FND_FILE writes to which directory ? Oracle documentation says that FND_FILE writes to $APPLPTMP. This happens only when the $APPLPTMP directory is the first directory in the db init parameter utl_file_dir. If it so happens that /usr/tmp is the first directory in your utl_file_dir, FND_FILE is going to write to /usr/tmp and not to $APPLPTMP. This can be tested:

If you go to sqlplus and exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');
A new file with a name like lnnnnnnnn.tmp is created in /usr/tmp containing the text: 'THIS IS A TEST'. /usr/tmp is the first directory in utl_file_dir.

Metalink Note 261693.1 has information on troubleshooting ORA-20100 on Concurrent Processing, which is where FND_FILE is involved.