Appearance

Guides>Scripting Guide>Exceptions

Exceptions ​

User-defined and built-in exceptions help handle possible inconsistencies of data and script blocks.

Overview ​

An exception is a special data type in Scripting. Exceptions could be declared, thrown and handled.

User-specified handlers catch certain types of exceptions. Exceptions can be declared in a block and then used to catch that type of exception in the nested blocks of that block as well.

When an exception is raised, the system tries to find a handler for this exception:

  • First in the block in which the exception occurred.
  • Then in the enclosing block if this block does not have its own exception handler.

Execution continues at the beginning of this exception handler.

If the exception occurs deeper than the "first level", the exception is sent up level by level until:

  • either an appropriate exception handler handles the exception
  • or the outermost layer is reached. In this case an error is raised.

Therefore, if no suitable handler is found, the script execution will stop and an error will be reported.

An exception handler can contain nested exception handlers in case an exception occurs while processing another exception.

Exception declaration ​

User defined exceptions can be declared in the DECLARE section of the block.

sql
...
DECLARE
  my_exception EXCEPTION (-20001, 'I have triggered this exception at your request.');
...

See Exception declaration syntax for syntax information and Example 3 | EXCEPTION Declaration as one of the examples.

Raising an exception ​

With RAISE command an exception is thrown

sql
DECLARE
  my_exception EXCEPTION (-20001, 'I have triggered this exception at your request.');
BEGIN
  LET count INTEGER := 0;
  IF (count = 0) THEN
    RAISE my_exception;
  END IF;
  count := count + 1;
  RETURN count;
END;

At this point the script stops, the exception is raised and error message is returned.

txt
I have triggered this exception at your request.
 at my_folder.my_script_name (6:5)
 at my_folder.my_script_name (1:1)

Below you find out, how to to raise the same exception again and write exception handlers.

Define exception handlers ​

With an EXCEPTION clause (also called exception handler), exceptions can be handled explicitly or passed on to the parent blocks.

Within an EXCEPTION clause, exceptions can also be handled by name using the WHEN clause. This can be done for both user-declared and built-in exceptions.

Following system variables contains information about raised exception:

  • SQLCODE: Exception 'id', which is represented as a 5-digit signed integer and can be specified by user in DECLARE section for user defined exceptions.
  • SQLERRM: An error message. The message can be specified by user in DECLARE section for user defined exceptions.

With WHEN OTHER THEN you can handle all other exceptions that do not have a WHEN clause.

For example:

sql
DECLARE
  MY_EXCEPTION EXCEPTION (-20001, 'I have triggered this exception at your request.');
BEGIN
  LET NUMERATOR INTEGER := 0;
  IF (NUMERATOR = 0) THEN
    RAISE MY_EXCEPTION;
  END IF;
  NUMERATOR := NUMERATOR + 1;
  RETURN NUMERATOR;
EXCEPTION
  WHEN MY_EXCEPTION THEN                                -- handler for user declared exception
    SELECT  'Excp.Type: '|| 'MY_EXCEPTION' ||             
            '; Excp.Code: ' || :SQLCODE || 
            '; Excp.Message: ' || :SQLERRM AS MESSAGE;
  WHEN OTHER THEN                                       -- handler for all other exception
    SELECT  'Excp.Type: '|| 'Unknown exception' ||             
            '; Excp.Code: ' || :SQLCODE || 
            '; Excp.Message: ' || :SQLERRM AS MESSAGE;
END;
txt
+-----------------------------------------------------------------------------------------------------------+
| MESSAGE                                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| Excp.Type: MY_EXCEPTION; Excp.Code: 20001; Excp.Message: I have triggered this exception at your request. |
+-----------------------------------------------------------------------------------------------------------+
  !   If an exception is declared in the DECLARE section, but no handler for this exception is found in the EXCEPTION section, an error is returned:
sql
DECLARE
  MY_EXCEPTION EXCEPTION (-20001, 'I have triggered this exception at your request.');
BEGIN
  LET NUMERATOR INTEGER := 0;
  IF (NUMERATOR = 0) THEN
    RAISE MY_EXCEPTION;
  END IF;
  NUMERATOR := NUMERATOR + 1;
  RETURN NUMERATOR;
END;

And

sql
DECLARE
  MY_EXCEPTION EXCEPTION (-20001, 'I have triggered this exception at your request.');
  SECOND_EXCEPTION EXCEPTION (-20002, 'I have triggered this exception at your request.');
BEGIN
  LET NUMERATOR INTEGER := 0;
  IF (NUMERATOR = 0) THEN
    RAISE MY_EXCEPTION;
  END IF;
  NUMERATOR := NUMERATOR + 1;
  RETURN NUMERATOR;
EXCEPTION
  WHEN SECOND_EXCEPTION THEN                             -- handler for SECOND_EXCEPTION
    RETURN  'Excp.Type: ' || 'SECOND_EXCEPTION' || 
            '; Excp.Code: ' || SQLCODE || 
            '; Excp.Message: ' || SQLERRM;
END;

Both have the following output:

txt
I have triggered this exception at your request.
 at my_folder.my_script_name (6:5)
 at my_folder.my_script_name (1:1)

Ignore exceptions with NULL command ​

The command NULL handles an exception by explicitely "doing nothing": in this case, the execution of the code will be continued without stop.

In this example, an "Unknown Exception" exception is thrown because a string value "x" is added to a numeric variable NUMERATOR. The error second exception handler ignores this exception:

sql
DECLARE
  MY_EXCEPTION EXCEPTION (-20001, 'I have triggered this exception at your request.');
