UTL_FILE Page - 4

Page 4 of 5

Author: Saurabh Gupta Published on: 10th Aug 2011

UTL_FILE

UTL_FILE: Handling Exceptions

There are situations where UTL_FILE fails to operate due to junk input to subprogram or junk data in the source text file. Apart from normal NO_DATA_FOUND and VALUE_ERROR, UTL_FILE maintains list of exceptions pre-defined in its specification, as listed below.

Exception Remarks

INVALID_PATH Raised if the file location is invalid
INVALID_MODE Raised when invalid open mode parameter is given in FOPEN subprogram
INVALID_FILEHANDLE Raised for invalid file handle
INVALID_OPERATION Raised when invalid operation is attempted on a file
READ_ERROR Raised when operating system fails complete read operation
WRITE_ERROR Raised when operating system fails complete write operation
INTERNAL_ERROR Raised due to unspecified PL/SQL error
CHARSETMISMATCH Raised due to mismatch in character sets of FOPEN and
PUTF/GET_LINE FILE_OPEN Raised when the file is already open
INVALID_MAXLINESIZE Raised when MAX_LINESIZE crosses its range i.e. 1..32767
INVALID_FILENAME Raised if invalid filename is attempted for open
ACCESS_DENIED Raised if the directory is not accessible to the user
INVALID_OFFSET Raised under below conditions
- ABSOLUTE_OFFSET = NULL and RELATIVE_OFFSET = NULL, or
- ABSOLUTE_OFFSET < 0, or
- Either offset caused a seek past the end of the file
DELETE_FAILED Raised when delete operation on the file fails
RENAME_FAILED Raised when rename operation on the file fails

Have a look at the sample EXCEPTION block which contains all the UTL_FILE exception handlers. It uses RAISE_APPLICATION_ERROR to customize the associated error message.

Sample Code

EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20001,'Invalid directory path or file name')
WHEN utl_file.invalid_mode THEN
raise_application_error(-20002,'Invalid mode for the file operation')
WHEN utl_file.invalid_filehandle THEN
raise_application_error(-20002,'Invalid file handler encountered')
WHEN utl_file.invalid_operation THEN
raise_application_error(-20003,'Invalid operation request on the file')
WHEN utl_file.read_error THEN
raise_application_error(-20004,'System error occurred during read operation')
WHEN utl_file.write_error THEN
raise_application_error(-20004,'System error occurred during write operation')
WHEN utl_file.internal_error THEN
raise_application_error(-20006,'System error occurred during UTL_FILE
operation')
END

Examples and Illustrations

I have a file TestFile.txt at a drive location. The DBA creates the directory UTLDIR to access the content of the file.

The PL/SQL block below reads and displays the contents of the file.

Sample Code

DECLARE
LH UTL_FILE.FILE_TYPE
L_TXT VARCHAR2(1000)
BEGIN
LH := UTL_FILE.FOPEN('UTLDIR','TESTFILE.TXT','R')
LOOP
UTL_FILE.GET_LINE(LH, L_TXT)
DBMS_OUTPUT.PUT_LINE(L_TXT)
END LOOP
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(LH)
END
/
Testing UTL_FILE read operation
Operation successful
PL/SQL procedure successfully completed.

Append Operation:

Now, if I shall demonstrate the addition/writing of content into the above file. Note that the file is not empty and already holds the data. Therefore, new data has to be appended to it.

Check the content of the TestFile.txt before Append operation.

The PL/SQL block below writes a line in the above view.

Sample Code

DECLARE
LH UTL_FILE.FILE_TYPE
L_TXT VARCHAR2(1000)
BEGIN
LH := UTL_FILE.FOPEN('UTLDIR','TESTFILE.TXT','A')
UTL_FILE.PUT_LINE(LH, 'Appended from PL/SQL')
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(LH)
END
PL/SQL procedure successfully completed.

The snapshot of the file content after the execution of above block is pasted below.

Write operation:

If UTL_FILE fails to find the file, which is specified under Append mode, it creates it in the OS at the same directory location. The case is demonstrated by the below PL/SQL block.

Sample Code