PL/SQL Raise Exceptions

Summary: in this tutorial, you will learn how to use the PL/SQL RAISE statement to raise a user-defined exception, internally defined exception, and reraising an exception.

To raise an exception explicitly, you use the RAISE statement. The RAISE statement allows you to:

  • Raise a user-defined exception.
  • Raise an internally defined exception.
  • Reraising the current exception.

Raising a user-defined exception

A user-defined exception is defined by users like you or other developers in the declaration section of a block or subprogram.

Declaring a user-defined exception

To define a user-defined exception, you use the following syntax:

DECLARE exception_name EXCEPTION;
Code language: SQL (Structured Query Language) (sql)

Similar to the variable declaration, you declare an exception in the declaration section of a block.

A user-defined exception must have assigned error_code . To do it, you use the EXCEPTION_INIT pragma as follows:

PRAGMA EXCEPTION_INIT (exception_name, error_code)
Code language: SQL (Structured Query Language) (sql)

In this syntax, the error_code is an integer that ranges from -20,999 to -20,000. And the message is a character string with a maximum length of 2,048 bytes.

The entire syntax for declaring a user-defined exception is as follows:

DECLARE exception_name EXCEPTION; PRAGMA EXCEPTION_INIT (exception_name, error_number);
Code language: SQL (Structured Query Language) (sql)

Raising a user-defined exception example

The following example illustrates how to declare a user-defined exception and associate it with an error code.

DECLARE e_credit_too_high EXCEPTION; PRAGMA exception_init( e_credit_too_high, -20001 ); l_max_credit customers.credit_limit%TYPE; l_customer_id customers.customer_id%TYPE := &customer_id; l_credit customers.credit_limit%TYPE := &credit_limit; BEGIN -- get the meax credit limit SELECT MAX(credit_limit) INTO l_max_credit FROM customers; -- check if input credit is greater than the max credit IF l_credit > l_max_credit THEN RAISE e_credit_too_high; END IF; -- if not, update credit limit UPDATE customers SET credit_limit = l_credit WHERE customer_id = l_customer_id; COMMIT; END; /
Code language: SQL (Structured Query Language) (sql)

In this example,

  • First, declare a user-defined exception e_credit_too_high and associates it with the error number -20001.
  • Second, select maximum credit from the customers table using the MAX() function and assign this value to the l_max_credit variable.
  • Third, check if the input credit with the maximum credit, if the input credit is greater than the max, then raise the e_credit_too_high exception.
  • Finally, update the customer whose id is entered by the user with the new credit limit.

Here is the output if you enter customer id 100 and credit limit 20000:

ORA-20001:

If you want to include a custom message, you can replace the line:

RAISE e_credit_too_high;
Code language: SQL (Structured Query Language) (sql)

by the following line:

raise_application_error(-20001,'Credit is too high');
Code language: SQL (Structured Query Language) (sql)

And execute the code block again, you will receive the following error:

ORA-20001: Credit is too high
Code language: SQL (Structured Query Language) (sql)

Raising an internally defined exception

Typically, the runtime system raises internally defined exceptions implicitly when they occur. Besides, you can explicitly raise an internally defined exception with the RAISE statement if the exception has a name:

RAISE exception_name;
Code language: SQL (Structured Query Language) (sql)

This example shows how to raise an internally defined exception INVALID_NUMBER:

DECLARE l_customer_id customers.customer_id%TYPE := &customer_id; BEGIN -- get the meax credit limit IF l_customer_id < 0 THEN RAISE invalid_number; END IF; END; /
Code language: SQL (Structured Query Language) (sql)

If you execute the block and enter the customer id -10, you will get the following error:

ORA-01722: invalid number
Code language: SQL (Structured Query Language) (sql)

Reraising the current exception

You can re-raise the current exception with the RAISE statement. Reraising an exception passes it to the enclosing block, which later can be handled further. To reraise an exception, you don’t need to specify the exception name.

DECLARE e_credit_too_high EXCEPTION; PRAGMA exception_init( e_credit_too_high, -20001 ); l_max_credit customers.credit_limit%TYPE; l_customer_id customers.customer_id%TYPE := &customer_id; l_credit customers.credit_limit%TYPE := &credit_limit; BEGIN BEGIN -- get the max credit limit SELECT MAX(credit_limit) INTO l_max_credit FROM customers; -- check if input credit is greater than the max credit IF l_credit > l_max_credit THEN RAISE e_credit_too_high; END IF; EXCEPTION WHEN e_credit_too_high THEN dbms_output.put_line('The credit is too high' || l_credit); RAISE; -- reraise the exception END; EXCEPTION WHEN e_credit_too_high THEN -- get average credit limit SELECT avg(credit_limit) into l_credit from customers; -- adjust the credit limit to the average dbms_output.put_line('Adjusted credit to ' || l_credit); -- update credit limit UPDATE customers SET credit_limit = l_credit WHERE customer_id = l_customer_id; COMMIT; END; /
Code language: SQL (Structured Query Language) (sql)

In this example:

  • First, get the max credit limit from the customers table.
  • Second, compare the max credit with the user-input credit. If the user-input credit is greater than the max credit, then raise the e_credit_too_high exception.
  • Third, display a message and reraise the exception in the exception-handling section in the inner block.
  • Finally, in the outer block, reassign the average credit to the l_credit variable and update the customer with the newly adjusted credit.

If you enter the customer id 100 and credit limit 10000, the credit limit of the customer will be updated to the average credit.

SELECT * FROM customers WHERE customer_id = 100;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

plsql raise exception example

In this tutorial, you have learned how to use the PL/SQL RAISE statement to explicitly raise a user-defined exception, internally defined exception, and reraising an exception.

Was this tutorial helpful?