pl-sql
  1. pl-sql-exception
PreviousNext

Exception - (PL/SQL Exception)

Exceptions are a way of handling errors or exceptional circumstances in a PL/SQL program. When an error occurs, an exception is raised and the program jumps to the exception handling section of the code. This allows for more robust error handling and can help to prevent a program from crashing or producing incorrect results.

Syntax

The syntax for handling exceptions in PL/SQL is as follows:

BEGIN
  -- Code that may raise an exception

EXCEPTION
  WHEN exception_name THEN
    -- Exception handling code
END;

Here, the exception handling code is only executed if an exception with the specified name is raised in the try block.

Example

DECLARE
  num NUMBER := 5;
BEGIN
  IF num > 10 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Number cannot be greater than 10');
  END IF;

  DBMS_OUTPUT.PUT_LINE('Number is: ' || num);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

In this example, we declare a variable num and set it to 5. We then check if num is greater than 10, and if it is, we raise an exception with the error message "Number cannot be greater than 10" and an error code of -20001. Finally, we output the value of num to the console. If an exception occurs, the program prints the error message to the console.

Output

The output of this program depends on whether an exception is raised or not. If num is less than or equal to 10, the program outputs "Number is: 5". If num is greater than 10, the program raises an exception and outputs "An error occurred: ".

Explanation

In the above example, we have used the RAISE_APPLICATION_ERROR function to raise an exception if num is greater than 10. This causes the program to jump to the EXCEPTION block, which contains code to handle the exception. In this case, we are simply printing the error message to the console using the DBMS_OUTPUT.PUT_LINE function.

Use

Exceptions are a powerful tool for handling errors and exceptional circumstances in PL/SQL programs. They allow for more robust error handling and can help to prevent a program from crashing or producing incorrect results.

Important Points

  • Exceptions allow for more robust error handling in PL/SQL programs.
  • The RAISE_APPLICATION_ERROR function can be used to raise an exception with a custom error message and error code.
  • The EXCEPTION block contains code to handle any exceptions that may be raised in the try block.
  • Exception handling can help to prevent a program from crashing or producing incorrect results.

Summary

In summary, exceptions are an important aspect of error handling in PL/SQL programs. They allow for more robust error handling and can help to prevent a program from crashing or producing incorrect results. The RAISE_APPLICATION_ERROR function can be used to raise exceptions with custom error messages and error codes, while the EXCEPTION block contains code to handle any exceptions that may be raised in the try block.

CursorTrigger
Published on: