Skip to Main Content
  • Questions
  • RE: [Fwd: Ask Tom Submission] Pragma Exception_Init question


Question and Answer

Tom Kyte

Thanks for the question, Rajendran .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: April 16, 2010 - 10:50 am UTC


Viewed 1000+ times

You Asked

I was looking at user defined exceptions and assigning error
code/numbers to exception using PRAGMA EXCEPTION_INIT. for example,

EXCEPTION_INIT (invalidSKU, -20001);

Now is there a way to associate an error message with an user defined
error code, in addition to the defining exception ? The idea being that
I should be able to capture the error message using SQLERRM. Also I will
be able define various user exceptions,
an valid error code and an a generic
error message, all at one place maybe one package.


and Tom said...

You are sort of mixing 2 concepts up here.

One is the ability to define a user defined exception (invalidSKU above) and the other is to take an existing Oracle error code (eg: -20001 in your example) and map that to some user defined exception.

We'll be able to achieve your goal (define exceptions and have known error messages/codes associated with them) easily though. You can do this in one package as well.

First, lets look at what you did above. Here is a full example:

SQL> declare
3 pragma EXCEPTION_INIT (invalidSKU, -20001);
5 raise_application_error( -20001, 'Some Error Message' );
6 exception
7 when invalidSKU then
8 dbms_output.put_line( 'We caught the invalid SKU exception' );
9 dbms_output.put_line( 'sqlerrm = ' || sqlerrm );
10 dbms_output.put_line( 'sqlcode = ' || sqlcode );
11 end;
12 /
We caught the invalid SKU exception
sqlerrm = ORA-20001: Some Error Message
sqlcode = -20001

PL/SQL procedure successfully completed.

