Parameterized Customized Oracle Database Error Messages

Avi Abrami

1 - The Requirement

Oracle database reserves error codes in the range  
                        
-20000 to -20999 for  customized error messages. 
Often you want to include erroneous details in  the body of your error message. 
For example, if you want to tell the user  that [s]he entered a non-existent employee number, 
you would like to remind  them what incorrect number they entered. 
Therefore, instead of supplying an error message that says:   
                        
No such employee ID.   
You would probably prefer an error message similar to the following: Employee ID 12345 not found.    

However, how do you put the actual employee ID into the error message?
                      

2 - Customizing Oracle Database Error Message Using Oracle JVM

You can use Oracle's database embedded JVM and the core java class  "java.text.MessageFormat". 
The "MessageFormat" class has a method "format()"  that receives an Object array method as parameter 
and returns a String. The method  replaces placeholders in the String parameter with values from 
the array parameter.    
Using the example above, the default Oracle error message string would be:     
                        
Employee ID {0} not found.    
And you would invoke the 'format()' method as follows
    
                        
MessageFormat.format("Employee ID {0} not found", new Object[]{"12345"});      
                      

3 - Implementation

Here are the implementation details: 

1. Create a PL/SQL collection type. 
   This must be a named, database type, otherwise it cannot be passed from PL/SQL to java. 
     create or replace type STR_ARR is table of varchar2(100);      


2. Next create a java class that will be later used as a java stored  procedure:    


     import java.sql.Array;  
     import java.sql.SQLException;  
     import java.text.MessageFormat;     
     
     public class MsgFormatter {    
        
        public static String fmtMsg(String msg, Array prms) {      
           MessageFormat mf = new MessageFormat(msg);      
           Object[] objs = (Object[]) prms.getArray();      
           return mf.format(objs);    
        }  
      }      


3. Load the java class into the database. 
   You can use the loadjava utility  to do this.    


4. Create a java stored procedure for the class you loaded into the  database. 
   For example:  
    create or replace function FORMAT_MSG (P_MSG varchar2, P_ARR STR_ARR) return  varchar2  
       is language java name  'MsgFormatter.fmtMsg(java.lang.String, java.sql.Array) 
         return  java.lang.String';      

And that's it. 

4 - Use It

You can now supply your custom error message in your PL/SQL  code, as follows:      
 
 create or replace procedure FIND_EMP (P_EMP_ID  number)  
 is    
  L_ARR  STR_ARR;    
  L_DUMMY  char(1); 
  begin    
      L_ARR := STR_ARR(to_char(P_EMP_ID));    
      select 'X' into L_DUMMY    from EMP    where EMPNO = P_EMP_ID;  
  exception   
      when NO_DATA_FOUND then 
       RAISE_APPLICATION_ERROR(-20001,  
                        
FORMAT_MSG('Employee ID {0} not found.', L_ARR));  
  end; 
  /