PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks.

The three ways are:
  1. SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely to run into: the error stack will be truncated at 512 bytes, and you might lose some error information.
  2. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will.
  3. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace. 
Note: check out this LiveSQL script if you'd like to explore more of the nuances between SQLERRM and DBMS_UTILITY.FORMAT_ERROR_STACK.

Other helpful resources regarding error management in PL/SQL:

Nine Good to Knows for PL/SQL Error Management
PL/SQL Error Handling (doc)

Finally, here's the code from our quiz that you can copy/paste into your editor to check out these alternatives.
CREATE OR REPLACE PROCEDURE plch_check_balance (
   balance_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF balance_in < 0
   THEN
      RAISE VALUE_ERROR;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

BEGIN
   plch_check_balance (-1);
END;
/

CREATE OR REPLACE PROCEDURE plch_check_balance (
   balance_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF balance_in < 0
   THEN
      RAISE VALUE_ERROR;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;
/

BEGIN
   plch_check_balance (-1);
END;
/

CREATE OR REPLACE PROCEDURE plch_check_balance (
   balance_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF balance_in < 0
   THEN
      RAISE VALUE_ERROR;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'ORA-'
         || TO_CHAR (UTL_CALL_STACK.error_number (1), 'fm00000')
         || ': '
         || UTL_CALL_STACK.error_msg (1));
END;
/

BEGIN
   plch_check_balance (-1);
END;
/

DROP PROCEDURE plch_check_balance
/

Comments

  1. Hi Steven,
    Love the article (as usual). But wasn't UTL_CALL_STACK introduced in 12c instead of 11g?

    ReplyDelete
    Replies
    1. Ha! Yes, Erwin, you are absolutely correct. Will fix the post.

      Delete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts