EXCEPTION (Snowflake Scripting) | Snowflake Documentation (original) (raw)

Specifies how to handle exceptions raised in the Snowflake Scripting block.

For more information on exceptions, see Handling exceptions.

See also:

RAISE

Syntax¶

EXCEPTION WHEN [ OR ... ] THEN ; [ ; ... ] [ WHEN ... ] [ WHEN OTHER THEN ] ; [ ; ... ]

Where:

_exceptionname_

An exception name defined in theDECLARE portion of the current block, or in an enclosing block.

_statement_

A statement can be any of the following:

• A single SQL statement (including CALL).
• A control-flow statement (for example, a looping orbranching statement).
• A nested block.

Usage notes¶

• Each block can have its own exception handler.
• Snowflake supports no more than one exception handler per block. However, that handler can catch more than one type of exception by having more than one WHEN clause.
• The exception handler should be at the end of the block. If the block contains statements after the exception handler, those statements are not executed.
• The WHEN OTHER THEN clause catches any exception not yet specified.
• If more than one WHEN clause could match a specific exception, then the first WHEN clause that matches is the one that is executed. The other clauses are not executed.
• An exception handler can handle a specified exception only if that specified exception is inscope.
• If a stored procedure is intended to return a value, then it should return a value from each possible path, including each WHEN clause of the exception handler.
• To use a variable in an exception handler, the variable must be declared in theDECLARE section or passed as an argument to a stored procedure. It can’t be declared in the BEGIN … ENDsection. For more information, see Passing variables to an exception handler.

Examples¶

This example declares, raises, and handles an exception. Note that:

• The exception handler is designed to handle more than one type of exception.
• One of the WHEN clauses in the exception handler contains a single statement, while the other contains ablock.
  DECLARE
  RESULT VARCHAR;
  EXCEPTION_1 EXCEPTION (-20001, 'I caught the expected exception.');
  EXCEPTION_2 EXCEPTION (-20002, 'Not the expected exception!');
  BEGIN
  RESULT := 'If you see this, I did not catch any exception.';
  IF (TRUE) THEN
  RAISE EXCEPTION_1;
  END IF;
  RETURN RESULT;
  EXCEPTION
  WHEN EXCEPTION_2 THEN
  RETURN SQLERRM;
  WHEN EXCEPTION_1 THEN
  RETURN SQLERRM;
  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 DECLARERESULTVARCHAR;EXCEPTION1EXCEPTION(−20001,′Icaughttheexpectedexception.′);EXCEPTION2EXCEPTION(−20002,′Nottheexpectedexception!′);BEGINRESULT:=′Ifyouseethis,Ididnotcatchanyexception.′;IF(TRUE)THENRAISEEXCEPTION1;ENDIF;RETURNRESULT;EXCEPTIONWHENEXCEPTION2THENRETURNSQLERRM;WHENEXCEPTION1THENRETURNSQLERRM;END;DECLARE
  RESULT VARCHAR;
  EXCEPTION_1 EXCEPTION (-20001, 'I caught the expected exception.');
  EXCEPTION_2 EXCEPTION (-20002, 'Not the expected exception!');
  BEGIN
  RESULT := 'If you see this, I did not catch any exception.';
  IF (TRUE) THEN
  RAISE EXCEPTION_1;
  END IF;
  RETURN RESULT;
  EXCEPTION
  WHEN EXCEPTION_2 THEN
  RETURN SQLERRM;
  WHEN EXCEPTION_1 THEN
  RETURN SQLERRM;
  END;DECLARERESULTVARCHAR;EXCEPTION1​EXCEPTION(−20001,′Icaughttheexpectedexception.′);EXCEPTION2​EXCEPTION(−20002,′Nottheexpectedexception!′);BEGINRESULT:=′Ifyouseethis,Ididnotcatchanyexception.′;IF(TRUE)THENRAISEEXCEPTION1​;ENDIF;RETURNRESULT;EXCEPTIONWHENEXCEPTION2​THENRETURNSQLERRM;WHENEXCEPTION1​THENRETURNSQLERRM;END;
  ;

Here is the output of executing the example that raises the exception. This shows that the exception handler caught the exception.

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

anonymous block
I caught the expected exception.
+----------------------------------+

This next example is similar to the previous example, but demonstrates nested blocks, and shows that an inner block can raise an exception declared in either the inner block or in an outer block.

DECLARE e1 EXCEPTION (-20001, 'Exception e1');

