PL/SQL Exceptions

In PL/SQL, exceptions are used to handle errors and abnormal conditions that may arise during the execution of a program.

When an error occurs, an exception is raised, and the program can handle it using exception handling blocks.

You can declare your own exceptions or use predefined exceptions provided by Oracle.

 

DECLARE

   my_exception EXCEPTION;

BEGIN

   -- Code that may raise an exception

   RAISE my_exception;

EXCEPTION

   WHEN my_exception THEN

      -- Code to handle the exception

      DBMS_OUTPUT.PUT_LINE('My custom exception was raised.’);

END;

 

SYSTEM EXCEPTION

These exceptions are predefined by Oracle and are raised automatically when certain error conditions occur during program execution.

NO_DATA_FOUND: Raised when a SELECT statement returns no rows.

TOO_MANY_ROWS: Raised when a SELECT INTO statement returns more than one row.

ZERO_DIVIDE: Raised when an attempt is made to divide by zero.

INVALID_NUMBER: Raised when conversion of a character string to a number fails.

BEGIN

   SELECT 1/0 INTO result FROM dual;

EXCEPTION

   WHEN ZERO_DIVIDE THEN

      DBMS_OUTPUT.PUT_LINE('Division by zero error occurred.');

END;

EXCEPTION:

This keyword marks the beginning of the exception handling part of the PL/SQL block.

WHEN ZERO_DIVIDE THEN:

This line specifies the exception handler for the ZERO_DIVIDE exception. If a ZERO_DIVIDE exception is raised, the code inside this block will be executed.

 

USER DEFINED EXCEPTION

These exceptions are defined by the programmer to handle specific error conditions that are not covered by system exceptions. They provide a way to customize error handling for application-specific scenarios.

RAISE Statement: You can explicitly raise an exception using the RAISE statement.

DECLARE

   my_exception EXCEPTION;

BEGIN

   IF condition THEN

      RAISE my_exception;

   END IF;

EXCEPTION

   WHEN my_exception THEN

      DBMS_OUTPUT.PUT_LINE('My custom exception was raised.');

END;

 

RAISE my_exception;:

This statement explicitly raises the user-defined exception my_exception.

When this statement is executed, the normal flow of the PL/SQL block is interrupted, and control is transferred to the exception handling part of the block.

 

EXCEPTION:

This keyword marks the beginning of the exception handling part of the PL/SQL block.

 

WHEN my_exception THEN:

This line specifies the exception handler for the my_exception exception.

If the my_exception exception is raised, the code inside this block will be executed.

 

RAISE_APPLICATION_ERROR Function: You can use the RAISE_APPLICATION_ERROR function to raise a user-defined exception with a custom error message and error code.

BEGIN

   IF condition THEN

      RAISE_APPLICATION_ERROR(-20001, 'Custom error message');

   END IF;

END;

RAISE_APPLICATION_ERROR(-20001, 'Custom error message');:

This function is used to raise a user-defined exception.

-20001 is a custom error code. Oracle allows user-defined error codes in the range of -20000 to -20999.

'Custom error message' is the custom error message that will be displayed when the exception is raised.

When this function is called, it stops the normal execution of the PL/SQL block and raises an exception with the specified error code and message.