Q1. Explain the steps in creating Oracle non-predefined exception handlers and user defined exception handlers.
Q2. Respond to the following;
Cite References
Oracle non-predefined exception handlers allow exception handling for Oracle numbered errors that do not have predefined exceptions. This requires associating the error number with a user developed exception. This is accomplished by declaring the exception, then using a pragma statement to tie the declared exception to a desired Oracle number (Casteel, 2013, p. 149-150). For example:
Declare exception: ex_name_condition EXCEPTION;
Pragma statement: PRAGMA EXCEPTION_INIT(ex_name_condition, oracleNumber);
At this point, the exception portion of the PL/SQL block is executed when the Oracle error number is encountered.
A user defined exceptions allow developers to enforce business rules. These type of exceptions will not generate predefined exceptions or errors, so they must be developed to execute under certain circumstances (Casteel, 2013, p. 151-152). This is accomplished by declaring the exception, then executing the exception with the RAISE statement when a certain condition is met. For example:
Declare exception: ex_name_condition EXCEPTION;
IF condition THEN RAISE ex_name_condition;
At this point, the exception portion of the PL/SQL block executes the user defined statement or action.
References
Casteel, J. (2013). Oracle 11g: PL/SQL Programming. Boston, MA: Course Technology.
Q3. Respond to the following;
Cite References
Oracle provides some pre-defined exceptions which are names associated with some common errors that are encountered.
Non-predefined exceptions
If we receive an Oracle error that doesn't have a pre-defined exception we may need to create and exception for it and associate it with the Oracle error code. To do this we need to do the following:
DECLARE
Declare the exception name
Associate an Oracle error number with the exception name using the PRAGMA statement
BEGIN
Contains the error sequence
EXCEPTION
Exception handler runs if the sequence declared in BEGIN raises the error code contained in DECLARE
User-defined exceptions
A user-defined error is an error we manually create to handle what we perceive to be an error in the system. For example if no rows were updated after running some code. User defined errors are set up in the following format:
DECLARE
Declare the exception giving it a name
BEGIN
Contains the error sequence that we perceive as incorrect e.g if no rows are updated
EXCEPTION
Exception handler runs if the sequence declared in BEGIN raises the error code contained in DECLARE
Reference
Casteel, J. (2013). Oracle 11g: PL/SQL Programming, 2nd Edition. [VitalSource Bookshelf Online].
Q4. Answer the questions and be sure to cite work
What are the differences between anonymous PL/SQL code and procedures (Oracle named blocks)?