Exception in Oracle

Errors occur during runtime processing due to either hardware or network failure or application logic errors are known as exception.

Types of Exceptions in Oracle
Predefined Oracle Exception
User-Defined Exception

Predefined Oracle Exceptions are a part of the package and need not be explicitly written or declared. These exceptions are called if any oracle violation is overridden.

Example:
ORA-06530- ACCESS_INTO_NULL
ORA-06511- CURSOR_ALREADY_OPEN

User-Defined Exception is declared and written by the user himself depending on the need. They need to be raised explicitly using the RAISE command and declared in declaration section.

Following is the predefined oracle exceptions

No_data_found
Too_many_rows
Zero_divide
Login_denied
Program_error
Timeout_on_resource
Invalid_cursor
Cursor_already_open
Dup_val_on_index

 

A user can explicitly raise an exception by using the Raise command

Oracle is not aware of these exceptions unless declared.

Example:
DECLARE
        Trans EXCEPTION;
BEGIN
IF TO_CHAR(SYSDATE, ‘DY’)= ‘SUN’ THEN
RAISE Trans;
END IF
EXCEPTION
WHEN trans THEN
DBMS_OUTPUT.PUT_LINE(‘No transactions today’);
END;

 

How PL/SQL Exceptions Are Raised?

The PL/SQL exceptions can be raised in 4 ways. Either the user can raise them or they can be raised by the PL/SQL engine. They are as follows:

The PL/SQL runtime engine raised named system exception: These exceptions are raised automatically by the program. You cannot control when PL/SQL will raise a system exception.

The programmer raised named exception: The programmer can use an explicit call to the RAISE statement to raise a programmer-defined or system-named exception.

The programmer raised unnamed, programmer-defined exception: These are raised with an explicit call to the RAISE_APPLICATION_ERROR procedure in the DBMS_STANDARD package.

The programmer re-raised "current" exception: From within an exception handler, you can re-raise the same exception for propagation to the enclosing block