How to Handle Exceptions in Snowflake Scripting: A Step-by-Step Guide
In Snowflake Scripting, exceptions occur when errors arise during code execution, such as attempting to drop a non-existent table. When an exception is raised, subsequent code lines are not executed. To manage these errors, you can define exception handlers that catch specific exceptions within a block or its nested blocks.
You can declare custom exceptions in the `DECLARE` section and raise them using the `RAISE` command. If an exception is raised, Snowflake Scripting searches for an appropriate handler in the current block or in enclosing blocks. If no handler is found, the error is reported to the client executing the block.
Exception handlers are defined using the `EXCEPTION` clause with a `WHEN` clause to handle specific exceptions, including custom and built-in exceptions like `STATEMENT_ERROR` and `EXPRESSION_ERROR`. If no specific handler is provided, a `WHEN OTHER THEN` clause can handle all other exceptions. You can retrieve details about exceptions using built-in variables (`SQLCODE`, `SQLERRM`, and `SQLSTATE`).
In cases where you need to re-raise an exception after handling it, you can use the `RAISE` command without arguments. This allows capturing details about the exception before raising it again. This structure provides a flexible way to manage and respond to errors within Snowflake Scripting blocks.
------------------------
-- Declare a custom exception
DECLARE
table_not_found EXCEPTION (-20001, 'Table does not exist.');
BEGIN
-- Attempt to drop a table
EXECUTE IMMEDIATE 'DROP TABLE non_existent_table';
EXCEPTION
-- Handle the scenario where the table doesn't exist
WHEN statement_error THEN
-- Raise custom exception if the table is not found
IF sqlerrm LIKE '%does not exist%' THEN
RAISE table_not_found;
ELSE
RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
'SQLCODE', sqlcode,
'SQLERRM', sqlerrm,
'SQLSTATE', sqlstate);
END IF;
-- Handle custom exception and log it
WHEN table_not_found THEN
LET error_details := OBJECT_CONSTRUCT('Error type', 'TABLE_NOT_FOUND',
'SQLCODE', sqlcode,
'SQLERRM', sqlerrm,
'SQLSTATE', sqlstate);
INSERT INTO error_log VALUES (error_details);
RETURN error_details;
-- Handle all other exceptions
WHEN OTHER THEN
RETURN OBJECT_CONSTRUCT('Error type', 'OTHER_ERROR',
'SQLCODE', sqlcode,
'SQLERRM', sqlerrm,
'SQLSTATE', sqlstate);
END;
--------------------------------------
Watch video How to Handle Exceptions in Snowflake Scripting: A Step-by-Step Guide online without registration, duration 07 minute 43 second in high hd quality. This video was added by user Data World Solution 05 September 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 2 once and liked it people.