英文:
Snowflake - Getting invalid identifier error while passing variables between SQL Stored Procs
问题
I have a Snowflake SQL stored procedure from where I'm trying to call the SYSTEM$SEND_EMAIL function, but its erroring out as below:
`create or replace procedure testsproc()
RETURNS VARCHAR
language sql
as
$$
BEGIN
SELECT * FROM non_existent_table;
EXCEPTION
WHEN OTHER THEN
LET LINE := SQLCODE || ': ' || SQLERRM;
INSERT INTO myexception VALUES (:LINE); -- this line works
call SYSTEM$SEND_EMAIL('MYEMAIL','abc@mail.com','Errors!!',LINE); -- this line is throwing the error invalid identifier 'LINE' (line 320)
return line;
END;
$$`
英文:
I have a Snowflake SQL stored procedure from where I'm trying to call the SYSTEM$SEND_EMAIL function, but its erroring out as below:
`create or replace procedure testsproc()
RETURNS VARCHAR
language sql
as
$$
BEGIN
SELECT * FROM non_existent_table;
EXCEPTION
WHEN OTHER THEN
LET LINE := SQLCODE || ': ' || SQLERRM;
INSERT INTO myexception VALUES (:LINE); -- this line works
call SYSTEM$SEND_EMAIL('MYEMAIL','abc@mail.com','Errors!!',LINE); -- this line is throwing the error invalid identifier 'LINE' (line 320)
return line;
END;
$$`
I tried passing :LINE to system$send_email, it still isn't working. What am I doing wrong?
Any help is much appreciated.
答案1
得分: 1
缺少一个冒号，与脚本上一行使用的冒号相同：
修正后：
call SYSTEM$SEND_EMAIL('MYEMAIL','abc@mail.com','Errors!!', :LINE);
通过这样做，我会在我的电子邮件中得到所需的异常：
英文:
It's missing a colon, the same colon the script uses in the previous line:
Fixed:
call SYSTEM$SEND_EMAIL('MYEMAIL','abc@mail.com','Errors!!', :LINE);
With that, I get the desired exception in my email:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论