BEGIN -- Inner block. DECLARE e2 EXCEPTION (-20002, 'Exception e2'); selector BOOLEAN DEFAULT TRUE; BEGIN IF (selector) THEN RAISE e1; ELSE RAISE e2; END IF; END;

EXCEPTION WHEN e1 THEN RETURN SQLERRM || ' caught in outer block.'; 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 >DECLARE>e1EXCEPTION(−20001,′Exceptione1′);>>BEGIN>−−Innerblock.>DECLARE>e2EXCEPTION(−20002,′Exceptione2′);>selectorBOOLEANDEFAULTTRUE;>BEGIN>IF(selector)THEN>RAISEe1;>ELSE>RAISEe2;>ENDIF;>END;>>EXCEPTION>WHENe1THEN>BEGIN>RETURNSQLERRM∣∣′caughtinouterblock.′;>END;>>END;>> DECLARE > e1 EXCEPTION (-20001, 'Exception e1'); > > BEGIN > -- Inner block. > DECLARE > e2 EXCEPTION (-20002, 'Exception e2'); > selector BOOLEAN DEFAULT TRUE; > BEGIN > IF (selector) THEN > RAISE e1; > ELSE > RAISE e2; > END IF; > END; > > EXCEPTION > WHEN e1 THEN > BEGIN > RETURN SQLERRM || ' caught in outer block.'; > END; > > END; >>DECLARE>e1EXCEPTION(−20001,′Exceptione1′);>>BEGIN>−−Innerblock.>DECLARE>e2EXCEPTION(−20002,′Exceptione2′);>selectorBOOLEANDEFAULTTRUE;>BEGIN>IF(selector)THEN>RAISEe1;>ELSE>RAISEe2;>ENDIF;>END;>>EXCEPTION>WHENe1THEN>BEGIN>RETURNSQLERRM∣∣′caughtinouterblock.′;>END;>>END;> ;

Here is the output of executing the example that raises the exception. This shows that the exception handler caught the exception.

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

anonymous block
Exception e1 caught in outer block.
+-------------------------------------+

This next example is similar to the previous example, but demonstrates nested blocks, each of which has its own exception handler.

DECLARE RESULT VARCHAR; e1 EXCEPTION (-20001, 'Outer exception e1');

BEGIN RESULT := 'No error so far (but there will be).';

DECLARE e1 EXCEPTION (-20101, 'Inner exception e1'); BEGIN RAISE e1; EXCEPTION WHEN e1 THEN RESULT := 'Inner exception raised.'; RETURN RESULT; END;

RETURN RESULT;

EXCEPTION WHEN e1 THEN RESULT := 'Outer exception raised.'; RETURN RESULT;

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 >DECLARE>RESULTVARCHAR;>e1EXCEPTION(−20001,′Outerexceptione1′);>>BEGIN>RESULT:=′Noerrorsofar(buttherewillbe).′;>>DECLARE>e1EXCEPTION(−20101,′Innerexceptione1′);>BEGIN>RAISEe1;>EXCEPTION>WHENe1THEN>RESULT:=′Innerexceptionraised.′;>RETURNRESULT;>END;>>RETURNRESULT;>>EXCEPTION>WHENe1THEN>RESULT:=′Outerexceptionraised.′;>RETURNRESULT;>>END;>> DECLARE > RESULT VARCHAR; > e1 EXCEPTION (-20001, 'Outer exception e1'); > > BEGIN > RESULT := 'No error so far (but there will be).'; > > DECLARE > e1 EXCEPTION (-20101, 'Inner exception e1'); > BEGIN > RAISE e1; > EXCEPTION > WHEN e1 THEN > RESULT := 'Inner exception raised.'; > RETURN RESULT; > END; > > RETURN RESULT; > > EXCEPTION > WHEN e1 THEN > RESULT := 'Outer exception raised.'; > RETURN RESULT; > > END; >>DECLARE>RESULTVARCHAR;>e1EXCEPTION(−20001,′Outerexceptione1′);>>BEGIN>RESULT:=′Noerrorsofar(buttherewillbe).′;>>DECLARE>e1EXCEPTION(−20101,′Innerexceptione1′);>BEGIN>RAISEe1;>EXCEPTION>WHENe1THEN>RESULT:=′Innerexceptionraised.′;>RETURNRESULT;>END;>>RETURNRESULT;>>EXCEPTION>WHENe1THEN>RESULT:=′Outerexceptionraised.′;>RETURNRESULT;>>END;> ;

Note

This example uses the same exception name (e1) in the outer and inner blocks.

This is not recommended.

The example does this to illustrate the scope of exception names. The two exceptions with the name e1 are different exceptions.

