Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
Oracle

Oracle UTL_FILE

Updated on Sep 29, 2020

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

  • UTL_FILE.FILE_TYPE : The datatype that can handle UTL File type variable.
  • UTL_FILE.FOPEN : Function to open a file for read or write operations. FOPEN function accepts 4 arguments-
  1. file_location[ ext_tab_dir ]
  2. file_name[ emp.csv ]
  3. open_mode[ i.e. 'R' or 'W' ]
  4. max_linesize [ Optional field, accepts BINARY_INTEGER defining the linesize of read or write DEFAULT is NULL ]
  • UTL_FILE.FOPEN_NCHAR : Function to open a multi byte character file for read or write operations. Same as FOPEN.
  • UTL_FILE.FCLOSE: Close a file. FCLOSE accepts 1 argument-
  1. file [ utl_type file variable ]
  • UTL_FILE.FCLOSE_ALL: Closes all files.
  • UTL_FILE.GET_LINE : Reads a Line from a file. GET_LINE function accepts 2 arguments-
  1. file [ utl_type file variable ]
  2. len [ String variable to store the line read ]
  • UTL_FILE.GETLINE_NCHAR : Reads a Line from a multi-byte character file. Same as GET_LINE.
  • UTL_FILE.PUT : Writes a string to a file. PUT function accepts 3 arguments-
  1. file [ utl_type file variable ]
  2. str [ String variable to write to file ]
  3. autoflush [ BOOLEAN variable DEFAULT is FALSE ]
  • UTL_FILE.PUT_NCHAR : Writes a unicode string to a file. Same as PUT.
  • UTL_FILE.PUT_LINE : Writes a line to a file and appends a newline character. PUT_LINE function accepts 3 arguments-
  1. file [ utl_type file variable ]
  2. str [ String variable to write to file ]
  • UTL_FILE.PUT_LINE_NCHAR : Writes a unicode line to a file and appends a newline character
  • UTL_FILE.NEW_LINE : Writes one or more new line character to a file. NEW_LINE function accepts 2 arguments-
  1. file [ utl_type file variable ]
  2. lines [ Number of new line characters ]
  • UTL_FILE.IS_OPEN: Returns True if the file is Open Otherwise False. IS_OPEN accepts 1 argument-
  1. file [utl_type file variable].
  • UTL_FILE.FLUSH: Writes pending data to the file. FFLUSH accepts 1 argument-
  1. 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