You made it so that when the SQL error "-20001" is raised, PL/SQL will convert that into the named exception invalidSKU. The error message that accompanied the -20001 (generated by the raise_application_error) is there in the SQLERRM builtin function. While we can make it so that when invalidSKU is raised -- the sqlcode is set to -20001 -- we have no control over the associated error text (it'll be NULL).

What pragma exception_init is designed for is to convert an Oracle Error code into a named exception to make programing easier. Instead of having a big 'when others' like:

when others then
if ( sqlcode = some_value ) then
elsif ( sqlcode = some_other_value ) then
elsif ....
end if;

the pragma allows you to have:

when Some_Error then
when Some_Other_Error then

Now, how can you define a package that has a bunch of user defined exceptions with corresponding error codes and messages and how would another programmer 'raise' such an exception? It might look like this:

SQL> create or replace package exception_pkg
2 as
3 invalid_sku constant number default -20001;
4 invalid_sku_exception exception;
5 pragma exception_init( invalid_sku_exception, -20001 );
7 another_error constant number default -20002;
8 another_error_exception exception;
9 pragma exception_init( another_error_exception, -20002 );
11 procedure throw( p_exception in number, p_extra_msg in varchar2 default NULL );
13 end;
14 /

Package created.

This is my package spec for all of my exceptions. Here I will assign a numeric code in the range of -20000 to -20999 (these are the valid sqlcodes you may control, all others belong to Oracle itself) to each named exception. I also define an EXCEPTION for each code and then use pragma exception_init to map the named exception to the error code. In the above example, I have defined 2 exceptions. I have directed PL/SQL to map the error code -20001 to the named exception "invalid_sku_exception".

This spec also defines a "throw" procedure. This is what the developers will call to cause an exception to be raised. This procedure will take one of the constants as input and will call the internal routine "raise_application_error" to raise an exception with a specific error code and message. Inside the package body, we will see how we get error messages (text) mapped to specific error codes. Additionally, we will see that the parameter p_extra_msg that can be supplied to the throw procedure will be appended to the error message as well.

Now, the body of exception_pkg. It might look like this:

SQL> create or replace package body exception_pkg
2 as
4 -- We will create an array of error messages. Since the PLSQL table
5 -- type is a sparse array, we can put into it indices like -20001,
6 -- -20002 and so on and leave the rest of the table blank. The
7 -- 'elaboration' code at the bottom of this package will be executed
8 -- once per session when the package is first referenced. This elaboration
9 -- code will initialize the plsql table of error messages for us
11 type myarray is table of varchar2(255) index by binary_integer;
12 err_msgs myArray;
15 -- throw is what is used to raise user defined exceptions, those
16 -- defined in the exception_pkg specification.
17 --
18 -- p_exception should be one of the constants found in the specification
19 -- p_extra_msg is optional. if supplied, it will be appended to the error message
20 --
21 -- this procedure uses another routine, who_called_me (supplied separately).
22 -- who_called_me gets the callers name and line number. this information
23 -- is put into the error message as well so we can figure out who threw
24 -- the exception in the first place easily from the error message.
26 procedure throw( p_exception in number, p_extra_msg in varchar2 default NULL )
27 is
28 l_owner varchar2(30);
29 l_name varchar2(30);
30 l_type varchar2(30);
31 l_line number;
32 l_exception number;
33 begin
34 who_called_me( l_owner, l_name, l_line, l_type );
35 raise_application_error( p_exception ,
36 'Exception at ' || l_type || ' ' ||
37 l_owner || '.' || l_name || '(' || l_line || ') ' ||
38 err_msgs(p_exception ) || ' ' || p_extra_msg );
39 exception
40 -- we will get this when we have an invalid exception code, one
41 -- that was not set in the err_msgs array below. The plsql table
42 -- access will raise the NO-DATA-FOUND exception. We'll catch it,
43 -- verify the exception code is in the valid range for raise_application_error
44 -- (if not, set to -20000) and then raise the exception with the message
45 -- "unknown error"
47 when NO_DATA_FOUND then
48 if ( p_exception between -20000 and -20999 ) then
49 l_exception := p_exception;
50 else
51 l_exception := -20000;
52 end if;
54 raise_application_error( l_exception ,
55 'Exception at ' || l_type || ' ' ||
56 l_owner || '.' || l_name || '(' || l_line || ') ' ||
57 '**UNKNOWN ERROR**' || ' ' || p_extra_msg );
59 end;
61 begin
62 -- Our elaboration code. For each constant in the package spec we should
63 -- have an entry in this plsql table.
64 -- this code is run once per session when the package is first 'touched'
66 err_msgs( invalid_sku ) := 'Invalid SKU Code';
67 err_msgs( another_error ) := 'Another Error Message...';
68 end;
69 /

Package body created.

The procedure who_called_me is attached (as well as the source code for this package). You could just embed who_called_me right into this package body but since I use it in so many places, I prefer to just let it standalone.

Now, a developer will throw and catch exceptions like this:

SQL> create or replace procedure demo1
2 as
3 begin
4 exception_pkg.throw( exception_pkg.invalid_sku, '<extra text from runtime>' );
5 exception
6 when exception_pkg.invalid_sku_exception then
7 dbms_output.put_line( 'Caught the error' );
8 dbms_output.put_line( 'sqlerrm = ' || sqlerrm );
9 dbms_output.put_line( 'sqlcode = ' || sqlcode );
10 end;
11 /

Procedure created.

They will use the exception_pkg.throw procedure to raise an exception (instead of using RAISE or raise_application_error directly). They can also pass in extra text, for example with the invalid SKU error -- they might pass in the SKU that was in fact invalid for the end user to see.

This routine also shows how they would catch these errors. They would use the named exceptions in the exception_pkg as I have above. Specifically:

6 when exception_pkg.invalid_sku_exception then

is how they would refer to the exception. When we run this procedure in SQLPLUS we can see that we achieved the desired results:

SQL> exec demo1
Caught the error
sqlerrm = ORA-20001: Exception at PROCEDURE TKYTE.DEMO1(4) Invalid SKU Code <extra text from runtime>
sqlcode = -20001

PL/SQL procedure successfully completed.


o Caught the error with line 6
o See the error message "Invalid SKU Code". It has some runtime info appended and at the front it has the actual name and line number of the procedure that threw the exception.
o it has the error code we defined for this error....

The following demonstrates what happens when this package is used with an undefined, out of range error code:

SQL> create or replace procedure demo2
2 as
3 begin
4 exception_pkg.throw( 123, '<This is an unknown error>' );
5 exception
6 when Others then
7 dbms_output.put_line( 'Caught the error in OTHERS since this error was not mapped' );
8 dbms_output.put_line( 'sqlerrm = ' || sqlerrm );
9 dbms_output.put_line( 'sqlcode = ' || sqlcode );
10 end;
11 /

Procedure created.

SQL> exec demo2
Caught the error in OTHERS since this error was not mapped
sqlerrm = ORA-20000: Exception at PROCEDURE TKYTE.DEMO2(4) **UNKNOWN ERROR** <This is an unknown error>
sqlcode = -20000

PL/SQL procedure successfully completed.

Since "123" is not a valid error code -- this raises the OTHERS exception (cannot be mapped to a named exception) and the error message reflects this


  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Good Stuff

roland Daane, February 14, 2002 - 5:42 am UTC

This is really interesting stuff.

I'm using this method of work by inderting the data for the exceptions into an Oracle table and from that creating the error package.

I'm really interested in the source for the who_called_me procedure!

Tom Kyte
February 22, 2002 - 9:00 am UTC

A reader, August 20, 2003 - 11:28 am UTC

Excellent Tom

Sami, January 26, 2004 - 11:23 am UTC

Who am i -- Packages

Marcio, January 31, 2004 - 10:01 am UTC

Tom, I still don't get how to work with packages and who_called_me, look this:

ops$t_mp00@MRP817> create or replace package pkg
2 is
3 procedure p;
4 procedure p1;
5 end;
6 /

Package created.

ops$t_mp00@MRP817> create or replace package body pkg
2 is
3 procedure p
4 as
5 begin
6 p1;
7 end;
8 procedure p1
9 as
10 l_owner varchar2(30);
11 l_name varchar2(30);
12 l_lineno number;
13 l_type varchar2(30);
14 begin
15 who_called_me( l_owner, l_name, l_lineno, l_type );
16 dbms_output.put_line( l_owner || '.' || l_name);
17 end;
18 end;
19 /

Package body created.

ops$t_mp00@MRP817> exec pkg.p

How to log it into log_error, just package name? I mean the real name of procedure inside package can I see? Or any other suggestion for this kind of instrumentation am trying implement on?

Tom Kyte
January 31, 2004 - 10:13 am UTC

you get the schema object name (the package name) and the LINENUMBER.

there could be 500 procedure "p1's" in that package -- the package name and line number are what you get here. and that is all you can get. we do not track things down to the procedure/function name inside of a package.

another light+

Marcio, February 05, 2004 - 9:47 pm UTC

Suppose you have an entire batch system, setting up by parameters -- this system catch information from tables over dblink and populates some another tables and them write flat files as long as day range of parameter.

1) What would be a effective handling exception on this context?

