Oracle PL/SQL Exception

Yusuf SEZER

4 years ago

This article contains information about Oracle PL/SQL Exception and Types such as system defined, user defined.

What is Exception in Oracle PL/SQL?

These are the structures used for the management of errors that occur during the execution of commands.

Oracle PL/SQL Exception Types

System-defined
User-defined

The use of the PL / SQL Exception structure is as follows.

DECLARE
    -- definitions
BEGIN
    -- commands
EXCEPTION
    WHEN HATA-TURU THEN
        -- commands
    WHEN OTHERS THEN
        -- commands
END;

DECLARE

-- definitions

BEGIN

-- commands

EXCEPTION

WHEN HATA-TURU THEN

-- commands

WHEN OTHERS THEN

-- commands

END;

Sample Exception Usage is as follows;

BEGIN
    DBMS_OUTPUT.put_line(3/0);
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.put_line('Divide by zero error.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('unknown error.');
END;

BEGIN

DBMS_OUTPUT.put_line(3/0);

EXCEPTION

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.put_line('Divide by zero error.');

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('unknown error.');

END;

System Defined Exceptions in Oracle PL/SQL

The Exception type previously created by Oracle PL / SQL is called System defined.

System-defined exception types are listed below.

ACCESS_INTO_NULL
ASE_NOT_FOUND
COLLECTION_IS_NULL
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER, LOGIN_DENIED
NO_DATA_FOUND
NOT_LOGGED_ON
PROGRAM_ERROR
ROWTYPE_MISMATCH
SELF_IS_NULL
STORAGE_ERROR
TOO_MANY_ROWS
VALUE_ERROR
ZERO_DIVIDE

User Defined Exceptions in Oracle PL/SQL

Oracle also allows custom exception definition. These Exception types are called user defined exceptions. You can create User Defined Exception as follows.

DECLARE
    MY_CUSTOM_ERROR EXCEPTION;
BEGIN
    NULL;
END;

DECLARE

MY_CUSTOM_ERROR EXCEPTION;

BEGIN

NULL;

END;

We can set a special error code for the exception as follows.

DECLARE
    CUSTOM_ERROR EXCEPTION;
    PRAGMA EXCEPTION_INIT (CUSTOM_ERROR, -1453);
BEGIN
    NULL;
END;

DECLARE

CUSTOM_ERROR EXCEPTION;

PRAGMA EXCEPTION_INIT (CUSTOM_ERROR, -1453);

BEGIN

NULL;

END;

The occured error can be triggered by RAISE.

SET SERVEROUTPUT ON;
DECLARE
    CUSTOM_ERROR EXCEPTION;
    PRAGMA EXCEPTION_INIT (CUSTOM_ERROR, -1453);
    v_sayi PLS_INTEGER := '0';
BEGIN
    IF v_sayi = 0 THEN
        RAISE CUSTOM_ERROR;
        -- RAISE_APPLICATION_ERROR(-1453, 'Another Error.');
    END IF;
EXCEPTION
    WHEN CUSTOM_ERROR THEN
        DBMS_OUTPUT.put_line('Special Error Occured.');
        DBMS_OUTPUT.put_line(SQLERRM);
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('Unknown Error Occured.');
END;

SET SERVEROUTPUT ON;

DECLARE

CUSTOM_ERROR EXCEPTION;

PRAGMA EXCEPTION_INIT (CUSTOM_ERROR, -1453);

v_sayi PLS_INTEGER := '0';

BEGIN

IF v_sayi = 0 THEN

RAISE CUSTOM_ERROR;

-- RAISE_APPLICATION_ERROR(-1453, 'Another Error.');

END IF;

EXCEPTION

WHEN CUSTOM_ERROR THEN

DBMS_OUTPUT.put_line('Special Error Occured.');

DBMS_OUTPUT.put_line(SQLERRM);

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('Unknown Error Occured.');

END;

The RAISE_APPLICATION_ERROR function takes the “error code”, “error message”, and “whether the error should be replaced with existing errors” as parameters to create a custom error.

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

You can find more information about exception at docs.oracle.com