Oracle

Oracle UTL_FILE

Saurav Mitra

The Oracle supplied PL/SQL package UTL_FILE used to read and write operating system files that are located on the database server.

UTL_FILE

The Oracle Directory should be created as follows:

CONN SYS/SYS_PWORD AS SYSDBA 
CREATE OR REPLACE DIRECTORY ext_tab_dir AS 'C:\External_Tables';  
GRANT READ,WRITE ON DIRECTORY ext_tab_dir TO scott; 

Setting the init.ora Parameters:

utl_file_dir=C:\External_Tables 

UTL_FILE Commands

  1. UTL_FILE.FILE_TYPE : The datatype that can handle UTL File type variable.
  2. UTL_FILE.FOPEN : Function to open a file for read or write operations. FOPEN function accepts 4 arguments-
    • file_location[ ext_tab_dir ]
    • file_name[ emp.csv ]
    • open_mode [ i.e. 'R' or 'W' ]
    • max_linesize [ Optional field, accepts BINARY_INTEGER defining the linesize of read or write DEFAULT is NULL ]

  3. UTL_FILE.FOPEN_NCHAR : Function to open a multi byte character file for read or write operations. Same as FOPEN.
  4. UTL_FILE.FCLOSE: Close a file. FCLOSE accepts 1 argument-
    • file [ utl_type file variable ]
  5. UTL_FILE.FCLOSE_ALL: Closes all files.
  6. UTL_FILE.GET_LINE : Reads a Line from a file. GET_LINE function accepts 2 arguments-
    • file [ utl_type file variable ]
    • len [ String variable to store the line read ]
  7. UTL_FILE.GETLINE_NCHAR : Reads a Line from a multi-byte character file. Same as GET_LINE.
  8. UTL_FILE.PUT : Writes a string to a file. PUT function accepts 3 arguments-
    • file [ utl_type file variable ]
    • str [ String variable to write to file ]
    • autoflush [ BOOLEAN variable DEFAULT is FALSE ]
  9. UTL_FILE.PUT_NCHAR : Writes a unicode string to a file. Same as PUT.
  10. UTL_FILE.PUT_LINE : Writes a line to a file and appends a newline character. PUT_LINE function accepts 3 arguments-
    • file [ utl_type file variable ]
    • str [ String variable to write to file ]
  11. UTL_FILE.PUT_LINE_NCHAR : Writes a unicode line to a file and appends a newline character
  12. UTL_FILE.NEW_LINE : Writes one or more new line character to a file. NEW_LINE function accepts 2 arguments-
    • file [ utl_type file variable ]
    • lines [ Number of new line characters ].
  13. UTL_FILE.IS_OPEN: Returns True if the file is Open Otherwise False. IS_OPEN accepts 1 argument-
    • file [utl_type file variable].
  14. UTL_FILE.FFLUSH : Writes pending data to the file. FFLUSH accepts 1 argument-
    • file [utl_type file variable].

UTL_FILE Exceptions

  • utl_file.invalid_filename
  • utl_file.access_denied
  • utl_file.file_open
  • utl_file.invalid_path
  • utl_file.invalid_mode
  • utl_file.invalid_filehandle
  • utl_file.invalid_operation
  • utl_file.read_error
  • utl_file.write_error