Oracle custom error message

When developing scripts and code with PL/SQL for a oracle database you always like to think that your code is the best in the world. You would like to think it will never result in an error. However, users who are using your code will find a way to crash it; you will have overlooked some possibilities. So even after you and several other people have tested the code you will find that in some cases a error will happen.

So you have implemented all kinds of error handling, the problem with “standard” error handling is that it will generate all kinds of user unfriendly error messages. For developers and DBA’s this will make sense however if you want it to be shown to your user community and make sure it will have some meaning it might be nice to have a custom error message.

In Oracle PL/SQL you can use the RAISE_APPLICATION_ERROR procedure. RAISE_APPLICATION_ERROR allows you to set a custom error message which will have more meaning to an end-user than the standard ORA messages. You will also have the option to attach a custom ORA number so you know where it has happened in the code so you will have some more useful information while debugging your code.

For example if I want to raise a error like ORA-60001: The value you entered is not a valid customer ID number I have to tell my code to somehow do this.

So let’s say you have some IF clause which checks the given value for the customer ID which is entered by a user. If the check ends in a successes there is no need to raise a error. If it fails you can use RAISE_APPLICATION_ERROR to show the error message. It is done as shown below:

RAISE_APPLICATION_ERROR(-60001,’ The value you enterd is not a valid customer ID number’);

It goes without saying that you can add some variables to the message so it might be nice to show the user for example the value he has entered. However you might in that case also consider having an alternative error message in case RAISE_APPLICATION_ERROR will be unable to handle the variable. Think about a customer ID which has a length beyond the length that can be shown… etc etc etc.

However, using custom error messages in your PL/SQL code is a good way of showing your users what is wrong. It is better than having the standard somewhat cryptic messages which are provided by Oracle.