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.

The output from executing this script is as follows:

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.

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.

The drawback to this method is that you have to know what the EXCEPTION_INIT v...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database administrator Understanding SQL string functions What is the difference between a database engineer, architect and administrator? Import on one table from dump file Error during RMAN backup Can I drop a column in SYS schema? STATSPACK tool: transaction vs. execution measurement Should I port from Microsoft Access? How can I find statistics on total memory usage and database connections? Installing multiple Oracle homes Modifying SYS password in a RAC environment

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

alues 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.