PolarDB:Errors and messages

Exceptions

Overview

Exceptions are PL/SQL runtime errors. They can be caused by many reasons, such as design faults, coding mistakes, and hardware failures. Not all potential exceptions can be anticipated, but you can write exception handlers that allow your programs to run when an exception occurs. Any PL/SQL block can have an exception handling section that consists of one or more exception handlers. For example, you can use the following syntax to specify the exception handling section. The ex_name_1 field specifies the name of an exception and the statements_n field specifies one or more statements.

DECLARE
  ...
BEGIN
  ...
EXCEPTION
  WHEN ex_name_1 THEN statements_1
  WHEN ex_name_2 OR ex_name_3 THEN statements_2
  WHEN OTHERS THEN statements_3
END;

When an exception occurs in the executable part of the PL/SQL block, which is the BEGIN block, the executable part stops and the exception handling section takes over the control. Internally, PolarDB controls the triggering of exception conditions based on exception codes. Each ex_name_n maps a unique exception code. If ex_name_1 is raised, statements_1 is executed. If ex_name_2 or ex_name_3 is raised, statements_2 is executed. If other exceptions are raised, statements_3 is executed. The OTHERS keyword indicates all other exceptions. If you do not specify OTHERS and an exception that is not trapped by an ex_name_n field occurs, the exception is raised and handled by the exception handling section of the invoker of the PL/SQL block. If no PL/SQL block can handle the exception, the exception is eventually thrown to the invoker.

Exception handlers help you write a more understandable program in an easier manner and reduce the occurrence of unhandled exceptions. If you do not use exception handlers, you must check all positions where exceptions may occur and handle the exceptions. Potential exceptions and positions where exceptions may occur are easily overlooked, especially when the exceptions cannot be immediately detected. For example, you cannot detect bad data unless you use the data in your calculation.

If you use exception handlers, you do not need to know every exception and the position where the exception may occur. You need to only configure an exception handling section in all blocks in which an exception may occur. An exception handling section contains exception handlers for both specific and unknown exceptions. If an exception occurs in the block or its subblocks, an exception handler handles the exception. The code of exception handlers is isolated in the exception handling section of a block.

Internal exceptions

An internal exception is an internally defined exception that may occur when the system is running. PolarDB uses a unique five-character code to represent each internal exception. Internal exceptions also have unique exception names.

For example, 22012 indicates the division_by_zero exception and 2202E indicates the array_subscript_error exception. You can specify an internal exception by setting the ex_name_n field to either the exception code or exception name, as shown in the following examples.

EXCEPTION
  WHEN sqlstate '22012' THEN
    ...
  WHEN array_subscript_error THEN
    ...

You can use the RAISE statement to explicitly raise an internal exception.

DECLARE
BEGIN
  RAISE division_by_zero; -- Explicitly raise the divide_by_zero exception.
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'catch exception!';
    RAISE; -- Raise the exception again. 
END;

Predefined exceptions

In PolarDB, each internal exception has an exception name. Therefore, you can catch the internal exceptions by using exception names. In addition, PolarDB is compatible with some Oracle-defined exceptions. The following table describes the mappings between Oracle exception names and PolarDB exception names.

User-defined exceptions

You can use the following syntax to declare a custom exception. Then, you can raise and catch the custom exception.

DECLARE
  exception_name EXCEPTION;
BEGIN
  RAISE exception_name;
EXCEPTION
  WHEN exception_name THEN
    RAISE NOTICE 'catch user-defined exception!';
    RAISE NOTICE '% : %', SQLCODE, SQLERRM;
END;

If a user-defined exception is not bound to an exception code, the exception is bound to an internal exception code. The exception code that you obtain by using SQLCODE is 1 and the exception message that you obtain by using SQLERRM is 'User-Defined Exception'.

If you want to bind a specific exception code to a user-defined exception and associate a specific exception message with the user-defined exception when the user-defined exception is raised, you can use the following syntax:

DECLARE
  my_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT (my_exception, -20001); -- Bind the exception to an exception code.
BEGIN
  RAISE_APPLICATION_ERROR(-20001, 'raise a special divide by zero exception! '); -- Raise the exception based on the exception code and associate an exception message with the exception.
EXCEPTION
  WHEN my_exception THEN -- WHEN zero_divide/division_by_zero THEN
    RAISE NOTICE '% : %', SQLCODE, SQLERRM;
END;

Execution result:

