Welcome, Guest

Oracle PL/SQL Exceptions Handling

Options

Previous Topic

Next Topic

@2016-10-12 10:29:43

RAISE_APPLICATION_ERROR ( )

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.

Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).

RAISE_APPLICATION_ERROR raises an exception but does not handle it. RAISE_APPLICATION_ERROR is used for the following reasons:

To create a unique id for an user-defined exception.
To make the user-defined exception look like an Oracle error.

The General Syntax to use this procedure is:

RAISE_APPLICATION_ERROR (error_number, error_message);

The error number must be between -20000 and -20999

The error_message is the message you want to display when the error occurs.

DECLARE
   num_tables NUMBER;
BEGIN
   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;
   IF num_tables < 1000 THEN
      raise_application_error(-20101, 'Expecting at least 1000 tables');
   ELSE
      NULL; -- Do the rest of the processing (for the non-error case).
   END IF;
END;

@2017-10-19 11:18:54

In order to show the line number where the exception is thrown, you can do it by using

DBMS_OUTPUT.put_line ( 'Error raised: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' - '||sqlerrm);

Example

declare

clientrow prod.client%rowtype;

begin

dbms_output.enable(null);

select *

into clientrow

from prod.client;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line ( 'Error raised: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' - '||sqlerrm);

end;

Since Oracle Database 10g, Oracle has added DBMS_UTILITY.FORMAT_ERROR_BACKTRACE , which can and should be called in your exception handler. It displays the call stack at the point where an exception was raised, even if the function is called in a PL/SQL block in an outer scope from that where the exception was raised. Thus, you can call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE within an exception section at the top level of your stack and still find out where the error was raised deep within the call stack.

The following error mesasge is shown when there are multiple rows in the table client.

Error raised: ORA-06512: at line 7 - ORA-01422: exact fetch returns more than requested number of rows

Oracle ORA-06512 is the catch-all Oracle error for PL/SQL exceptions.

Oracle ORA-06512 will generally appear as part of a message stack in which the message directly preceding Oracle ORA-06512 will list the reason for the error.

In above example, this user finds himself being thrown Oracle ORA-06512 and ORA-01422 while attempting to retrieving one row into clientrow varaible.

@2017-10-19 14:35:18

In a real-world application, the error backtrace could be very long. Generally, debuggers and support people don't really want to have to deal with the entire stack; they are mostly going to be interested in that top-most entry. The developer of the application might even like to display that critical information to the users so that they can immediately and accurately report the problem to the support staff. the following utility package can parse the backtrace string and retrieve just the top-most entry.

CREATE OR REPLACE PACKAGE bt IS /* | Overview: bt provides structured access to the information | returned by DBMS_UTILITY.format_error_backtrace. | | Author: Steven Feuerstein | Repository: www.qnxo.com | Contact: steven@stevenfeuerstein.com | Copyright 2004 Steven Feuerstein, all rights reserved | | You are welcome to copy and use this program, but please | include attribution of the source of the code. */ TYPE error_rt IS RECORD ( program_owner all_objects.owner%TYPE , program_name all_objects.object_name%TYPE , line_number PLS_INTEGER ); -- -- Parse a line with this format: -- ORA-NNNNN: at "SCHEMA.PROGRAM", line NNN -- FUNCTION info (backtrace_in IN VARCHAR2) RETURN error_rt; PROCEDURE show_info (backtrace_in IN VARCHAR2); END bt; / CREATE OR REPLACE PACKAGE BODY bt IS -- Strings that delimit different parts of line in stack. c_name_delim CONSTANT CHAR (1) := '"'; c_dot_delim CONSTANT CHAR (1) := '.'; c_line_delim CONSTANT CHAR (4) := 'line'; c_eol_delim CONSTANT CHAR (1) := CHR (10); -- FUNCTION info (backtrace_in IN VARCHAR2) RETURN error_rt IS -- Lots of INSTRs to come; these variables keep track -- of the start and end points of various portions of the string. l_at_loc PLS_INTEGER; l_dot_loc PLS_INTEGER; l_name_start_loc PLS_INTEGER; l_name_end_loc PLS_INTEGER; l_line_loc PLS_INTEGER; l_eol_loc PLS_INTEGER; -- retval error_rt; PROCEDURE initialize_values IS BEGIN l_name_start_loc := INSTR (backtrace_in, c_name_delim, 1, 1); l_dot_loc := INSTR (backtrace_in, c_dot_delim); l_name_end_loc := INSTR (backtrace_in, c_name_delim, 1, 2); l_line_loc := INSTR (backtrace_in, c_line_delim); l_eol_loc := INSTR (backtrace_in, c_eol_delim); IF l_eol_loc = 0 THEN l_eol_loc := LENGTH (backtrace_in) + 1; END IF; END initialize_values; BEGIN initialize_values; -- retval.program_owner := SUBSTR (backtrace_in , l_name_start_loc + 1 , l_dot_loc - l_name_start_loc - 1 ); -- retval.program_name := SUBSTR (backtrace_in, l_dot_loc + 1, l_name_end_loc - l_dot_loc - 1); -- retval.line_number := SUBSTR (backtrace_in, l_line_loc + 5, l_eol_loc - l_line_loc - 5); RETURN retval; END info; PROCEDURE show_info (backtrace_in IN VARCHAR2) IS l_line error_rt; BEGIN l_line := info (backtrace_in); DBMS_OUTPUT.put_line ('Program owner = ' || l_line.program_owner); DBMS_OUTPUT.put_line ('Program name = ' || l_line.program_name); DBMS_OUTPUT.put_line ('Line number = ' || l_line.line_number); END show_info; END bt; /

Previous Topic

Next Topic

1 2

Comments

You must Sign In to comment on this topic.

Print this topic

Watch this topic

Normal

Threaded