Handling exceptions | Snowflake Documentation (original) (raw)

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.');

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;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE DECLAREmyexceptionEXCEPTION(−20002,′RaisedMYEXCEPTION.′);BEGINLETcounter:=0;LETshouldraiseexception:=true;IF(shouldraiseexception)THENRAISEmyexception;ENDIF;counter:=counter+1;RETURNcounter;END;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;DECLAREmye​xceptionEXCEPTION(−20002,′RaisedMYE​XCEPTION.′);BEGINLETcounter:=0;LETshouldr​aisee​xception:=true;IF(shouldr​aisee​xception)THENRAISEmye​xception;ENDIF;counter:=counter+1;RETURNcounter;END; ;

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.

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, seeRaising the same exception again in an exception handler.

Handling an exception¶

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

Within the EXCEPTION clause, 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 _exceptionnumber_ shown in the syntax for declaring an exception.
• SQLERRM: This is an error message. For user-defined exceptions, this is the _exceptionmessage_ 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;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE DECLAREmyexceptionEXCEPTION(−20002,′RaisedMYEXCEPTION.′);BEGINLETcounter:=0;LETshouldraiseexception:=true;IF(shouldraiseexception)THENRAISEmyexception;ENDIF;counter:=counter+1;RETURNcounter;EXCEPTIONWHENstatementerrorTHENRETURNOBJECTCONSTRUCT(′Errortype′,′STATEMENTERROR′,′SQLCODE′,sqlcode,′SQLERRM′,sqlerrm,′SQLSTATE′,sqlstate);WHENmyexceptionTHENRETURNOBJECTCONSTRUCT(′Errortype′,′MYEXCEPTION′,′SQLCODE′,sqlcode,′SQLERRM′,sqlerrm,′SQLSTATE′,sqlstate);WHENOTHERTHENRETURNOBJECTCONSTRUCT(′Errortype′,′Othererror′,′SQLCODE′,sqlcode,′SQLERRM′,sqlerrm,′SQLSTATE′,sqlstate);END;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;DECLAREmye​xceptionEXCEPTION(−20002,′RaisedMYE​XCEPTION.′);BEGINLETcounter:=0;LETshouldr​aisee​xception:=true;IF(shouldr​aisee​xception)THENRAISEmye​xception;ENDIF;counter:=counter+1;RETURNcounter;EXCEPTIONWHENstatemente​rrorTHENRETURNOBJECTC​ONSTRUCT(′Errortype′,′STATEMENTE​RROR′,′SQLCODE′,sqlcode,′SQLERRM′,sqlerrm,′SQLSTATE′,sqlstate);WHENmye​xceptionTHENRETURNOBJECTC​ONSTRUCT(′Errortype′,′MYE​XCEPTION′,′SQLCODE′,sqlcode,′SQLERRM′,sqlerrm,′SQLSTATE′,sqlstate);WHENOTHERTHENRETURNOBJECTC​ONSTRUCT(′Errortype′,′Othererror′,′SQLCODE′,sqlcode,′SQLERRM′,sqlerrm,′SQLSTATE′,sqlstate);END; ;

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:

+--------------------------------------+

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.

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.

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;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE BEGINSELECT∗FROMnonexistenttable;EXCEPTIONWHENOTHERTHENLETLINE:=SQLCODE∣∣′:′∣∣SQLERRM;INSERTINTOmyexceptionsVALUES(:line);RAISE;−−Raisethesameexceptionthatyouarehandling.END;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;BEGINSELECT∗FROMnone​xistentt​able;EXCEPTIONWHENOTHERTHENLETLINE:=SQLCODE∣∣′:′∣∣SQLERRM;INSERTINTOmyexceptionsVALUES(:line);RAISE;−−Raisethesameexceptionthatyouarehandling.END;;

Passing variables to an exception handler¶

You can pass variables to an exception handler. The exception handler can execute code based on the value of the variable, and the variable value can be returned in error messages.

For a variable to be passed to a handler in the EXCEPTION section, the variable must be declared in the DECLARE section. If a variable is declared in the BEGIN … END section of the block, it can’t be accessed in the EXCEPTION section.

In addition, if you are writing a Snowflake Scripting stored procedure that accepts arguments, you can use those arguments in an exception handler.

For example, the following anonymous block passes the value of the counter_val variable to the exception handler:

