Module 08: Handling Exceptions

Handling Exceptions

01. What is an Exception?

An exception is a PL/SQL error that is raised duringprogram execution.

02. What are the Methods for Raising an Exception?

An exception can be raised:

a. Implicitly by the Oracle Server

b. Explicitly by the program

03. What are the process of Handling Exceptions

An exception can be handled:

a. By trapping it with a handler

b. By propagating it to the calling environment

04. What are the types of Exception?

Exception:

a. Implicitly raised:  

i. Predefined oracle server

ii. Non-predefined oracle server

b. Explicitly raised: User defined

05. Write down the Guidelines for Trapping Exceptions.

Guidelines:

a. The EXCEPTION keyword starts the exception-handlingsection.

b. Several exception handlers are allowed.

c. Only one handler is processed before leaving the block.

d. WHEN OTHERS is the last clause.

06. How to you trap predefined oracle server errors?

Server Errors:

a. Trapping Predefined Oracle Server Errors

b. Trap a predefined Oracle Server error by referencing its predefined name within thecorresponding exception-handling routine.

07. Mention the Sample predefined exceptions, please.

Sample predefined exceptions:

a. NO_DATA_FOUND.

b. TOO_MANY_ROWS.

c. INVALID_CURSOR.

d. ZERO_DIVIDE.

e. DUP_VAL_ON_INDEX.

08. Write down the lifecycle for Trapping Non-Predefined Oracle Server Errors.

09. What are the types of Functions for Trapping Exceptions?

Trapping Exceptions:

a. SQLCODE: Returns the numeric value for the error code.

b. SQLERRM: Returns the message associated with the error number.

10. Write down the lifecycle for Trapping User-Defined Exceptions.

11. How PL/SQL Exceptions Propagate

When an exception is raised, if PL/SQL cannot find a handler for it in the current block or subprogram, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. In the latter case, PL/SQL returns an unhandled exception error to the host environment.

12. Write down the use of RAISE_APPLICATION_ERROR Procedure.

RAISE_APPLICATION_ERROR Procedure:

a. You can use this procedure to issue user-defined error messages from stored subprograms.

b. You can report errors to your application and avoid returning unhandled exceptions.