UTL_FILE Utility Basic Setups

DB and Server Setups

1.       The directory path on the application node will need to be mounted/shared on the database node.

2.      The directory path is readable/writable from the database node.

3.       The directory path should have required permissions.

DB Level - Verification SQL

SELECT directory_name,

       directory_path

  FROM sys.dba_directories

WHERE directory_name = 'CUSTOM_DIR';

SELECT grantee,

       privilege,

       table_name

  FROM sys.dba_tab_privs

WHERE table_name = 'CUSTOM_DIR';

Server Level - Verification Command

ls -altr CUSTOM_DIR_PATH

Basic Code with Important Exceptions

DECLARE

   lf_utl_file_id      UTL_FILE.file_type;

   lv_dba_dir_name     VARCHAR2 (30) := 'CUSTOM_DIR';

   lv_data_file_name   VARCHAR2 (80) := 'outbound_file.txt';

   lv_utl_file_mode    VARCHAR2 (1) := 'w';    -- 'w', 'r'

   lv_data_line        VARCHAR2 (2000);

BEGIN

   IF NOT UTL_FILE.is_open (lf_utl_file_id)

   THEN

      lf_utl_file_id      :=

         UTL_FILE.fopen (lv_dba_dir_name,

                         lv_data_file_name,

                         lv_utl_file_mode,

                         32767);

   END IF;

   lv_data_line   := '';             -- Extract Data

   UTL_FILE.put_line ( lf_utl_file_id, lv_data_line);

   UTL_FILE.fclose (lf_utl_file_id);

EXCEPTION

   WHEN UTL_FILE.invalid_mode

   THEN

      raise_application_error ( -20051, 'Invalid Mode Parameter');

   WHEN UTL_FILE.invalid_path

   THEN

      raise_application_error ( -20052, 'Invalid File Location');

   WHEN UTL_FILE.invalid_filehandle

   THEN

      raise_application_error ( -20053, 'Invalid Filehandle');

   WHEN UTL_FILE.invalid_operation

   THEN

      raise_application_error ( -20054, 'Invalid Operation');

   WHEN UTL_FILE.read_error

   THEN

      raise_application_error ( -20055, 'Read Error');

   WHEN UTL_FILE.internal_error

   THEN

      raise_application_error ( -20057, 'Internal Error');

   WHEN UTL_FILE.charsetmismatch

   THEN

      raise_application_error ( -20058, 'Opened With FOPEN_NCHAR, But Later I/O Inconsistent');

   WHEN UTL_FILE.file_open

   THEN

      raise_application_error ( -20059, 'File Already Opened');

   WHEN UTL_FILE.invalid_maxlinesize

   THEN

      raise_application_error ( -20060, 'Line Size Exceeds 32K');

   WHEN UTL_FILE.invalid_filename

   THEN

      raise_application_error ( -20061, 'Invalid File Name');

   WHEN UTL_FILE.access_denied

   THEN

      raise_application_error ( -20062, 'File Access Denied');

   WHEN UTL_FILE.invalid_offset

   THEN

      raise_application_error ( -20063, 'FSEEK Param Less Than 0');

   WHEN OTHERS

   THEN

      raise_application_error ( -20099, 'Unknown UTL_FILE Error');

END;