DECLARE counter_val INTEGER DEFAULT 0; my_exception EXCEPTION (-20002, 'My exception text'); BEGIN WHILE (counter_val < 12) DO counter_val := counter_val + 1; IF (counter_val > 10) THEN RAISE my_exception; END IF; END WHILE; RETURN counter_val; EXCEPTION WHEN my_exception THEN RETURN 'Error ' || sqlcode || ': Counter value ' || counter_val || ' exceeds the limit of 10.'; END;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE DECLAREcountervalINTEGERDEFAULT0;myexceptionEXCEPTION(−20002,′Myexceptiontext′);BEGINWHILE(counterval<12)DOcounterval:=counterval+1;IF(counterval>10)THENRAISEmyexception;ENDIF;ENDWHILE;RETURNcounterval;EXCEPTIONWHENmyexceptionTHENRETURN′Error′∣∣sqlcode∣∣′:Countervalue′∣∣counterval∣∣′exceedsthelimitof10.′;END;DECLARE counter_val INTEGER DEFAULT 0; my_exception EXCEPTION (-20002, 'My exception text'); BEGIN WHILE (counter_val < 12) DO counter_val := counter_val + 1; IF (counter_val > 10) THEN RAISE my_exception; END IF; END WHILE; RETURN counter_val; EXCEPTION WHEN my_exception THEN RETURN 'Error ' || sqlcode || ': Counter value ' || counter_val || ' exceeds the limit of 10.'; END;DECLAREcounterv​alINTEGERDEFAULT0;mye​xceptionEXCEPTION(−20002,′Myexceptiontext′);BEGINWHILE(counterv​al<12)DOcounterv​al:=counterv​al+1;IF(counterv​al>10)THENRAISEmye​xception;ENDIF;ENDWHILE;RETURNcounterv​al;EXCEPTIONWHENmye​xceptionTHENRETURN′Error′∣∣sqlcode∣∣′:Countervalue′∣∣counterv​al∣∣′exceedsthelimitof10.′;END; ;

The block returns the following error message:

+---------------------------------------------------------+

The following is an example of a Snowflake Scripting stored procedure that passes in an argument. The example demonstrates how you can use the argument in an exception handler:

CREATE OR REPLACE PROCEDURE exception_test_vars(amount INT) RETURNS TEXT LANGUAGE SQL AS DECLARE my_exception_1 EXCEPTION (-20002, 'Value too low'); my_exception_2 EXCEPTION (-20003, 'Value too high'); BEGIN CREATE OR REPLACE TABLE test_order_insert(units INT); IF (amount < 1) THEN RAISE my_exception_1; ELSEIF (amount > 10) THEN RAISE my_exception_2; ELSE INSERT INTO test_order_insert VALUES (:amount); END IF; RETURN 'Order inserted successfully.'; EXCEPTION WHEN my_exception_1 THEN RETURN 'Error ' || sqlcode || ': Submitted amount ' || amount || ' is too low (1 or greater required).'; WHEN my_exception_2 THEN RETURN 'Error ' || sqlcode || ': Submitted amount ' || amount || ' is too high (exceeds limit of 10).'; END;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

CREATE OR REPLACE PROCEDURE exception_test_vars(amount INT) RETURNS TEXT LANGUAGE SQL AS DECLAREmyexception1EXCEPTION(−20002,′Valuetoolow′);myexception2EXCEPTION(−20003,′Valuetoohigh′);BEGINCREATEORREPLACETABLEtestorderinsert(unitsINT);IF(amount<1)THENRAISEmyexception1;ELSEIF(amount>10)THENRAISEmyexception2;ELSEINSERTINTOtestorderinsertVALUES(:amount);ENDIF;RETURN′Orderinsertedsuccessfully.′;EXCEPTIONWHENmyexception1THENRETURN′Error′∣∣sqlcode∣∣′:Submittedamount′∣∣amount∣∣′istoolow(1orgreaterrequired).′;WHENmyexception2THENRETURN′Error′∣∣sqlcode∣∣′:Submittedamount′∣∣amount∣∣′istoohigh(exceedslimitof10).′;END;DECLARE my_exception_1 EXCEPTION (-20002, 'Value too low'); my_exception_2 EXCEPTION (-20003, 'Value too high'); BEGIN CREATE OR REPLACE TABLE test_order_insert(units INT); IF (amount < 1) THEN RAISE my_exception_1; ELSEIF (amount > 10) THEN RAISE my_exception_2; ELSE INSERT INTO test_order_insert VALUES (:amount); END IF; RETURN 'Order inserted successfully.'; EXCEPTION WHEN my_exception_1 THEN RETURN 'Error ' || sqlcode || ': Submitted amount ' || amount || ' is too low (1 or greater required).'; WHEN my_exception_2 THEN RETURN 'Error ' || sqlcode || ': Submitted amount ' || amount || ' is too high (exceeds limit of 10).'; END;DECLAREmye​xception1​EXCEPTION(−20002,′Valuetoolow′);mye​xception2​EXCEPTION(−20003,′Valuetoohigh′);BEGINCREATEORREPLACETABLEtesto​rderi​nsert(unitsINT);IF(amount<1)THENRAISEmye​xception1​;ELSEIF(amount>10)THENRAISEmye​xception2​;ELSEINSERTINTOtesto​rderi​nsertVALUES(:amount);ENDIF;RETURN′Orderinsertedsuccessfully.′;EXCEPTIONWHENmye​xception1​THENRETURN′Error′∣∣sqlcode∣∣′:Submittedamount′∣∣amount∣∣′istoolow(1orgreaterrequired).′;WHENmye​xception2​THENRETURN′Error′∣∣sqlcode∣∣′:Submittedamount′∣∣amount∣∣′istoohigh(exceedslimitof10).′;END; ;

The following calls to the stored procedure show the expected output:

CALL exception_test_vars(7);

+------------------------------+

CALL exception_test_vars(-3);

+-----------------------------------------------------------------------+

CALL exception_test_vars(20);

+----------------------------------------------------------------------+