Oracle UTL_FILE

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