Oracle PL/SQL Exception Handling

An Exception in PL/SQL is a runtime error that occurs during code execution. When an exception is raised, the current PL/SQL block stops immediately, and control moves to the EXCEPTION section. If an appropriate exception handler exists, any final corrective actions will be carried out before the block exits; otherwise, the unhandled error is passed to the host environment.

Exceptions in PL/SQL are categorized into three types: predefined Oracle server exceptions, non-predefined Oracle server exceptions, and user-defined exceptions.

Predefined Oracle Server Exceptions are common system errors with predefined names, such as NO_DATA_FOUND (ORA-01403) and TOO_MANY_ROWS (ORA-01422). These exceptions are automatically declared by Oracle and do not require explicit declaration in the code block. Non-Predefined Oracle Server Exceptions, on the other hand, handle standard Oracle server errors that lack explicit built-in names.

These exceptions need to be declared in the DECLARE section and associated with the specific Oracle server error number using PRAGMA EXCEPTION_INIT. User-Defined Exceptions are custom business logic violations defined by application developers and are declared in the DECLARE section using the RAISE or RAISE_APPLICATION_ERROR statements.

Only one exception handler can be executed per block execution, and a block can contain multiple exception handlers, but only one WHEN OTHERS clause is allowed. Exception handlers cannot be placed inside assignment statements or standard SQL statements. Oracle provides two key functions inside exception handlers to extract diagnostic data: SQLCODE, which returns the numeric error code, and SQLERRM, which returns the error message string corresponding to the SQLCODE.

These functions cannot be used directly inside SQL statements and must be assigned to local variables or used via standard PL/SQL wrapper statements.