Oracle Database: UTL_FILE_DIR init Parameter validation script

From Oracle 10g and above, Oracle have been encouraging users to use DIRECTORY instead of UTL_FILE_DIR. The new 'DIRECTORY' is very flexible to add, edit and delete than UTL_FILE_DIR. Whenever you want to include a directory structure to the UTL_FILE_DIR parameter a database bounce is required.

Below are couple of scripts to verify that directories listed in utl_file_dir or DIRECTORY_PATH is open to write/read. All these scripts are from Oracle Metalink. Replace the directory path (marked in red) with your value of utl_file_dir directory path or DIRECTORY_NAME.

Script(1):

set serveroutput on
DECLARE
   file_name   VARCHAR2 (256) := 'utlfile.txt';
   file_text   VARCHAR2 (256) := 'Hello World';
   file_id     UTL_FILE.file_type;
BEGIN
   file_id := UTL_FILE.fopen ('/u03/out/fromerp', file_name, 'W');
   UTL_FILE.put_line (file_id, file_text);
   UTL_FILE.fclose (file_id);
EXCEPTION
   WHEN UTL_FILE.INVALID_PATH
   THEN
      DBMS_OUTPUT.put_line ('Invalid path ' || SQLERRM);
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Others ' || SQLCODE || ' ' || SQLERRM);
END;
/


Another Script (2):

Step 1: Create  a test table

create table testtab (c1 number, c2 number);
insert into testtab values (10,25);
insert into testtab values (20,50);
commit;

Step 2: Create the procedute

CREATE OR REPLACE PROCEDURE utl_test1
IS
   file_handle        UTL_FILE.FILE_TYPE;       -- file handle of OS flat file
   col1               NUMBER;               -- C1 retrieved from testtab table
   retrieved_buffer   VARCHAR2 (100);         -- Line retrieved from flat file
BEGIN
   -- Open file to write into  and get it's file_handle
   file_handle :=
      UTL_FILE.FOPEN ('/u03/out/fromerp', 'myfile.txt', 'W');

   -- Write a line of text out to the file.
   UTL_FILE.PUT_LINE (file_handle, 'this is line 1 as a test');

   -- Select the c1 from the testtab table where empno = 7900
   SELECT c1
     INTO col1
     FROM testtab
    WHERE c2 = 25;

   -- Using PUTF write text with the col1 argument out to the file.
   UTL_FILE.PUTF (file_handle,
                  'This is the c1 %s when the c2 is %s.\n',
                  col1,
                  '25');

   -- Close the file.
   UTL_FILE.FCLOSE (file_handle);

   -- Open the same file to read from
   file_handle :=
      UTL_FILE.FOPEN ('/u03/out/fromerp', 'myfile.txt', 'R');

   -- Read a line from the file.
   UTL_FILE.GET_LINE (file_handle, retrieved_buffer);

   -- Print fetched line out to the SQL*PLUS prompt.
   DBMS_OUTPUT.PUT_LINE (retrieved_buffer);

   -- CLose the file.
   UTL_FILE.FCLOSE (file_handle);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('no_data_found');
      UTL_FILE.FCLOSE (file_handle);
   WHEN UTL_FILE.INVALID_PATH
   THEN
      DBMS_OUTPUT.PUT_LINE ('UTL_FILE.INVALID_PATH');
      UTL_FILE.FCLOSE (file_handle);
   WHEN UTL_FILE.READ_ERROR
   THEN
      DBMS_OUTPUT.PUT_LINE (' UTL_FILE.READ_ERROR');
      UTL_FILE.FCLOSE (file_handle);
   WHEN UTL_FILE.WRITE_ERROR
   THEN
      DBMS_OUTPUT.PUT_LINE ('UTL_FILE.WRITE_ERROR');
      UTL_FILE.FCLOSE (file_handle);
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('other stuff');
      UTL_FILE.FCLOSE (file_handle);
END;
/

To execute the above procedure:
set serveroutput on
execute utl_test1

This script also works to test 'DIRECTORY'.

Reference:
Using utl_file, how file permissions are determined, working sample (Doc ID 74268.1)
ORA-29280: invalid directory path' is returned when using spaces within the directory (Doc ID 1063519.6)
UTL_FILE_DIR path test : http://gavinsoorma.com/2009/07/utl_file_dir-path-test/