Reference no: EM131066620

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)?