DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Why Should Databases Go Natural?
  • Using AUTHID Parameter in Oracle PL/SQL
  • SQL Interview Preparation Series: Mastering Questions and Answers Quickly
  • Keep Calm and Column Wise

Trending

  • Solid Testing Strategies for Salesforce Releases
  • Is Agile Right for Every Project? When To Use It and When To Avoid It
  • The 4 R’s of Pipeline Reliability: Designing Data Systems That Last
  • AI's Dilemma: When to Retrain and When to Unlearn?
  1. DZone
  2. Data Engineering
  3. Databases
  4. Nine Good-to-Knows about Oracle PL/SQL Error Management

Nine Good-to-Knows about Oracle PL/SQL Error Management

The following article highlights nine aspects of PL/SQL error management (you should be aware of before embarking on your next app dev effort. It also offers links to a number of Oracle LiveSQL scripts you can run to validate the good-to-knows.

By 
Steven Feuerstein user avatar
Steven Feuerstein
·
Apr. 11, 16 · Analysis
Likes (5)
Comment
Save
Tweet
Share
20.7K Views

Join the DZone community and get the full member experience.

Join For Free

Oracle PL/SQL is one of the most mature and robust database programming languages available today. While it is a proprietary language from Oracle Corporation, both IBM DB2 and PostgresSQL offer varying levels of support for compilation and execution of PL/SQL code within their databases.

The PL/SQL offers an especially powerful error management facility, fully documented here. The following article highlights nine aspects of PL/SQL error management you should be aware of before embarking on your next app dev effort.

I offer links to a number of Oracle LiveSQL scripts you can run to validate the good-to-knows. LiveSQL offers 24x7 access to a free Oracle Database 12c Release 1 instance, so you can play around with both SQL and PL/SQL.

1. Exceptions raised in the declaration section are not handled in the exception section.

This sometimes surprises a developer new to PL/SQL. The exception section of a PL/SQL block can only possibly handle an exception raised in the executable section. An exception raised in the declaration section (in an attempt to assign a default value to a variable or constant) always propagates out unhandled to the enclosing block.

Verify on LiveSQL

  • Exceptions Raised in Declaration Section Not Handled Locally

2. An exception raised does not automatically roll back uncommitted changes to tables.

Any non-query DML statements that complete successfully in your session are not rolled back when an exception occurs—either directly in PL/SQL or propagated out from the SQL engine. You still have the option of either committing or rolling back yourself.

If, however, the exception goes unhandled out to the host environment, a rollback almost always occurs (this is performed by the host environment).

Verify on LiveSQL

  • Exceptions Do Not Rollback Uncommitted Changes

3. You can (and should!) name those unnamed ORA errors (never hard-code an error number).

Oracle Database pre-defines a number of exceptions for common ORA errors, such as NO_DATA_FOUND and VALUE_ERROR. But there a whole lot more errors for which there is no pre-defined name. And some of these can be encountered quite often in code. The key thing for developers is to avoid hard-coding these error numbers in your code. Instead, use the EXCEPTION_INIT pragma to assign a name for that error code, and then handle it by name.

Verify on LiveSQL

  • Generate Named Exceptions

  • Use EXCEPTION_INIT to Give Names to Un-named Oracle Errors

4. If you do not re-raise an exception in your exception handler, the outer block doesn't know an error has occurred.

Just sayin'. You have a subprogram that invokes another subprogram (or nested block). That "inner" subprogram fails with an exception. It contains an exception handler. It logs the error, but then neglects to re-raise that exception (or another). Control passes out to the invoking subprogram, and it continues executing statements, completely unaware that an error occurred in that inner block. Which means, by the way, that a call to SQLCODE will return 0. This may be just what you want, but make sure you do this deliberately.

Verify on LiveSQL

  • If Exception Not Re-raised, No More Exception!

5. Whenever you log an error, capture the call stack, error code, error stack, and error backtrace.

Ideally, this is a total non-issue for you, because you simply invoke a generic logger procedure in your exception handlers (example and recommendation: download and use Logger, an open source utility that does almost anything and everything you can think of).

But, if you are about to write your own (or are using a home-grown logging utility), make sure that you cal and store in your log (likely a relational table), the values returned by:

  • SQLCODE
  • DBMS_UTILITY.FORMAT_CALL_STACK (or corresponding subprograms in 12.1's UTL_CALL_STACK package) - answers question "How did I get here?"
  • DBMS_UTILITY.FORMAT_ERROR_STACK (or corresponding subprograms in 12.1's UTL_CALL_STACK package) - answers question "What is my error message/stack?" We recommend using this instead of SQLERRM.
  • DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (or corresponding subprograms in 12.1's UTL_CALL_STACK package) - answers question "On what line was the error raised?"

Verify on LiveSQL

  • Basic Error Logging Package

  • "How did I get here?" DBMS_UTILITY.FORMAT_CALL_STACK

  • UTL_CALL_STACK: Fine-grained execution call stack package (12.1)

  • Error Message Functions: SQLERRM and DBMS_UTILITY.FORMAT_ERROR_STACK

  • Back Trace Exception to Line That Raised It

6. Always log your error (and backtrace) before re-raising the exception.

When you re-raise an exception, you will reset the backtrace (the track back to the line on which the error was raised) and might change the error code (if you raise a different exception to propagate the exception "upwards").

So, it is extremely important to call you error logging subprogram (see previous Good to Know) before you re-raise an exception.

Verify on LiveSQL

  • Back Trace Exception to Line That Raised It

7. Compile-time warnings will help you avoid "WHEN OTHERS THEN NULL".

One of Tom Kyte's favorite pet peeves, the following exception sections "swallow up" errors.

EXCEPTION 
   WHEN OTHERS 
   THEN 
      NULL;


EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);

In fact, any exception handler that does not re-raise the same exception or another, runs the risk of hiding errors from the calling subprogram, your users, and yourself as you debug your code.

Generally, you should log the error, then re-raise it. 

There are certainly some cases in which this advice does not hold (for example: a function that fetches a single row for a primary key. If there is no row for the key, it's not an application error, so just return NULL). In those cases, include a comment so that the person maintaining your code in the distant future knows that you weren't simply ignoring the Wisdom of the Kyte. Example:

EXCEPTION 

    WHEN OTHERS 

    THEN /* No company or this ID, let calling subprogram decide what to do */ RETURN NULL;

One way to avoid this problem is to turn on compile-time warnings. Then when your program unit is compiled, you will be warned if the compiler has identified an exception handler that does not contain a RAISE statement or a call to RAISE_APPLICATION_ERROR.

Verify on LiveSQL

  • Automatically Detect Exception Handlers That "Swallow Up" Errors

8. Use LOG ERRORS to suppress SQL errors at the row level.

The impact of a non-query DML statement is usually "all or nothing". If my update statement identifies 100 rows to change, then either all 100 rows are changed or none are. And, none might be the outcome if, say, an error occurs on just one of the rows (value too large to fit in column, NULL value for non-NULL column, etc.).

But, if you have a situation in which you would really like to "preserve" as many of those row-level changes as possible, you can add the LOG ERRORS clause to your DML statement. Then, if any row changes raise an error, that information is written to your error log table, and processing continues.

IMPORTANT: if you use LOG ERRORS, you must, must, must check that error log table immediately after the DML statement completes. You should also enhance the default error log table.

Verify on LiveSQL

  • Suppress DML Errors at Row Level with LOG ERRORS

  • Helper Package for LOG ERRORS

9. Send an application-specific error message to your users with RAISE_APPLICATION_ERROR.

If you execute a SELECT-INTO that does not identify any rows, the PL/SQL runtime engine raises: ORA-01403 and the error message (retrieved via SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK) is simply "No data found".

That may be exactly what you want your users to see. But there is a very good chance you'd like to offer something more informative, such as "An employee with that ID is not in the system."

In this case, you can use RAISE_APPLICATION_ERROR, as in:

CREATE OR REPLACE PACKAGE BODY employees_mgr
IS
   FUNCTION onerow (employee_id_in IN
hr.employees.employee_id%TYPE)
      RETURN hr.employees%ROWTYPE
      RESULT_CACHE
      IS
   l_employee hr.employees%ROWTYPE;
   BEGIN
      SELECT *
      INTO l_employee
      FROM hr.employees
      WHERE employee_id = employee_id_in;
      RETURN l_employee;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         raise_application_error (
         -20000,
          'An employee with that ID is not in the system.');
   END;
END;

Verify on LiveSQL

  • Send Application-specific Error Message To Users With RAISE_APPLICATION_ERROR

PL/SQL Database Relational database sql

Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Why Should Databases Go Natural?
  • Using AUTHID Parameter in Oracle PL/SQL
  • SQL Interview Preparation Series: Mastering Questions and Answers Quickly
  • Keep Calm and Column Wise

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: