Handling Exceptions

In a Snowflake Scripting block, you can raise an exception if an error occurs. You can also handle exceptions that occur in your Snowflake Scripting code.

Introduction

Snowflake Scripting raises an exception if an error occurs while executing a statement (e.g. if a statement attempts to DROP a table that doesn’t exist).

An exception prevents the next lines of code from executing.

In a Snowflake Scripting block, you can write exception handlers that catch specific types of exceptions declared in that block and in blocks nested inside that block.

In addition, for errors that can occur in your code, you can define your own exceptions that you can raise when errors occur.

When an exception is raised in a Snowflake Scripting block (either by your code or by a statement that fails to execute), Snowflake Scripting attempts to find a handler for that exception:

  • If the block in which the exception occurred has a handler for that exception, then execution resumes at the beginning of that exception handler.

  • If the block does not have its own exception handler, then the exception can be caught by the enclosing block.

    If the exception occurs more than one layer deep, then the exception is sent upward one layer at a time until either:

    • A layer with an appropriate exception handler handles the exception.

    • The outermost layer is reached, in which case an error occurs.

  • If there is no handler for the exception in the current block or in any enclosing blocks, execution of the block stops, and the client that submits the block for execution (e.g. the web interface, SnowSQL, etc.) reports this as a Snowflake error.

An exception handler can contain its own exception handler in case an exception occurs while handling another exception.

Declaring an Exception

You can declare your own exception in the DECLARE section of the block. Use the syntax described in Exception Declaration Syntax. For example:

DECLARE
  my_exception EXCEPTION (-20002, 'Raised MY_EXCEPTION.');
Copy

Raising a Declared Exception

To raise an exception, execute the RAISE command. For example:

DECLARE
  my_exception EXCEPTION (-20002, 'Raised MY_EXCEPTION.');
BEGIN
  LET counter := 0;
  LET should_raise_exception := true;
  IF (should_raise_exception) THEN
    RAISE my_exception;
  END IF;
  counter := counter + 1;
  RETURN counter;
END;
Copy

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

EXECUTE IMMEDIATE $$
DECLARE
  my_exception EXCEPTION (-20002, 'Raised MY_EXCEPTION.');
BEGIN
  LET counter := 0;
  LET should_raise_exception := true;
  IF (should_raise_exception) THEN
    RAISE my_exception;
  END IF;
  counter := counter + 1;
  RETURN counter;
END;
$$
;
Copy

Execution stops at the point when the exception is raised. (In the example, counter is never incremented and returned.)

The client that submits this block for execution (e.g. Snowsight) reports an error and indicates that the exception was not caught:

-20002 (P0001): Uncaught exception of type 'MY_EXCEPTION' on line 8 at position 4 : Raised MY_EXCEPTION.
Copy

If you want to add code to handle any exceptions that you raise (as well as exceptions raised when statements fail to execute), you can write exception handlers. See Handling an Exception.

Note

In an exception handler, if you need to raise the same exception again, see Raising the Same Exception Again in an Exception Handler.

Handling an Exception

You can explicitly handle an exception by catching it with an EXCEPTION clause, or you can allow the block to pass the exception on to the enclosing block.

Within the EXCEPTION clause, you use a WHEN clause to handle an exception by name. You can handle exceptions that you declare as well as built-in exceptions. Currently, Snowflake provides the following built-in exceptions:

  • STATEMENT_ERROR: This exception indicates an error while executing a statement. For example, if you attempt to drop a table that does not exist, this exception is raised.

  • EXPRESSION_ERROR: This exception indicates an error related to an expression. For example, if you create an expression that evaluates to a VARCHAR, and you attempt to assign the value of the expression to a FLOAT, this error is raised.

When an exception occurs, you can get information about the exception by reading the following three built-in variables:

  • SQLCODE: This is a 5-digit signed integer. For user-defined exceptions, this is the exception_number shown in the syntax for declaring an exception.

  • SQLERRM: This is an error message. For user-defined exceptions, this is the exception_message shown in the syntax for declaring an exception.

  • SQLSTATE: This is a 5-character code modeled on the ANSI SQL standard SQLSTATE . Snowflake uses additional values beyond those in the ANSI SQL standard.

To handle all other exceptions that do not have a WHEN clause, use a WHEN OTHER THEN clause.

For example:

DECLARE
  my_exception EXCEPTION (-20002, 'Raised MY_EXCEPTION.');
BEGIN
  LET counter := 0;
  LET should_raise_exception := true;
  IF (should_raise_exception) THEN
    RAISE my_exception;
  END IF;
  counter := counter + 1;
  RETURN counter;
EXCEPTION
  WHEN statement_error THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  WHEN my_exception THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'MY_EXCEPTION',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  WHEN OTHER THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
END;
Copy

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

EXECUTE IMMEDIATE $$
DECLARE
  my_exception EXCEPTION (-20002, 'Raised MY_EXCEPTION.');
BEGIN
  LET counter := 0;
  LET should_raise_exception := true;
  IF (should_raise_exception) THEN
    RAISE my_exception;
  END IF;
  counter := counter + 1;
  RETURN counter;
EXCEPTION
  WHEN statement_error THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  WHEN my_exception THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'MY_EXCEPTION',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  WHEN OTHER THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
END;
$$
;
Copy

This example handles each type of exception by calling OBJECT_CONSTRUCT to construct and return an object that contains the details about the exception. The example produces the following output:

+--------------------------------------+
| anonymous block                      |
|--------------------------------------|
| {                                    |
|   "Error type": "MY_EXCEPTION",      |
|   "SQLCODE": -20002,                 |
|   "SQLERRM": "Raised MY_EXCEPTION.", |
|   "SQLSTATE": "P0001"                |
| }                                    |
+--------------------------------------+
Copy

In rare cases, you might want to explicitly handle an exception by doing nothing. This allows you to continue, rather than abort, when the exception occurs. For more information, see the NULL command.

Note that if you do not set up a handler for an exception, the client that submits the block for execution (e.g. the web interface) reports an error (as explained in Raising a Declared Exception).

-20002 (P0001): Uncaught exception of type 'MY_EXCEPTION' on line 8 at position 4 : Raised MY_EXCEPTION.
Copy

Note

If you need to raise the same exception again, see Raising the Same Exception Again in an Exception Handler.

Raising the Same Exception Again in an Exception Handler

In some cases, you might need to raise the same exception that you caught in your exception handler. In these cases, execute the RAISE command without specifying any arguments.

For example, suppose that during exception handling, you need to capture some details about the exception before raising the same exception again. After capturing the details, execute the RAISE command:

BEGIN
  SELECT * FROM non_existent_table;
EXCEPTION
  WHEN OTHER THEN
    LET LINE := SQLCODE || ': ' || SQLERRM;
    INSERT INTO myexceptions VALUES (:line);
    RAISE; -- Raise the same exception that you are handling.
END;
Copy

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

EXECUTE IMMEDIATE $$
BEGIN
  SELECT * FROM non_existent_table;
EXCEPTION
  WHEN OTHER THEN
    LET LINE := SQLCODE || ': ' || SQLERRM;
    INSERT INTO myexceptions VALUES (:line);
    RAISE; -- Raise the same exception that you are handling.
END;
$$;
Copy