The Oracle (tm) Users' Co-Operative FAQ

How can I turn Oracle error messages into my own 'friendlier' error messages ?


Author's name: Ryan Gaffuri

Author's Email: rkg100@erols.com

Date written: 4th March 2002

Oracle version(s): 8.1

A PL/SQL block is composed of 4 parts: Declare, Begin, Exception, and End. In order to convert messages into a friendlier format, you need to learn about the Exception section and exception handling. This article will cover basic exception handling, basic built-in errors that you can convert, and how to take any other Oracle error message and transform it into a useable error message.

Back to index of questions


First lets begin with how Oracle handles exception. The first thing to remember is that PL/SQL is an extremely friendly environment and does an excellent job trapping errors. When you compile and execute some PL/SQL code that has an error, Oracle returns a number along with an error message. All processing stops. There are methods to pass the execution to the exception part of the block and process it. One of the most common errors is ORA-01403: no data found. This is not something you want to show to a user.

Well Oracle has a built in procedure called NO_DATA_FOUND. Lets look at a sample block...

DECLARE 
	v_variable NUMBER(10); 
BEGIN 
	SELECT COLUMN 
	INTO v_variable 
	FROM TABLE; 
EXCEPTION 
	/* 
		When-Then operates just like an IF-Then statement in the main part of the block. 
		DBMS_OUTPUT.PUT_LINE is a built in package(program) that will print information to the screen. 
		Remember to type "Set SERVEROUTPUT ON" Before running this program in order to see your output 
		The '*/' tells Oracle to ignore and not process this information. Its read only. 
	*/ 
WHEN NO_DATA_FOUND 
	THEN DBMS_OUTPUT.PUT_LINE('No Data'); 
END; 

If you run this simple implicit cursor and there is no data in the table processing will be passed to the EXCEPTION part of the block. In there you are saying "WHEN" there is no data(NO_DATA_FOUND) "THEN" print this message to the screen. There are several other built-in programs for handling exception. You can get a list of them at technet.oracle.com Go to the documentation and read the PL/SQL documentation listed under the Oracle 9i Database.

Oracle has thousands of possible errors. What happens if there isnt a built in for an error that you are getting? Well Oracle provides a feature called a Pragma that will allow you to associate an error message with a variable. Then you can do your WHEN-THEN off of that variable and return your message(or whatever else you want to do)

A pragma is an instruction to the Oracle compiler that tells it to do something. In this case you are telling Oracle to associate an error that you choose to a variable that you choose. This pragma must go in the declarative section of your block. Lets look at a pragma in action....

DECLARE 
	v_variable NUMBER(10); 
	v_error VARCHAR2(100); 
	PRAGMA EXCEPTION_INIT(01403, 'v_error'); -- Yes No Data found has a built-in. I just want to keep it simple 
BEGIN 
	SELECT COLUMN 
	INTO v_variable 
	FROM TABLE; 
EXCEPTION 
	/* 
		When-Then operates just like an IF-Then statement in the main part of the block. 
		DBMS_OUTPUT.PUT_LINE is a built in package(program) that will print information to the screen. 
		Remember to type "Set SERVEROUTPUT ON" Before running this program in order to see your output 
		The '*/' tells Oracle to ignore and not process this information. Its read only. 
	*/ 
WHEN v_error 
	THEN DBMS_OUTPUT.PUT_LINE('No Data'); 
END; 

The PRAGMA we are using is an EXCEPTION_INIT This simply means initialize this exception to this variable. So after I wrote the word PRAGMA I have the name of my exception PRAGMA EXCEPTION_INIT I then pass two variables. The first is the number of the error and the second is the variable that Im associating it to. This association is only good for this block. So the association will not exist anywhere else. There is also one slight difference in the exception section. Instead of WHEN 'NO_DATA_FOUND', I used v_error, which is the variable associated to that error in the pragma.

There is alot more to exception handling and it is an extremely important part of PL/SQL. I highly recommend referring to the PL/SQL documentation on technet.oracle.com. It's free and easy to read. .


Further reading: N/A


Back to top

Back to index of questions