Exception handling

This script demonstrates how to use nested blocks and exception handlers to control the flow of execution when exceptions are raised. I've included the script and the output from executing the same.

test_get_file.sql
-----------------------------------------------------------------
set serveroutput on format word_wrapped

variable return_code number
variable return_msg  varchar2(100);

declare
myFileHandle   UTL_FILE.FILE_TYPE;
myInputBuffer  varchar2(1023);
myEOF          char(1);

begin
:return_code := 0;
:return_msg  := 'SUCCESS';

dbms_output.enable(50000);

myFileHandle :=
UTL_FILE.FOPEN('/dataxfer/trace/data_in','testfile.txt','r');

myEOF := 'N';
while myEOF = 'N' loop

begin
 UTL_FILE.GET_LINE(myFileHandle, myInputBuffer);
 if myInputBuffer is null then
  myInputBuffer := 'Read in a blank line.';
 end if;
 dbms_output.put_line(substr(myInputBuffer,1,79));
exception
when UTL_FILE.READ_ERROR then
 dbms_output.put_line('Error reading record. Probably too long a record.');
when VALUE_ERROR then
 dbms_output.put_line('Read a line that was too long.');
when NO_DATA_FOUND then
 myEOF := 'Y';
 dbms_output.put_line('Reached end-of-file.');
end;

end loop;

UTL_FILE.FCLOSE(myFileHandle);

exception
when UTL_FILE.INVALID_PATH then
 :return_code := sqlcode;
 :return_msg  := substr(sqlerrm,1,100);
 dbms_output.put_line('Invalid path name.');
when UTL_FILE.INVALID_MODE then
 :return_code := sqlcode;
 :return_msg  := substr(sqlerrm,1,100);
 dbms_output.put_line('Opened file with invalid mode.');
when UTL_FILE.INVALID_OPERATION then
 :return_code := sqlcode;
 :return_msg  := substr(sqlerrm,1,100);
 dbms_output.put_line('Performed an invalid operation.');
when UTL_FILE.INVALID_FILEHANDLE then
 :return_code := sqlcode;
 :return_msg  := substr(sqlerrm,1,100);
 dbms_output.put_line('Specified an invalid file handle.');
when UTL_FILE.READ_ERROR then
 :return_code := sqlcode;
 :return_msg  := substr(sqlerrm,1,100);
 dbms_output.put_line('Error reading record.');
when OTHERS then
 :return_code := sqlcode;
 :return_msg  := substr(sqlerrm,1,100);
 dbms_output.put_line(to_char(:return_code)||': '||:return_msg);
end;
.
/
print return_code
print return_msg

exit

The output from executing this script is as follows:

-------------------------------------------------------------
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Mar 18 05:56:38 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production

This is line one of the file.
This is line two.
Read in a blank line.
This is line four of the file.
Error reading record. Probably too long a record.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line six of the file.
This is line seven.
Read in a blank line.
Read in a blank line.
Reached end-of-file.

PL/SQL procedure successfully completed.


RETURN_CODE
-----------
          0


RETURN_MSG
---------------------------------------------------------------------
SUCCESS

Disconnected from 
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production

Reader Feedback

After having done PL/SQL programming for the better part of the last five years, Jeffrey Toth's leaves me non-plussed. My experience with exception handling has lead to avoid the use of EXCEPTION variables/constants.

The inherent problem of trapping EXCEPTION variables/constant is that you have to repeat any kind that is generic to all exceptions. Since we typically want to clean up behind ourselve when in the EXCEPTION block, we could wind up with a lot of repeated code. I'm always careful that my EXCEPTION block closes cursors left open, release handles and so on.

In Jeffrey's example, you would want to do the UTL_FILE.FCLOSE(myFileHandle) if necessary. But the way his exception block is coded, you'd have to repeat the following IF statement for every WHEN e_varname.

IF (UTL_FILE.ISOPEN(myFileHandle)) THEN
  UTL_FILE.FCLOSE(myFileHandle);
END IF;

My solution is to use strictly WHEN OTHERS with SQLCODE value testing for exception-specific handling. If I rewrote Jeffrey's last EXCEPTION block, it would look like this.

EXCEPTION
  WHEN OTHERS THEN
    :return_code := SQLCODE;
    :return_msg  := SUBSTR(SQLRERRM,1,100);
 
    IF (UTL_FILE.ISOPEN(myFileHandle))THEN
      UTL_FILE.FCLOSE(myFileHandle);
    END IF;

    /* Use the SQLCODE values associated to
       the EXCEPTION constants with the
       PRAGMA EXCEPTION_INIT statement */
    IF (SQLCODE = -...) THEN
      DBMS_OUTPUT.PUT_LINE(...);
    ELSIF (SQLCODE = -...) THEN
      DBMS_OUTPUT.PUT_LINE(...);
    ...
    END IF;
END;

The drawback to this method is that you have to know what the EXCEPTION_INIT values that are associated to an exception (for example, the common NO_DATA_FOUND is +100). But what you loose in documentation quality, you gain in code normalization. You can make great savings in repeated code, which any programmer that has to do maintenance on the code will appreciate!

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.