2) Is it a good idea have a trigger before insert on log_error table to send up e-mails with body error caught to warning operators people that something is going wrong?

3) Is the exception_pkg code public?

thank you,

Tom Kyte
February 06, 2004 - 8:37 am UTC

1) don't know, insufficient data, the programmers should "naturally know" as writing PLSQL code is no different then writing C, Java, VB -- just easier :)

This is a programming decision. I can only tell you the wrong ways to avoid -- such as "using when others" and returning error codes instead of propagating the exceptions out.

2) see #1. I would find it rather annoying myself but it is up to you.

3) everything on this site is for you to cut and paste, copy and use.

about question #2

Marcio, February 07, 2004 - 10:43 am UTC

2) But, how will the operator know if something was going wrong? I'm thinking mail him just where system goes down, not for any exception.

Tom Kyte
February 07, 2004 - 3:11 pm UTC

there are excellent system management tools on the market from hundreds of vendors (including Oracle with OEM) that will do that.

This was excellent!

Mike McAllister, January 06, 2006 - 5:39 pm UTC

This was an absolutely excellent and useful exception handling architecture. With a few changes I implemented it myself in the application I'm working on now. As another responder mentioned, I also modified the "throw" routine to also log the error to a table in the database using an autonomous transaction. I also modified the raise_application_error to set the third parameter to TRUE so that the application error is put on top of the error stack, rather than replacing it.

