In this post we are going to cover exceptions and RAISE in Snowflake Scripting.
An exception is what a block does when something goes wrong. A handler is the piece you write to catch it. RAISE is how you throw one on purpose, either one of Snowflake's own or one you declared yourself.
Episode 147 ended on a loop that failed half way through and kept every row it had already written. So the question for this post is whether catching the failure changes that. It does not, and that is the part worth staying for.
We will start with the smallest handler there is and build it up step by step. Every query below was run on a real Snowflake account, and every result and every error you see is what it gave back.
Episode 149 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
- Let's create some sample data
- Let's start with the smallest handler
- What happens if we put the handler first?
- Now let's catch an error we did not raise ourselves
- Can we catch it by the name in the error message?
- Now let's ask the handler what it caught
- Now let's give the handler three branches
- Let's declare an exception of our own
- What does the caller see if nobody catches it?
- Can a handler raise something itself?
- Now let's put a block inside a block
- What happens when the handler itself fails?
- Two names that are only checked when you call it
- Now the part this post is really about
- So where does the rollback go?
- Can a handler catch an error from a function?
- One last thing: TRY_CAST is the smaller answer
- And let's leave the schema the way we found it
Let's create some sample data
First let's see where the session is pointing, so we know what everything below is running against.
SELECT CURRENT_DATABASE() AS THE_DATABASE,
CURRENT_SCHEMA() AS THE_SCHEMA,
CURRENT_TRANSACTION() AS ANY_OPEN_TRANSACTION,
CURRENT_VERSION() AS THE_VERSION;| THE_DATABASE | THE_SCHEMA | ANY_OPEN_TRANSACTION | THE_VERSION |
|---|---|---|---|
| DEMO_DB | PUBLIC | NULL | 10.35.101 |
We are in DEMO_DB and the PUBLIC schema, and there is no transaction open. Keep that last column in mind. We come back to it near the end.
Now one small table. The handlers in this post write rows into it, and later on we go and look at what is left in there.
CREATE OR REPLACE TABLE TRACE_T149 (
STEP INT,
NOTE VARCHAR(80)
);| status |
|---|
| Table TRACE_T149 successfully created. |
Two columns, a step number and a note. That is all we need.
Let's start with the smallest handler
The smallest handler we can write is one that raises an error on purpose and then catches it.
Have a look at where the handler goes. It is after the statements, at the end of the block, just before END.
CREATE OR REPLACE PROCEDURE CAUGHT_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
RAISE STATEMENT_ERROR;
RETURN 'never reached';
EXCEPTION
WHEN STATEMENT_ERROR THEN
RETURN 'the handler ran';
END;
$$;| status |
|---|
| Function CAUGHT_T149 successfully created. |
Now let's call it.
CALL CAUGHT_T149();| CAUGHT_T149 |
|---|
| the handler ran |
The handler ran. That is the sentence from the handler, not the one from the body, so the RAISE did fire and the RETURN 'never reached' under it never did.
And the call itself succeeded. We got a result, not an error. That is the first thing to notice about a handler, and the whole post hangs off it.
What happens if we put the handler first?
If you have written try and catch in another language, the handler going last may feel backwards. Let's try putting it first and see what Snowflake says.
CREATE OR REPLACE PROCEDURE HANDLERFIRST_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
EXCEPTION
WHEN OTHER THEN
RETURN 'the handler ran';
RAISE STATEMENT_ERROR;
END;
$$;001003 (42000): SQL compilation error: syntax error line 3 at position 0 unexpected 'EXCEPTION'.
001003, a syntax error, and it points straight at the word EXCEPTION. The procedure was never created.
So the order is fixed. Statements first, handler last. There is no try block to put anything inside.
Now let's catch an error we did not raise ourselves
So far we raised the error ourselves and caught it by name. Real failures do not arrive with a name we chose. Let's divide by zero instead and catch it with WHEN OTHER, which is the catch-all.
CREATE OR REPLACE PROCEDURE CATCHALL_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
N NUMBER;
BEGIN
N := 1 / 0;
RETURN 'never reached';
EXCEPTION
WHEN OTHER THEN
RETURN 'caught by OTHER: ' || SQLERRM;
END;
$$;| status |
|---|
| Function CATCHALL_T149 successfully created. |
CALL CATCHALL_T149();| CATCHALL_T149 |
|---|
| caught by OTHER: Division by zero |
Caught, and SQLERRM has the text of the real error in it. We did not have to know what was going to go wrong.
And now the spelling you would use in Oracle
In Oracle and in PL/pgSQL the catch-all is WHEN OTHERS, with an S. Let's write it that way and see.
CREATE OR REPLACE PROCEDURE CATCHALLS_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
N NUMBER;
BEGIN
N := 1 / 0;
RETURN 'never reached';
EXCEPTION
WHEN OTHERS THEN
RETURN 'caught by OTHERS: ' || SQLERRM;
END;
$$;| status |
|---|
| Function CATCHALLS_T149 successfully created. |
That was accepted. Snowflake did not complain at all. Now let's call it.
CALL CATCHALLS_T149();000904 (42000): SQL compilation error: error line 8 at position 9 invalid identifier 'OTHERS'
000904, invalid identifier 'OTHERS'. The plural does not exist here. Snowflake read it as the name of a condition, looked for one called OTHERS, and did not find it.
Read that pair again, because this is the shape of most of the mistakes in this post. The procedure was created without a word of complaint, and the refusal only arrived when we called it. A handler branch name is not resolved at create time.
Can we catch it by the name in the error message?
The division by zero printed its own text. Snowflake calls that kind of failure an EXPRESSION_ERROR. So can we name it in the branch instead of using the catch-all?
CREATE OR REPLACE PROCEDURE CATCHEXPR_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
N NUMBER;
BEGIN
N := 1 / 0;
RETURN 'never reached';
EXCEPTION
WHEN EXPRESSION_ERROR THEN
RETURN 'caught as EXPRESSION_ERROR: ' || SQLERRM;
END;
$$;| status |
|---|
| Function CATCHEXPR_T149 successfully created. |
CALL CATCHEXPR_T149();| CATCHEXPR_T149 |
|---|
| caught as EXPRESSION_ERROR: Division by zero |
We can. So the type name that turns up in Snowflake's own error text is the word you write in the branch. That is worth knowing, because it means the error message is also telling you how to catch it.
Now let's ask the handler what it caught
A handler can read three things about the failure: SQLCODE, SQLSTATE and SQLERRM. Let's build one string out of all three and return it.
CREATE OR REPLACE PROCEDURE WHATWAS_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
RAISE STATEMENT_ERROR;
RETURN 'never reached';
EXCEPTION
WHEN STATEMENT_ERROR THEN
RETURN 'code=' || SQLCODE || ' state=' || SQLSTATE || ' msg=' || SQLERRM;
END;
$$;| status |
|---|
| Function WHATWAS_T149 successfully created. |
CALL WHATWAS_T149();| WHATWAS_T149 |
|---|
| NULL |
NULL. The whole string came back null.
Concatenating a null in SQL gives you a null, so at least one of those three is null here. This grid cannot tell us which one it was, and we are not going to guess at it.
What it does prove is worth having. A handler that builds its message by sticking values together can hand you a null instead of a message, and you will not get an error telling you so.
And we already know SQLERRM works for a real error, because the two division-by-zero handlers above both printed its text. So the gap is specific to a bare RAISE that carries no message of its own.
Now let's give the handler three branches
A handler can have more than one branch. Let's write one with STATEMENT_ERROR, EXPRESSION_ERROR and a catch-all, and a parameter that decides what goes wrong.
CREATE OR REPLACE PROCEDURE THREEWAY_T149(WHICH VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
N NUMBER;
BEGIN
IF (:WHICH = 'raise') THEN
RAISE STATEMENT_ERROR;
ELSEIF (:WHICH = 'divide') THEN
N := 1 / 0;
ELSE
RETURN 'nothing went wrong';
END IF;
RETURN 'never reached';
EXCEPTION
WHEN STATEMENT_ERROR THEN
RETURN 'the statement branch ran';
WHEN EXPRESSION_ERROR THEN
RETURN 'the expression branch ran';
WHEN OTHER THEN
RETURN 'the catch-all ran: ' || SQLERRM;
END;
$$;| status |
|---|
| Function THREEWAY_T149 successfully created. |
Let's raise a STATEMENT_ERROR first.
CALL THREEWAY_T149('raise');| THREEWAY_T149 |
|---|
| the statement branch ran |
Now let's divide by zero instead.
CALL THREEWAY_T149('divide');| THREEWAY_T149 |
|---|
| the expression branch ran |
Each one found its own branch, and the catch-all sat there unused both times.
And when nothing goes wrong at all?
CALL THREEWAY_T149('fine');| THREEWAY_T149 |
|---|
| nothing went wrong |
That is the body's own RETURN. No branch fired. A handler costs nothing when the block works.
Let's declare an exception of our own
We do not have to use Snowflake's names. We can declare an exception ourselves, with a code and a message, and raise that.
CREATE OR REPLACE PROCEDURE MYOWN_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
TOO_SMALL EXCEPTION (-20001, 'that amount is too small');
BEGIN
RAISE TOO_SMALL;
RETURN 'never reached';
EXCEPTION
WHEN TOO_SMALL THEN
RETURN 'caught my own: code=' || SQLCODE || ' msg=' || SQLERRM;
END;
$$;| status |
|---|
| Function MYOWN_T149 successfully created. |
CALL MYOWN_T149();| MYOWN_T149 |
|---|
| caught my own: code=-20001 msg=that amount is too small |
Both the code and the message carried through to the handler. SQLCODE gave us back the number we chose and SQLERRM gave us back our own sentence.
How short can that declaration be?
Let's drop the code and the message and declare just a name.
CREATE OR REPLACE PROCEDURE MYOWNBARE_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
PLAIN_ONE EXCEPTION;
BEGIN
RAISE PLAIN_ONE;
RETURN 'never reached';
EXCEPTION
WHEN PLAIN_ONE THEN
RETURN 'caught the bare one: code=' || SQLCODE;
END;
$$;| status |
|---|
| Function MYOWNBARE_T149 successfully created. |
CALL MYOWNBARE_T149();| MYOWNBARE_T149 |
|---|
| caught the bare one: code=-20000 |
That works too, and SQLCODE is -20000. That is the default you get when you do not pick one.
What does the caller see if nobody catches it?
Let's declare one, raise it, and leave the handler out altogether.
CREATE OR REPLACE PROCEDURE MYOWNLOOSE_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
TOO_SMALL EXCEPTION (-20002, 'nobody is going to catch this');
BEGIN
RAISE TOO_SMALL;
RETURN 'never reached';
END;
$$;| status |
|---|
| Function MYOWNLOOSE_T149 successfully created. |
CALL MYOWNLOOSE_T149();-20002 (P0001): Uncaught exception of type 'TOO_SMALL' on line 5 at position 4 : nobody is going to catch this
The type name we chose and the message we wrote both reached the caller, along with the code. That is worth remembering when you are naming one. Pick a name somebody reading the error will understand.
Can a handler raise something itself?
Yes, and there are two ways to do it. The first is a bare RAISE with no argument, inside the handler.
CREATE OR REPLACE PROCEDURE RERAISE_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
RAISE STATEMENT_ERROR;
RETURN 'never reached';
EXCEPTION
WHEN STATEMENT_ERROR THEN
RAISE;
END;
$$;| status |
|---|
| Function RERAISE_T149 successfully created. |
CALL RERAISE_T149();-20000 (P0001): Uncaught exception of type 'STATEMENT_ERROR' on line 3 at position 4
It re-raised the one it had just caught. The caller gets the STATEMENT_ERROR after all. So a bare RAISE inside a handler means “the one I am handling”.
And the other way: raise a different one
Now let's catch the STATEMENT_ERROR and raise our own exception in its place.
CREATE OR REPLACE PROCEDURE RAISEOTHER_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
WRAPPED EXCEPTION (-20003, 'wrapped by the handler');
BEGIN
RAISE STATEMENT_ERROR;
RETURN 'never reached';
EXCEPTION
WHEN STATEMENT_ERROR THEN
RAISE WRAPPED;
END;
$$;| status |
|---|
| Function RAISEOTHER_T149 successfully created. |
CALL RAISEOTHER_T149();-20003 (P0001): Uncaught exception of type 'WRAPPED' on line 9 at position 8 : wrapped by the handler
Only the new one. The new exception replaced the old one completely, and the caller never hears what actually went wrong.
That is sometimes exactly what you want, because you do not always want internal detail leaving a procedure. Just know that you are throwing the original away when you do it.
Now let's put a block inside a block
Blocks nest. So let's write an inner block with no handler, inside an outer block that has one, and see which one catches the failure.
CREATE OR REPLACE PROCEDURE NESTED_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
BEGIN
RAISE STATEMENT_ERROR;
END;
RETURN 'never reached';
EXCEPTION
WHEN STATEMENT_ERROR THEN
RETURN 'the outer handler caught it';
END;
$$;| status |
|---|
| Function NESTED_T149 successfully created. |
CALL NESTED_T149();| NESTED_T149 |
|---|
| the outer handler caught it |
The outer handler caught it. A failure climbs out of the block it happened in and keeps going until it finds a handler.
Now let's give the inner block its own handler
CREATE OR REPLACE PROCEDURE NESTEDOWN_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
OUT_TEXT VARCHAR DEFAULT '';
BEGIN
BEGIN
RAISE STATEMENT_ERROR;
EXCEPTION
WHEN STATEMENT_ERROR THEN
OUT_TEXT := 'the inner handler ran';
END;
RETURN :OUT_TEXT || ', and the outer block carried on';
EXCEPTION
WHEN OTHER THEN
RETURN 'the outer handler ran, which it should not have';
END;
$$;| status |
|---|
| Function NESTEDOWN_T149 successfully created. |
CALL NESTEDOWN_T149();| NESTEDOWN_T149 |
|---|
| the inner handler ran, and the outer block carried on |
The inner handler ran and the outer block carried straight on. The outer handler never fired, and the statements after the inner block still ran.
So the nearest handler wins, and once it has dealt with the failure the block above it does not know anything happened.
What happens when the handler itself fails?
This is the awkward one. Let's write a handler that divides by zero while it is handling a STATEMENT_ERROR.
CREATE OR REPLACE PROCEDURE BADHANDLER_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
N NUMBER;
BEGIN
RAISE STATEMENT_ERROR;
RETURN 'never reached';
EXCEPTION
WHEN STATEMENT_ERROR THEN
N := 1 / 0;
RETURN 'never reached either';
END;
$$;| status |
|---|
| Function BADHANDLER_T149 successfully created. |
CALL BADHANDLER_T149();100051 (22012): Uncaught exception of type 'EXPRESSION_ERROR' on line 9 at position 13 : Division by zero
Read which error that is. 100051, an EXPRESSION_ERROR, division by zero. That is the new one, from inside the handler.
The STATEMENT_ERROR it was handling is gone. The caller never hears about it, and there is nothing in this message to suggest it ever existed.
So keep the work in a handler small and keep it safe. A handler is the last thing standing between your block and the caller.
Two names that are only checked when you call it
We saw this once already with WHEN OTHERS. Here are the other two cases, because they catch people out in the same way.
First, a RAISE naming an exception that was never declared.
CREATE OR REPLACE PROCEDURE RAISEUNKNOWN_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
RAISE NO_SUCH_EXCEPTION_T149;
RETURN 'never reached';
EXCEPTION
WHEN OTHER THEN
RETURN 'caught it somehow';
END;
$$;| status |
|---|
| Function RAISEUNKNOWN_T149 successfully created. |
Created without a complaint. Now the call.
CALL RAISEUNKNOWN_T149();000904 (42000): SQL compilation error: error line 3 at position 10 invalid identifier 'NO_SUCH_EXCEPTION_T149'
000904, and note that the catch-all handler in that procedure did not save it.
Second, a WHEN branch naming a condition that does not exist.
CREATE OR REPLACE PROCEDURE WHENUNKNOWN_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
RAISE STATEMENT_ERROR;
RETURN 'never reached';
EXCEPTION
WHEN NO_SUCH_CONDITION_T149 THEN
RETURN 'never reached either';
END;
$$;| status |
|---|
| Function WHENUNKNOWN_T149 successfully created. |
CALL WHENUNKNOWN_T149();000904 (42000): SQL compilation error: error line 6 at position 9 invalid identifier 'NO_SUCH_CONDITION_T149'
000904 again. Neither name is looked up until the procedure runs, so a successful CREATE PROCEDURE tells you nothing about whether the names in your handler are real.
Now the part this post is really about
Let's write a loop that inserts one row per pass and raises at pass three, and let's give it a handler to catch that.
CREATE OR REPLACE PROCEDURE CAUGHTROWS_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
FOR I IN 1 TO 4 DO
INSERT INTO TRACE_T149 (STEP, NOTE) VALUES (:I, 'caught run pass ' || :I);
IF (:I = 3) THEN
RAISE STATEMENT_ERROR;
END IF;
END FOR;
RETURN 'never reached';
EXCEPTION
WHEN STATEMENT_ERROR THEN
RETURN 'caught it at pass three';
END;
$$;| status |
|---|
| Function CAUGHTROWS_T149 successfully created. |
Now let's call it.
CALL CAUGHTROWS_T149();| CAUGHTROWS_T149 |
|---|
| caught it at pass three |
The handler ran, so the call succeeded. From the outside this looks like a procedure that worked.
Now the question that matters. What is in the table?
SELECT STEP, NOTE FROM TRACE_T149 WHERE STEP <= 4 ORDER BY STEP;| STEP | NOTE |
|---|---|
| 1 | caught run pass 1 |
| 2 | caught run pass 2 |
| 3 | caught run pass 3 |
Three rows. One, two and three, every one of them still there, written by the passes that ran before the RAISE.
So catching the exception changed what the caller saw, and nothing else at all. Episode 147 showed that an uncaught failure leaves its rows behind. This shows that a caught one leaves them too. A handler is not an undo.
So where does the rollback go?
In the handler. Let's run the same loop inside a transaction and put a ROLLBACK in the handler before the return.
CREATE OR REPLACE PROCEDURE ROLLEDBACK_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
BEGIN TRANSACTION;
FOR I IN 1 TO 4 DO
INSERT INTO TRACE_T149 (STEP, NOTE) VALUES (:I + 10, 'rolled run pass ' || :I);
IF (:I = 3) THEN
RAISE STATEMENT_ERROR;
END IF;
END FOR;
COMMIT;
RETURN 'never reached';
EXCEPTION
WHEN STATEMENT_ERROR THEN
ROLLBACK;
RETURN 'caught it and rolled back';
END;
$$;| status |
|---|
| Function ROLLEDBACK_T149 successfully created. |
CALL ROLLEDBACK_T149();| ROLLEDBACK_T149 |
|---|
| caught it and rolled back |
Caught, and rolled back. Now let's count its rows.
SELECT COUNT(*) AS ROLLED_ROWS FROM TRACE_T149 WHERE STEP > 10;| ROLLED_ROWS |
|---|
| 0 |
Zero. Nothing it wrote is in the table.
And let's check it did not leave a transaction open behind it, because a handler that returns in the middle of one could.
SELECT CURRENT_TRANSACTION() AS THE_OPEN_TRANSACTION;| THE_OPEN_TRANSACTION |
|---|
| NULL |
NULL, which means there is no transaction open. The ROLLBACK closed it.
This is the pattern to write down. BEGIN TRANSACTION at the top of the body, COMMIT on the happy path, ROLLBACK in the handler. Without that last line, catching the failure leaves every row the block had already written.
Can a handler catch an error from a function?
The failures so far all happened in the block itself. Let's write a JavaScript function that throws its own error, and call it from a block with a catch-all.
CREATE OR REPLACE FUNCTION STRICTHALF_T149(N FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
if (N < 0) { throw "the function will not take a negative"; }
return N / 2;
$$;| status |
|---|
| Function STRICTHALF_T149 successfully created. |
CREATE OR REPLACE PROCEDURE CATCHUDF_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
N FLOAT;
BEGIN
N := STRICTHALF_T149(-1);
RETURN 'never reached';
EXCEPTION
WHEN OTHER THEN
RETURN 'caught the function error: ' || SQLERRM;
END;
$$;| status |
|---|
| Function CATCHUDF_T149 successfully created. |
CALL CATCHUDF_T149();| CATCHUDF_T149 |
|---|
| caught the function error: JavaScript execution error: Uncaught the function will not take a negative in STRICTHALF_T149 at ' if (N < 0) { throw "the function will not take a negative"; }' position 17 stackstrace: STRICTHALF_T149 line: 2 |
Caught, and look at what SQLERRM has in it. JavaScript's own text, the name of the function, the line that threw and a position.
So the handler sees across the language boundary. A SQL handler catches an error thrown by JavaScript and can read its message.
One last thing: TRY_CAST is the smaller answer
Here is a problem a handler can solve, and then a smaller way to solve it. Let's guard a cast that will not work.
CREATE OR REPLACE PROCEDURE CASTHANDLER_T149(S VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
N NUMBER;
BEGIN
N := :S::NUMBER;
RETURN 'it cast to ' || :N;
EXCEPTION
WHEN OTHER THEN
RETURN 'the cast failed: ' || SQLERRM;
END;
$$;| status |
|---|
| Function CASTHANDLER_T149 successfully created. |
CALL CASTHANDLER_T149('not a number');| CASTHANDLER_T149 |
|---|
| the cast failed: Numeric value 'not a number' is not recognized |
The handler caught it and told us why. And with an input that does cast?
CALL CASTHANDLER_T149('42');| CASTHANDLER_T149 |
|---|
| it cast to 42 |
Fine. So that works. But look at what we built to get there: a procedure, a block, a declared variable and a handler.
Now the same two inputs through TRY_CAST, with no procedure, no block and no handler at all.
SELECT TRY_CAST('not a number' AS NUMBER) AS THE_BAD_ONE,
TRY_CAST('42' AS NUMBER) AS THE_GOOD_ONE;| THE_BAD_ONE | THE_GOOD_ONE |
|---|---|
| NULL | 42 |
NULL for the one that will not cast, and the number for the one that will. Same problem, two answers, and for a cast the smaller one is usually the right one.
That is worth holding on to generally. A handler is the right tool when the failure is a real failure. When Snowflake already has a function that hands you a null instead, use that.
And let's leave the schema the way we found it
The demo script drops everything it made. Here is the check afterwards.
SELECT CURRENT_DATABASE() AS THE_DATABASE,
CURRENT_SCHEMA() AS THE_SCHEMA,
CURRENT_TRANSACTION() AS ANY_OPEN_TRANSACTION;| THE_DATABASE | THE_SCHEMA | ANY_OPEN_TRANSACTION |
|---|---|---|
| DEMO_DB | PUBLIC | NULL |
SELECT COUNT(*) AS PROBE_TABLES_LEFT
FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND ENDSWITH(TABLE_NAME, '_T149');| PROBE_TABLES_LEFT |
|---|
| 0 |
Same database and schema, no transaction open, and none of the tables this post created are left behind.
Key points
- The handler goes at the end of the block, after the statements, just before
END. Putting it first is001003and the procedure is never created. WHEN OTHERis the catch-all.WHEN OTHERS, with anS, does not exist in Snowflake and gives you000904when you call it.- The type name in Snowflake's own error text is the word you write in the branch, so
EXPRESSION_ERRORandSTATEMENT_ERRORare both catchable by name. - A handler can read
SQLCODE,SQLSTATEandSQLERRM. Concatenating all three after a bareRAISEgave back aNULL, so at least one of them is null there. - Declare your own exception with a code and a message and both reach the handler. Leave them out and
SQLCODEis-20000. - An uncaught exception of your own hands the caller its type name, its code and your message.
- A bare
RAISEin a handler re-raises the one it caught. Raising a different one replaces the original completely. - A failure climbs out of an inner block to the nearest handler. Give the inner block its own and the outer one never fires.
- A handler that itself fails hands the caller the new error. The one it was handling is gone.
- An undeclared
RAISEname and an unknownWHENname are both refused atCALL, not atCREATE. - Catching an exception leaves every row the block already wrote. A handler is not an undo.
ROLLBACKbelongs in the handler. With it the rows are gone and nothing is left open.- A handler catches a JavaScript function's error and
SQLERRMcarries its text. - For a cast,
TRY_CASTsolves the same problem with no block and no handler at all.
In the next post we build the statement itself at run time: dynamic SQL with EXECUTE IMMEDIATE, binding values with USING, and what one quote mark in the wrong place does to a lookup that concatenates its argument.
Every query, every result grid and every error pane on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The three refusals sit in the demo file after its cleanup, and all three were really run. Every count in the text is read off one of the grids on this page. No account identifier, user name or query id appears anywhere on this page. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake Scripting exceptions documentation.



No comments:
Post a Comment
Note: Only a member of this blog may post a comment.