NOTICE: -20001 : raise a special divide by zero exception!

You can use the following syntax to bind an exception code to a user-defined exception. The exception code must be a negative number in the Oracle style.

 PRAGMA EXCEPTION_INIT (exception_name, error_code);

Then, you can use the RAISE_APPLICATION_ERROR procedure to raise the exception based on the exception code and associate an exception message with the exception. Valid values of an exception code: -20000 to 20999.

RAISE_APPLICATION_ERROR(error_code, 'raise a special exception! ');

If you create a user-defined exception whose name is the same as a predefined exception, the exception that you define overrides the predefined exception. We recommend that you do not override predefined exceptions in PolarDB.

DECLARE
  zero_divide EXCEPTION; 
BEGIN
  RAISE zero_divide;
EXCEPTION 
  WHEN zero_divide THEN
    RAISE NOTICE '% %', sqlcode, sqlerrm;
END;

Execution result:

NOTICE:  1 User-Defined Exception

Explicitly raise an exception

The system automatically raises internal exceptions when system errors occur. In addition to automatic exception raising, you can use the methods shown in the following sample code to explicitly raise exceptions:

DECLARE
  my_exception EXCEPTION;
BEGIN
  -- The nested blocks.
  DECLARE
  BEGIN
    RAISE my_exception; -- 1. Raise a specific exception.
  EXCEPTION
    WHEN my_exception THEN
      RAISE NOTICE 'catch inner exception!';
      RAISE; -- 2. Raise the current exception.
  END;

EXCEPTION
  WHEN my_exception THEN
    RAISE_APPLICATION_ERROR(-20000, 'raise a special exception!'); -- 3. Use the RAISE_APPLICATION_ERROR procedure to raise an exception with the specified exception code and exception message.
END;

Execution result:

NOTICE:  catch inner exception!
ERROR:  raise a special exception!

Exception propagation

If an exception is raised in a block that does not contain an exception handler, the exception propagates to outer blocks until a block handles the exception. If no block handles the exception, PL/SQL returns the unhandled exception to the invoker or host environment. If the exception is handled by a block, the next statement in the outer block is executed. If no outer block exists, the system returns to the invoker or host environment.

Obtain the exception code and exception message

If an exception is not catched, you can obtain the exception message in the console or other invokers, but you cannot obtain the exception code. After an exception is catched, you can obtain the exception code by using SQLCODE and the exception message by using SQLERRM. Example:

DECLARE
  ...
BEGIN
  ...
EXCEPTION 
  WHEN OTHERS THEN
    RAISE NOTICE '% %', sqlcode, sqlerrm;
END;

Transaction behavior

A PL/SQL program is always run in a transaction. If an EXCEPTION block exists, PolarDB implicitly creates a subtransaction to execute the statements in the BEGIN block. If an exception occurs in the BEGIN block, the subtransaction is rolled back and a new subtransaction is created to execute the statements in the EXCEPTION block. If the statements in the EXCEPTION block are successfully executed, the subtransaction can be committed. Example:

-- Prepare a test table.
CREATE TABLE test(id INT);

-- Raise an exception in the BEGIN block. The statements in the EXCEPTION block can be executed.
DECLARE
  my_exception EXCEPTION;
BEGIN
  INSERT INTO test VALUES(1);
  RAISE my_exception;
EXCEPTION
  WHEN my_exception THEN
    RAISE NOTICE 'catch!';
    INSERT INTO test VALUES(2);
END;

-- Query data from the table.
SELECT id FROM test;

Execution result:

NOTICE:  catch!
DO
postgres=# select * from t;
 id 
----
  2
(1 row)

If you want to retain the result that has been generated in the BEGIN block before the exception is raised, explicitly commit transactions or enable statement-level transactions. When you perform an explicit commit, the subtransaction and its parent transaction are both comitted. A new transaction and subtransaction are generated to execute the subsequent statements.

-- Clear the test table.
DELETE FROM test;

DECLARE
  my_exception EXCEPTION;
BEGIN
  INSERT INTO test VALUES(1);
  COMMIT; -- Commit transactions.
  RAISE my_exception;
EXCEPTION
  WHEN my_exception THEN
    RAISE NOTICE 'catch!';
    INSERT INTO test VALUES(2);
END;

-- Query data from the table.
SELECT id FROM test;

Execution result:

NOTICE:  catch!
DO
postgres=# select * from t;
 id 
----
  1
  2
(2 rows)