Exception INIT, Is it really required ?

Ravi Kumar, January 10, 2006 - 12:11 am UTC

Hi Tom..

I was looking for something in this website that "why do we need EXCEPTION INIT, While We have SQLCODE and we can use it in WHEN OTHERS".

And I found this and here you said ...

"What pragma exception_init is designed for is to convert an Oracle Error code into a named exception to make programing easier. Instead of having a big 'when
others' like:

when others then
if ( sqlcode = some_value ) then
elsif ( sqlcode = some_other_value ) then
elsif ....
end if; "

But I think, If we use Pragma , We have to declare each exception , then associate each of it with oracle error code(using Pragma), and then handle each of it in exception section. I think in this way we are actualy making it bigger/difficult instead of shorter/easier.

Can I have your comments on this, Please ?

Thanks & Regards
Ravi Kumar

Tom Kyte
January 10, 2006 - 8:03 am UTC

how many exceptions do you plan on handling there? Seems to me, it would be "a handful"

Which is more readable to you:

when no_data_found then ....
when too_many_rows then ....
when resource_busy then ...
when deadlock_detected then ....


when others
if (sqlcode = -1403) then ...
elsif (sqlcode = -1422) then ...
elsif (sqlcode = -54) then ....
elsif (sqlcode = -60) then ...
else RAISE;
end if;

I don't know about you, but I'd rather read the first - it is more efficient, it is definitely more readable, it is less error prone (tell me how many people will over look the need for the RAISE at the end?)

Given that most exceptions you want to trap are defined for you (no_data_found for example), and that you would rarely be having to define your own.....

I think that by using exceptions you are actually making it

o shorter
o easier
o more maintainable
o more self documenting
o clearer

Catch unique index error

Totu, April 20, 2006 - 2:43 am UTC

Dear Tom.
I can catch unique value error using DUP_VAL_ON_INDEX.

But if there are not one, some unique indexes, how can I catch which index and columns are the source of error?

Thanks in advance

Tom Kyte
April 20, 2006 - 8:03 am UTC

sqlerrm has the constraint name in it.

bug in code example

Dannie Kjeldgaard, March 15, 2007 - 9:39 am UTC

Notice that line 48 should read
if ( p_exception between -20999 and -20000 ) then

Best regards

Minor change to who_called_me

Greg Anderson, March 20, 2007 - 12:12 pm UTC

Hi Tom,

many thanks for your excellent website.

My client is using Oracle9i This appears to have a slightliy different call stack from that used in the original code.

This means the who_called_me procedure ought now to include:

-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller
if ( cnt = 3 ) then
    lineno := to_number(substr( line, 21, 6 ));  
    line   := substr( line, 29 );               

Of course, this may require further modification for 10g.



I need help on control file structure in sql loader

anitha, April 16, 2010 - 4:53 am UTC

I wanted to reject row with all null fields while loading. i need option for the same in the control file structure.

Please give me the sample structure
Tom Kyte
April 16, 2010 - 10:50 am UTC

that option is:

<this space left intentionally blank>

it just happens already.


Matt, April 17, 2010 - 6:49 am UTC

Happy 10 Years Tom!!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library