UTL_FILE

• 
• 

Details

Category: Database Oracle

Published on Wednesday, 28 April 2010 17:18

Written by Saurav Mitra

Hits: 851

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 Properties

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-

• 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]

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-

• 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-

• file [utl_type file variable]
• 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-

• file [utl_type file variable]
• str [String variable to write to file]
• 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-

• file [utl_type file variable]
• 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-

• file [utl_type file variable]
• lines [Number of new line characters].

UTL_FILE.IS_OPEN: Returns True if the file is Open Otherwise False. IS_OPEN accepts 1 argument-

• file [utl_type file variable].

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

• < Prev
• Next >