VF=>user  ID=>    Login Feedback FAQ Blog
1,050,815 quizzes played | 1,461 active players

Actions

Important News!

We've built a new "skin" for our quiz platform: the Oracle Dev Gym. We'd love to hear what you think of it. Click on the Dev Gym image on the right of your home page to check it out.



  On Handling Exceptions: Error Message Functions (Oracle PL/SQL)      Add to Favorites

Summary

As of 12.1, you can obtain error message information from SQLERRM, DBMS_UTILITY.FORMAT_ERROR_STACK and the UTL_CALL_STACK package.

Details

Assumes Oracle Database 12c Release 1 or higher
Intermediate Quiz, IDs: 18681/1799598/1171240

Reviewer(s): Vitaliy Lyanchevskiy

The Question

Which choices can be used in place in ##REPLACE## in the procedure below:

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 (##REPLACE##);
END;
/

so that after this block is executed:

BEGIN
   plch_check_balance (-1);
END;
/

you will see this text on the screen:

ORA-06502: PL/SQL: numeric or value error

The Choices [↑]

Explanation of Result Icons
Choice 1 (33516)
DBMS_UTILITY.format_call_stack

This choice returns the execution call stack, which has nothing to do with error messages or stacks.




91%
Choice 2 (33517)
DBMS_UTILITY.format_error_stack

The FORMAT_ERROR_STACK function in DBMS_UTILITY returns the full error stack, which consists of one of more error messages.




61%
Choice 3 (33518)
DBMS_UTILITY.format_error_backtrace

The FORMAT_ERROR_BACKTRACE function in DBMS_UTILITY returns a string that allows you to "trace back" to the line number on which the error was raised. But it does not contain error message information.




67%
Choice 4 (33519)
SQLERRM

The traditional function for getting the error message. And so, yes, this choice is correct. However, Oracle recommends that you use DBMS_UTILITY.FORMAT_ERROR_STACK instead, because SQLERRM can truncate long error stacks.




75%
Choice 5 (33520)
UTL_CALL_STACK.error_msg (1)

The UTL_CALL_STACK package was added in 12.1 and provides an extensive API to access parts of all of the execution call stack, error stack and backtrace.

Having said that, this choice is wrong because the ERROR_MSG function returns, literally, only the message text itself, in this case for example:

PL/SQL: numeric or value error

and does not include the error number.




78%
Choice 6 (33521)
   'ORA-'
|| TO_CHAR (UTL_CALL_STACK.error_number (1), 'fm00000')
|| ': '
|| UTL_CALL_STACK.error_msg (1)

I now call two functions in the UTL_CALL_STACK API to construct the typical and expected error message, including "ORA", the number, and the message.




68%

Answer [↑]

You will encounter several kinds of errors and warnings when working with PL/SQL program units:

1. Compilation errors: your programs will not compile. The errors reported by the PL/SQL compiler are of the form "PLS-NNNN: message".

2. Compile-time warnings: While compiling stored PL/SQL units, the PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation—for example, using a deprecated PL/SQL feature. Warnings take the form "PLW-NNNN: message".

3. Run-time errors: you (or your users) run your program and an error (known as an exception) is raised. Run-time errors generally take the form "ORA-NNNN: message".

You can write an exception handler in a PL/SQL block to trap and handle run-time errors.


The PL/SQL run-time engine will raise exceptions whenever the Oracle database detects a problem or it executes a RAISE or RAISE_APPLICATION_ERROR statement in your code. You can then trap or handle these exceptions in the exception section - or let the exception propagate unhandled to the enclosing block or host environment.


Oracle PL/SQL offers three (as of 12.1) built-in functions to retrieve the error message associated with the currently-raised exception: SQLERRM, DBMS_UTILITY.FORMAT_ERROR_STACK and UTL_CALL_STACK.ERROR_MSG.

If passed no argument (error code), SQLERRM returns the message associated with the currently-raised exception (it can also return a stack of such messages).

DBMS_UTILITY.FORMAT_ERROR_STACK returns the same thing, but supports longer strings; SQLERRM will truncate at around 500 characters (which can happen when you have a stack of errors).

Added in 12.1, UTL_CALL_STACK.ERROR_MSG is part of an clean, simple API to both call stack, error stack and backtrace information.


Statistics and Rankings [↑]

Rank Percentile Score Seconds% Correct
Median
74
50
328
262
83
Best
1
100
485
45
100

Click to View Verification Code [↑]

Quiz Link and Tweet [↑]

Pass along a link the quiz or use our text to tweet about this quiz. Thanks for helping to spread the word!

 
About Oracle | Terms of Use | Your Privacy Rights | Copyright 2010-2017, Oracle Corporation