Little known way to get the error message in PL/SQL

There are two built-in Oracle PL/SQL functions that return the error message associated with the error code of the most recently raised exception in your PL/SQL block. The first function is SQLERRM. The second is the less known function DBMS_UTILITY.FORMAT_ERROR_STACK.

Which one to use? To answer this question, let us first review the details of each function.

SQLERRM

  • Syntax: SQLERRM [(error_number)]
  • It returns the error message associated with its error_number argument:
    SQL> BEGIN
      2     DBMS_OUTPUT.put_line (SQLERRM (-1));
      3  END;
      4  /
    ORA-00001: unique constraint (.) violated
    
    PL/SQL procedure successfully completed.
  • If the argument is omitted (useful only in an exception handler), it returns the error message associated with the current value of SQLCODE (i.e. the error code of the most recently raised exception):
    SQL> BEGIN
      2     RAISE NO_DATA_FOUND;
      3  EXCEPTION
      4     WHEN OTHERS
      5     THEN
      6        DBMS_OUTPUT.put_line (SQLERRM);
      7  END;
      8  /
    ORA-01403: no data found
    
    PL/SQL procedure successfully completed.
  • Outside an exception handler, SQLERRM with no argument, or with argument equals to 0, always returns “ORA-0000: normal, successful completion”:
    SQL> BEGIN
      2        DBMS_OUTPUT.put_line (SQLERRM);
      3        DBMS_OUTPUT.put_line (SQLERRM(0));
      4  END;
      5  /
    ORA-0000: normal, successful completion
    ORA-0000: normal, successful completion
    
    PL/SQL procedure successfully completed.
  • SQLERRM with an argument of 1 (the user-defined exception error number) returns “User-Defined Exception”:
SQL> BEGIN
  2        DBMS_OUTPUT.put_line (SQLERRM(1));
  3  END;
  4  /
User-Defined Exception

PL/SQL procedure successfully completed.
  • SQLERRM with an invalid error code argument returns “ORA-NNNNN: Message NNNNN not found; product=RDBMS; facility=ORA” If the number is negative, and “-NNNNN: non-ORACLE exception” if the number is positive:
    SQL> BEGIN
      2     DBMS_OUTPUT.put_line (SQLERRM (7));
      3     DBMS_OUTPUT.put_line (SQLERRM (-9));
      4  END;
      5  /
    -7: non-ORACLE exception
    ORA-00009: Message 9 not found;  product=RDBMS; facility=ORA

    PL/SQL procedure successfully completed.
  • The maximum length string that SQLERRM will return is 512 characters.

DBMS_UTILITY.FORMAT_ERROR_STACK

  • Syntax: DBMS_UTILITY.FORMAT_ERROR_STACK RETURN VARCHAR2
  • In an exception handler, it returns the error message associated with the current value of SQLCODE (i.e. the error code of the most recently raised exception):
    SQL> BEGIN
      2     RAISE NO_DATA_FOUND;
      3  EXCEPTION
      4     WHEN OTHERS
      5     THEN
      6        DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK);
      7  END;
      8  /
    ORA-01403: no data found
    
    PL/SQL procedure successfully completed.
  • Outside an exception handler, it returns null:
    SQL> BEGIN
      2     DBMS_OUTPUT.put_line (
      3      COALESCE(DBMS_UTILITY.format_error_stack,'I am NULL'));
      4  END;
      5  /
    I am NULL
    
    PL/SQL procedure successfully completed.
  • The maximum length string that DBMS_UTILITY.FORMAT_ERROR_STACK will return is 2000 characters.

Which one to use?

Because of the size restriction (512 versus 2000 characters), it is recommended that you call DBMS_UTILITY.FORMAT_ERROR_STACK instead of SQLERRM to ensure that you see the full error message string.

However, you can use SQLERRM to check whether a particular number is a valid Oracle error or to return the error message corresponding to a particular error code.

Resources

Possibly related:

Tagged , | Post a Comment