Snowflake存储过程执行报错：STATEMENT_ERROR类型未捕获异常（无效标识符'var_dn'）排查与修复求助

Let's break down why you're hitting this error and how to fix it step by step:

Root Cause #

The main issue is that you're using Oracle's VARCHAR2 data type for variable declarations in your Snowflake SQL stored procedure. While Snowflake supports VARCHAR2 in query contexts (for Oracle compatibility), it doesn't recognize it as a valid type for stored procedure variables. This causes the database to fail to properly register your var_dn variable, leading to the "invalid identifier" error.

Fixed Stored Procedure Code #

Here's the corrected version of your procedure with explanations of the changes:

create or replace procedure sp_to_test() 
returns varchar 
language sql 
as $$ 
declare 
    var_dn VARCHAR(6200); -- Changed VARCHAR2 to Snowflake-native VARCHAR
    tmp_str integer; 
    prc_nm varchar(100); -- Changed VARCHAR2 to Snowflake-native VARCHAR
begin 
    select count(1) into tmp_str 
    from information_schema.tables 
    where table_name ='TMP_TBL_TO_TEST'; 

    IF (tmp_str = 1) then 
        var_dn := 'Drop Table TMP_TBL_TO_TEST'; 
        execute immediate var_dn; 
    END IF; 

    INSERT INTO log_tbl (sql_log_key, rsi_var, txt_val, v_date) 
    VALUES (seq_val.nextval, 'sp_to_test', var_dn, CURRENT_TIMESTAMP()); -- Replaced SYSDATE() with standard Snowflake timestamp function
    commit; 

    var_dn := 'Create Table TMP_TBL_TO_TEST AS Select * From ORG_TBL'; 
    INSERT INTO log_tbl (sql_log_key, rsi_var, txt_val, v_date) 
    VALUES (seq_val.nextval, 'sp_to_test', var_dn, CURRENT_TIMESTAMP()); 
    commit; 

    execute immediate var_dn; 
end ; 
$$ ;

Key Changes Made #

• Replaced VARCHAR2 with VARCHAR: Snowflake's SQL stored procedures require using native data types for variable declarations. VARCHAR (or its equivalent STRING) is the correct type for string variables here.
• Swapped SYSDATE() for CURRENT_TIMESTAMP(): While SYSDATE() works in Snowflake for cross-database compatibility, CURRENT_TIMESTAMP() is the more standard, Snowflake-native function for fetching the current timestamp. If you only need the date component, you could use CURRENT_DATE() instead.

Additional Tips #

• Double-check that seq_val is a valid sequence in your account, and that log_tbl exists with a schema matching the columns you're inserting into.
• Consider adding an EXCEPTION block to handle unexpected errors during execution—this will make debugging future issues much easier.

内容的提问来源于stack exchange，提问作者Sherin Shaziya