Handle errors in Oracle functions, processes, triggers, and so on

I. Caught error exceptions

1: capture predefined error exceptions in the Oralce system.

2: capture custom error exceptions.

Ii. Methods for capturing error exceptions

In the Oralce database object.

1: Definition error variable

User_Exception EXCEPTION;

2:Begin

-- Execute SQL statements

Exception

When others then
Null;

End;

Instance:

Create or replace procedure P_User_Pro (AL_ORDER_ID in NUMBER) is

Order_Id_Null EXCEPTION; -- custom error EXCEPTION

If AL_ORDER_ID = 0 or isnull (AL_ORDER_ID) then

RAISE Order_Id_Null;

End if;

Begin

Update User_Table_Name

Set order_state = '01'

Where order_id = AL_ORDER_ID;

Exception

When others then
RAISE_APPLICATION_ERROR (-20014, 'an error occurred while updating the document status! Error: '| SQLCODE | SQLERRM );
Return;

End;

Exception

When Order_Id_Null then -- catch custom error exceptions

RAISE_APPLICATION_ERROR (-20015, 'incorrect document ID! Error: '| SQLCODE | SQLERRM );

Rollback;

When other then

RAISE_APPLICATION_ERROR (-20016, 'an error occurred while updating the document status! Error: '| SQLCODE | SQLERRM );

Rollback;

End P_User_Pro;