During this post we will discuss about the Exception handling in snowflake via sql scripting. Snowflake Scripting raises an exception if an error occurs while executing a statement. When an exception is raised in a Snowflake Scripting block, Snowflake Scripting attempts to find a handler for that exception. If there is no handler for the exception in the current block or in any enclosing blocks, execution of the block stops and code reports error.
We know Oracle has great list of predefined exceptions, which are raised when any database rule is violated. Some of the commonly used exceptions are:
- NO_DATA_FOUND : when a select into statement returns no rows
- TOO_MANY_ROWS : when a SELECT INTO statement returns more than one row.
- ZERO_DIVIDE: when an attempt is made to divide a number by zero.
- INVALID_NUMBER : when the conversion of a character string into a number fails
In addition to it ,Oracle supports User defined Exceptions as well, where programmer build their own exceptions and use them for handling errors
We will try to implement above all predefined exceptions along with some user defined exceptions in Snowflake scripting.
We have implemented below stored procedure to implement Exception handling.
Execute the Procedure with Input and observe the output: