PL/SQL Exceptions - Built in Exceptions

PL/SQL exceptions are predefined and raised automatically into oracle engine when any error occur during a program.

Each and every error has defined a unique number and message. When warning/error occur in program it's called an exception to contains information about the error.

In PL/SQL built in exceptions or you make user define exception. Examples of built-in type (internally) defined exceptions division by zero, out of memory. Some common built-in exceptions have predefined names such as ZERO_DIVIDE and STORAGE_ERROR.

Normally when exception is fire, execution stops and control transfers to the exception-handling part of your PL/SQL block. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which are also raise predefined exceptions.

PL/SQL exceptions consist following three,

1. Exception Type
2. Error Code
3. Error Message

Syntax

DECLARE
    declaration statement(s);  
BEGIN 
    statement(s); 
EXCEPTION
    WHEN built-in_exception_name_1 THEN
        User defined statement (action) will be taken;
    WHEN built-in_exception_name_2 THEN
        User defined statement (action) will be taken;  
END;

Example

SQL>edit buitin_exp
DECLARE
    temp enum%rowtype;
BEGIN
    SELECT * INTO temp FROM enum
        WHERE eno=3;
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line("Table haven't data");
END;
/

Result

PL/SQL built in exceptions

Following are built in type exception,

• « Previous
• Next »