SQL Error [90232] [25000] returned running Snowflake anonymous block

[bobf32]

System information:

• Windows 10 Enterprise 21H2 build 19044.1526
• 22.0.0
• Additional extensions: None

Connection specification:

• Snowflake
• Driver name: Default
• Do you use tunnels or proxies (SSH, SOCKS, etc)? No

Describe the problem you're observing:

I'm trying to run an anonymous block of snowflake script. The block works as expected running it in the snowflake web interface.

Running the block below in manual commit mode gives the error:

SQL Error [90232] [25000]: Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.

If executed in auto commit mode it succeeds.

Steps to reproduce, if exist:

EXECUTE IMMEDIATE $$
DECLARE
    l_b_date date;
BEGIN
    l_b_date := to_date('10-MAR-22', 'dd-mon-yy');

    MERGE INTO foo tgt
    USING (
        SELECT 'B_DATE' name, :l_b_date VAL) src
    ON (tgt.NAME = src.NAME)
    WHEN MATCHED THEN
    UPDATE SET
        tgt.VAL = src.VAL
    WHEN NOT MATCHED THEN
    INSERT (
        tgt.NAME, tgt.VAL)
    VALUES (
        src.NAME, src.VAL
    );

END;
$$

Include any warning/errors/backtraces from the logs

snowflake-error.log

[LonwoLonwo]

Hello @bobf32

Can you please check the query manager?

Is the query with this error equal to your statement? I see extra lines in your query, and DBeaver uses them as delimiters by default.

[bobf32]

Sorry @LonwoLonwo, not sure I follow you. I have just run this version and am copying the text from the query log. As before, I get the error if commit mode is manual. No error with auto commit enabled.

EXECUTE IMMEDIATE $$
DECLARE
    l_b_date date;
BEGIN
    l_b_date := to_date('10-MAR-22', 'dd-mon-yy');

    MERGE INTO foo tgt
    USING (
        SELECT 'B_DATE' name, :l_b_date VAL) src
    ON (tgt.NAME = src.NAME)
    WHEN MATCHED THEN
    UPDATE SET
        tgt.VAL = src.VAL
    WHEN NOT MATCHED THEN
    INSERT (
        tgt.NAME, tgt.VAL)
    VALUES (
        src.NAME, src.VAL
    );

END;
$$

[LonwoLonwo]

Sorry @bobf32

I'm not sure that we can help in this case from the DBeaver side. Why can't you execute this script in auto-commit mode?

Maybe this video can be helpful for you? https://www.youtube.com/watch?v=ya-7U2nuD90

[bobf32]

Sorry @bobf32

I'm not sure that we can help in this case from the DBeaver side. Why can't you execute this script in auto-commit mode?

Maybe this video can be helpful for you? https://www.youtube.com/watch?v=ya-7U2nuD90

Thanks. I think I see the issue. I can execute in auto commit mode, and can surround the block with a separate BEGIN TRANSACTION; and COMMIT;

However, I notice that although I can hit Ctrl-Enter with the cursor on the COMMIT; and the the transaction will commit, if I do the same with the cursor on BEGIN TRANSACTION; I get a message at the bottom of the DBeaver window saying "Empty query string" and a little red triangle. I have to select the entire BEGIN TRANSACTION; line and then hit Ctrl-Enter for it to work.

[LonwoLonwo]

I'm not sure. I need to see the full statement. But maybe disabling this setting can help:

[bobf32]

Disabling the setting did not work. The statement is simply:

BEGIN TRANSACTION;

To recreate the issue, place the cursor anywhere inside that statement and hit Ctrl-Enter.

[bobf32]

I will close this issue and raise a new one for the BEGIN TRANSACTION; anomaly.