web analytics
Oracle PL/SQL Exceptions Handling Options
codeling
Posted: Wednesday, October 12, 2016 10:29:43 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 916
Points: 3849

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:

  1. To create a unique id for an user-defined exception.
  2. 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;
Sponsor
Posted: Wednesday, October 12, 2016 10:29:43 AM
codeling
Posted: Thursday, October 19, 2017 11:18:54 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 916
Points: 3849

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.

codeling
Posted: Thursday, October 19, 2017 2:35:18 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 916
Points: 3849

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;
/

 

Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2017 Digcode.com. All rights reserved.