BEGIN
  LET NUMERATOR INTEGER := 0;
  NUMERATOR := NUMERATOR + 'x';
  RETURN NUMERATOR;
EXCEPTION
  WHEN MY_EXCEPTION THEN                                -- handler for user declared exception
    RETURN  'Excp.Type: '|| 'MY_EXCEPTION' ||             
            '; Excp.Code: ' || SQLCODE || 
            '; Excp.Message: ' || SQLERRM;
  WHEN OTHER THEN                                       -- handler for all other exception
        NULL;  -- do nothing
  END;

Note

This can lead to unexpected results.

See NULL for more information.

Re-Raising of the caught exception ​

Sometimes it is helpful not only to trigger steps defined in a handler for an exception, but also to trigger the exception itself after the steps.

In this case, the "RAISE" command can be used without arguments.

In this example, we handle the exception "Object 'MY_EXCEPTIONS' already exists" by writing the exception message to the table "MY_EXCEPTIONS".

sql
CREATE TABLE IF NOT EXISTS my_folder.MY_EXCEPTIONS (CAPTURED STRING);
BEGIN
  CREATE TABLE  my_folder.MY_EXCEPTIONS (CAPTURED STRING);
EXCEPTION
  WHEN OTHER THEN
    LET LINE := SQLCODE || ': ' || SQLERRM;
    INSERT INTO my_folder.MY_EXCEPTIONS VALUES (:LINE);
END;
  !   No exception is raised:
txt
+-----------------+
| anonymous block |
+-----------------+
| null            |
+-----------------+

And:

sql
select * from my_exceptions;
txt
+-----------------------------------------------------------------------------------------------------------------------------------+
| CAPTURED                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------+
| undefined: Failed to create table 'my_folder.MY_EXCEPTIONS': There already exists a table at this path (my_folder.MY_EXCEPTIONS)! |
+-----------------------------------------------------------------------------------------------------------------------------------+

Now, in addition to storing the error message in the table, we can raise this exception using the RAISE command:

sql
CREATE TABLE IF NOT EXISTS my_folder.MY_EXCEPTIONS (CAPTURED STRING);
BEGIN
  CREATE TABLE  my_folder.MY_EXCEPTIONS (CAPTURED STRING);
EXCEPTION
  WHEN OTHER THEN
    LET LINE := SQLCODE || ': ' || SQLERRM;
    INSERT INTO my_folder.MY_EXCEPTIONS VALUES (:LINE);
    RAISE;
END;

Exception is raised and web ui shows message:

txt

RuntimeError: Failed to create table 'my_folder.MY_EXCEPTIONS': There already exists a table at this path (my_folder.MY_EXCEPTIONS)!
 at my_folder.my_script_name (3:3)
 at my_folder.my_script_name (2:1)

And:

sql
SELECT * FROM MY_EXCEPTIONS;
txt
+-----------------------------------------------------------------------------------------------------------------------------------+
| CAPTURED                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------+
| undefined: Failed to create table 'my_folder.MY_EXCEPTIONS': There already exists a table at this path (my_folder.MY_EXCEPTIONS)! |
+-----------------------------------------------------------------------------------------------------------------------------------+

Use variables in an exception handler ​

An exception handler can execute code based on the values ​​of a passed variable, or the variable value can be returned in an error message.

Such a variable must be declared in the DECLARE section of the code.

Variables declared in the BEGIN ... END section of the code cannot be used in the EXCEPTION section.

Stored procedure arguments can also be used in an exception handler.

In this example, variable NUMERATOR is used in EXCEPTION section:

sql
DECLARE
  NUMERATOR INTEGER;
  MY_EXCEPTION EXCEPTION (-20001, 'I have triggered this exception at your request.');
BEGIN
  NUMERATOR := 0;
  IF (NUMERATOR = 0) THEN
    RAISE MY_EXCEPTION;
  END IF;
  NUMERATOR := NUMERATOR + 1;
  RETURN NUMERATOR;
EXCEPTION
  WHEN MY_EXCEPTION THEN                                -- handler for user declared exception
    RETURN  'Excp.Type: '|| 'MY_EXCEPTION' || '; NUMERATOR = '|| NUMERATOR::STRING;             
END;
txt
Excp.Type: MY_EXCEPTION; NUMERATOR = 0

As you can see, NUMERATOR is still equal to 0 because an exception was raised before the line of code: NUMERATOR := NUMERATOR + 1;.

In the following example we use argument of the stored procedure in an exception handler, by referencing the argument DIVISOR in the SELECT statement. All arguments of a procedure are accessible in the exception handlers of the procedure:

sql
CREATE OR REPLACE PROCEDURE my_folder.TEST_DIVISOR(DIVISOR INTEGER)
  RETURNS STRING
AS
DECLARE
  DIVISION_EXCEPTION EXCEPTION (20001, ' Exception: Divisor is 0! ');
  QUOTIENT DECIMAL;
BEGIN
  IF (DIVISOR = 0) THEN
    RAISE DIVISION_EXCEPTION;
  END IF;
  QUOTIENT := 10 / DIVISOR;
  RETURN 'Quotient is: ' || QUOTIENT::STRING;
EXCEPTION
  WHEN DIVISION_EXCEPTION THEN
    SELECT 'Message ' || :SQLCODE || '; Divisor = ' || :DIVISOR AS MESSAGE;
END;
CALL my_folder.TEST_DIVISOR(0);
txt
+----------------------------+
| MESSSAGE                   |
+----------------------------+
| Message 20001; Divisor = 0 |
+----------------------------+
© 2025 MetaKraftwerk GmbH. All Rights Reserved.