Sunday, May 15, 2011

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;