The e1 handler in the outer block does not handle the exception e1 that is declared and raised in the inner block.

Here is the output of executing the example that raises the exception. This shows that the inner exception handler ran.

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

anonymous block
Inner exception raised.
+-------------------------+

This example fragment shows:

• How to catch more than one exception in the same clause by using OR.
• How to catch unspecified exceptions by using WHEN OTHER THEN.

  EXCEPTION
  WHEN MY_FIRST_EXCEPTION OR MY_SECOND_EXCEPTION OR MY_THIRD_EXCEPTION THEN
  RETURN 123;
  WHEN MY_FOURTH_EXCEPTION THEN
  RETURN 4;
  WHEN OTHER THEN
  RETURN 99;

The following example shows how to return SQLCODE, SQLERRM (SQL error message), and SQLSTATE when catching an exception:

DECLARE MY_EXCEPTION EXCEPTION (-20001, 'Sample message'); BEGIN RAISE MY_EXCEPTION; EXCEPTION WHEN STATEMENT_ERROR THEN RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR', 'SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE); WHEN EXPRESSION_ERROR THEN RETURN OBJECT_CONSTRUCT('Error type', 'EXPRESSION_ERROR', '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 >DECLARE>MYEXCEPTIONEXCEPTION(−20001,′Samplemessage′);>BEGIN>RAISEMYEXCEPTION;>EXCEPTION>WHENSTATEMENTERRORTHEN>RETURNOBJECTCONSTRUCT(′Errortype′,′STATEMENTERROR′,>′SQLCODE′,SQLCODE,>′SQLERRM′,SQLERRM,>′SQLSTATE′,SQLSTATE);>WHENEXPRESSIONERRORTHEN>RETURNOBJECTCONSTRUCT(′Errortype′,′EXPRESSIONERROR′,>′SQLCODE′,SQLCODE,>′SQLERRM′,SQLERRM,>′SQLSTATE′,SQLSTATE);>WHENOTHERTHEN>RETURNOBJECTCONSTRUCT(′Errortype′,′Othererror′,>′SQLCODE′,SQLCODE,>′SQLERRM′,SQLERRM,>′SQLSTATE′,SQLSTATE);>END;>> DECLARE > MY_EXCEPTION EXCEPTION (-20001, 'Sample message'); > BEGIN > RAISE MY_EXCEPTION; > EXCEPTION > WHEN STATEMENT_ERROR THEN > RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR', > 'SQLCODE', SQLCODE, > 'SQLERRM', SQLERRM, > 'SQLSTATE', SQLSTATE); > WHEN EXPRESSION_ERROR THEN > RETURN OBJECT_CONSTRUCT('Error type', 'EXPRESSION_ERROR', > 'SQLCODE', SQLCODE, > 'SQLERRM', SQLERRM, > 'SQLSTATE', SQLSTATE); > WHEN OTHER THEN > RETURN OBJECT_CONSTRUCT('Error type', 'Other error', > 'SQLCODE', SQLCODE, > 'SQLERRM', SQLERRM, > 'SQLSTATE', SQLSTATE); > END; >>DECLARE>MYE​XCEPTIONEXCEPTION(−20001,′Samplemessage′);>BEGIN>RAISEMYE​XCEPTION;>EXCEPTION>WHENSTATEMENTE​RRORTHEN>RETURNOBJECTC​ONSTRUCT(′Errortype′,′STATEMENTE​RROR′,>′SQLCODE′,SQLCODE,>′SQLERRM′,SQLERRM,>′SQLSTATE′,SQLSTATE);>WHENEXPRESSIONE​RRORTHEN>RETURNOBJECTC​ONSTRUCT(′Errortype′,′EXPRESSIONE​RROR′,>′SQLCODE′,SQLCODE,>′SQLERRM′,SQLERRM,>′SQLSTATE′,SQLSTATE);>WHENOTHERTHEN>RETURNOBJECTC​ONSTRUCT(′Errortype′,′Othererror′,>′SQLCODE′,SQLCODE,>′SQLERRM′,SQLERRM,>′SQLSTATE′,SQLSTATE);>END;> ;

Running this example produces the following output:

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

anonymous block
{
"Error type": "Other error",
"SQLCODE": -20001,
"SQLERRM": "Sample message",
"SQLSTATE": "P0001"
}
+--------------------------------+

This example demonstrates returning a value from each possible path:

declare e1 exception; e2 exception; begin statement_1; ... RETURN x; exception when e1 then begin ... RETURN y; end; when e2 then begin ... RETURN z; end; end;