PL/SQL allows the programmer to build an exception handler to deal with exceptions that occur within the block. Exceptions may be either Oracle errors or errors that the programmer decides on based on the logic of the program.
There are three kinds of errors: predefined Oracle errors, other Oracle errors, user-defined errors.
See the notes for additional information.
This is an example of a predefined error. The two errors that are being dealt with in the code are multiple rows being found by the select and no rows being found by the select.
The value of the exception trapping is that the processing handles the error rather than having the error dealt with outside the block.
See the notes for information on predefined errors.
Note that the PL/SQL procedure was successfully completed. The fact that the record was not found was handled with the EXCEPTION error.
Note that even though the procedure was completed, no change was made to the data because the record does not exist.
When the exception is commented out, the processing is not complete and the error message appears.
Note there is also a WHEN OTHERS THEN line that will trap non anticipated errors. I have specific error messages for the errors that I anticipate happening. I will handle the others with a generic message.
Note that again processing is successful because the exception was handled through the exception routine.
When I attempted to add a record to the ordline table that did not have a matching order number in the orderz table, an integrity error occured. This is because I had established a foreign key link from ordno in the ordline table to ordno in the ordez table. This attempted addition violated the foreign key link.
Procedure successfully completed because the error was trapped and handled in the exception routine.
Again note the generic WHEN OTHERS THEN which will deal with other kinds of errors that may occur.
PRAGMA EXCEPTION_INIT followed by the exception name that I defined and the specific number of the anticipated is the code that is used when a non predefined Oracle error is being dealt with.
Note that the 2291 appears as the error on the previous slide.
In the example from the notes, I did not want to allow the number ordered to be greater than 200.
Note that 300 is hard coded into the block and therefore the IF statement raises the error.
First I define the error name.
Second, I RAISE that error when a particular error occurs.
Third, the error is handled by the exception routine so the procedure is successfully completed.
Notice that this is not an Oracle error - it is a logic or data error that the programmer wants to specify. This is none with the RAISE to let Oracle know that an error has happened and the EXCEPTION routine should deal with it.
In this example, a constraint was put on a table so that no data could be entered if the numord was not <=200.
When I executed the block of code that attempted to put in 300, an exception occurred. In this case, I simply let the OTHERS catch the exception.
In this example I am providing a place to store the error code and the error message. When an error occurs I am using the WHEN OTHERS in the EXCEPTION routine to display the SQLCODE and the SQLERRM that Oracle generates.
This handles seeing the error but still having the procedure successfully completed.
Note that SQLCODE returns the code of the error and SQLERRM returns the message.
Since I now know the error number for the constraint violation (see previous slide), I can set up the error is as a test for a particular error and particular error handling if that message occurs. Note I left the WHEN OTHERS for other types of errors.
Note: The error came out in one line - I made it two to fit on the slide.
In this example, I am violating the foreign key primary key set up - the Oracle errcode and message which come from SQLCODE and SQLERRM are displayed. Note that SQLCODE returns the code of an error and SQLERRM returns the message itself.
This code loops. When a record is found with yrgoal less than 100 it will raise the error message and the processing will be complete.
Additional coding is required to be able to code a routine that will continue to process.
Note that when a record is encountered that is less than 100 the error message is displayed and the procedure ends with a successfully completed message.