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
By continuing you indicate that you agree to Terms of Service and Privacy Policy of the site.
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
PrimeChess

PrimeChess.org

PrimeChess.org makes elite chess training accessible and affordable for everyone. For the past 6 years, we have offered free chess camps for kids in Singapore and India, and during that time, we also observed many average-rated coaches charging far too much for their services.

To change that, we assembled a team of top-rated coaches including International Masters (IM) or coaches with multiple IM or GM norms, to provide online classes starting from $50 per month (8 classes each month + 4 tournaments)

This affordability is only possible if we get more students. This is why it will be very helpful if you could please pass-on this message to others.

Exclucively For Indian Residents: 
Basic - ₹1500
Intermediate- ₹2000
Advanced - ₹2500

Top 10 Articles