CODEWITHSUNDEEP
oracle-database
RustamIS
RustamIS

Reputation: 697

Where oracle Stores user defined exceptions?

Is there oracle table that stores user defined exceptions?

Upvotes: 3

Views: 1858

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

No.

User-defined exceptions are, like other variables, defined in PL/SQL blocks and have whatever scope the PL/SQL variable would have. So, for example

DECLARE
  my_exception EXCEPTION;
BEGIN
  RAISE my_exception;
EXCEPTION 
  WHEN my_exception
  THEN
    dbms_output.put_line( 'Caught my_exception' );
END;

will create the user-defined exception my_exception but the exception will only exist for the scope of the anonymous PL/SQL block. You can define exceptions in packages so that they can be referenced by multiple PL/SQL blocks. And you can use the exception_init pragma to associate user-defined exceptions with particular error codes

SQL> ed
Wrote file afiedt.buf

  1      DECLARE
  2        my_exception EXCEPTION;
  3        pragma exception_init( my_exception, -20001 );
  4      BEGIN
  5        RAISE my_exception;
  6*     END;
SQL> /
    DECLARE
*
ERROR at line 1:
ORA-20001:
ORA-06512: at line 5

Or you can use the raise_application_error function either alone or in concert with user-defined exception variables

SQL> ed
Wrote file afiedt.buf

  1      DECLARE
  2        my_exception EXCEPTION;
  3        pragma exception_init( my_exception, -20001 );
  4      BEGIN
  5        RAISE_APPLICATION_ERROR( -20001, 'This is my error text' );
  6      EXCEPTION
  7        WHEN my_exception
  8        THEN
  9          dbms_output.put_line( 'Caught my_exception' );
 10          dbms_output.put_line( sqlerrm );
 11*     END;
SQL> /
Caught my_exception
ORA-20001: This is my error text

PL/SQL procedure successfully completed.

There is no data dictionary table to store user-defined exceptions because there are so many (potentially conflicting) ways that your code might define those exceptions. From a good exception management standpoint, you would generally want to come up with a consistent way of defining your application-specific exceptions and sticking to that approach. Personally, I like a package that defines all my exceptions and associates them with error codes

SQL> ed
Wrote file afiedt.buf

  1  create or replace package error_pkg
  2  as
  3    invalid_name exception;
  4    pragma exception_init( invalid_name, -20001 );
  5    invalid_address exception;
  6    pragma exception_init( invalid_address, -20002 );
  7* end;
SQL> /

Package created.

Assuming all my user-defined exceptions are all defined like this, I can go to the package definition for all my definitions.

Upvotes: 4

Related Questions