|Author's name: Ryan Gaffuri
Author's Email: firstname.lastname@example.org
|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