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
2 invalidSKU EXCEPTION;
3 pragma EXCEPTION_INIT (invalidSKU, -20001);
4 BEGIN
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 );
6
7 another_error constant number default -20002;
8 another_error_exception exception;
9 pragma exception_init( another_error_exception, -20002 );
10
11 procedure throw( p_exception in number, p_extra_msg in varchar2 default NULL );
12
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
3
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
10
11 type myarray is table of varchar2(255) index by binary_integer;
12 err_msgs myArray;
13
14
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.
25
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"
46
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;
53
54 raise_application_error( l_exception ,
55 'Exception at ' || l_type || ' ' ||
56 l_owner || '.' || l_name || '(' || l_line || ') ' ||
57 '**UNKNOWN ERROR**' || ' ' || p_extra_msg );
58
59 end;
60
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'
65
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.


We

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