英文:
Why can't I have a direct INSERT statement within my SQL Scripting stored procedure?
问题
Your stored procedure appears to be encountering a syntax error when you try to replace the first EXECUTE IMMEDIATE with a direct INSERT statement. The error message indicates an issue with the syntax near DB_NAME and :=.
The correct SQL syntax for the INSERT statement should be like this:
INSERT INTO CENTRAL.AUDIT.CLONING_HISTORY(SOURCE_ENVIRONMENT, SOURCE_DATABASE, DEST_ENVIRONMENT, DEST_DATABASE, AS_OF_TIMESTAMP, OPERATION)
VALUES(?, ?, ?, ?, ?, 'CLONE');
Make sure you use placeholders (?) for the values you want to insert and specify those values using the USING clause just like in your original EXECUTE IMMEDIATE statement.
Here's the corrected section of your code:
IF ( :DB_NAME IS NOT NULL ) THEN
DEST_DB_NAME := REGEXP_REPLACE(:DB_NAME, '^' || :ENV_SOURCE, :ENV_DEST);
INSERT INTO CENTRAL.AUDIT.CLONING_HISTORY(SOURCE_ENVIRONMENT, SOURCE_DATABASE, DEST_ENVIRONMENT, DEST_DATABASE, AS_OF_TIMESTAMP, OPERATION)
VALUES(?, ?, ?, ?, ?, 'CLONE')
USING (ENV_SOURCE, DB_NAME, ENV_DEST, DEST_DB_NAME, AS_OF_TIMESTAMP);
SQL := 'CREATE DATABASE ' || :DEST_DB_NAME || ' CLONE ' || :DB_NAME || ' AT(TIMESTAMP => ''' || AS_OF_TIMESTAMP::STRING || '''::TIMESTAMP_NTZ)';
EXECUTE IMMEDIATE SQL;
END IF;
This should resolve the syntax error you were encountering.
英文:
My stored procedure works fine if I use EXECUTE IMMEDIATE 'INSERT...'; inside the stored procedure.
But it seem over complicated to use EXECUTE IMMEDIATE as it looks like we can just write INSERT...
So this extract of my stored procedure works
IF ( :DB_NAME IS NOT NULL ) THEN
DEST_DB_NAME := REGEXP_REPLACE(:DB_NAME, '^' || :ENV_SOURCE, :ENV_DEST);
EXECUTE IMMEDIATE 'INSERT INTO CENTRAL.AUDIT.CLONING_HISTORY(SOURCE_ENVIRONMENT, SOURCE_DATABASE, DEST_ENVIRONMENT, DEST_DATABASE, AS_OF_TIMESTAMP, OPERATION)
VALUES(?, ?, ?, ?, ?, ''CLONE'')'
USING (ENV_SOURCE, DB_NAME, ENV_DEST, DEST_DB_NAME, AS_OF_TIMESTAMP);
SQL := 'CREATE DATABASE ' || :DEST_DB_NAME ||' CLONE ' || :DB_NAME || ' AT(TIMESTAMP => \'' || AS_OF_TIMESTAMP::STRING || '\'::TIMESTAMP_NTZ)';
EXECUTE IMMEDIATE SQL;
But if I try to replace the first EXECUTE IMMEDIATE by a direct insert, I get a weird error message when trying to create the procedure
IF ( :DB_NAME IS NOT NULL ) THEN
DEST_DB_NAME := REGEXP_REPLACE(:DB_NAME, '^' || :ENV_SOURCE, :ENV_DEST);
INSERT INTO AUDIT.CLONING_HISTORY(SOURCE_ENVIRONMENT, SOURCE_DATABASE, DEST_ENVIRONMENT, DEST_DATABASE, AS_OF_TIMESTAMP, OPERATION)
VALUES(ENV_SOURCE, DB_NAME, ENV_DEST, DEST_DB_NAME, AS_OF_TIMESTAMP, 'CLONE')';
SQL := 'CREATE DATABASE ' || :DEST_DB_NAME ||' CLONE ' || :DB_NAME || ' AT(TIMESTAMP => \'' || AS_OF_TIMESTAMP::STRING || '\'::TIMESTAMP_NTZ)';
EXECUTE IMMEDIATE SQL;
I do not understand the error msg either
> SQL compilation error:
> syntax error line 48 at position 8 unexpected 'DB_NAME'.
> syntax error line 48 at position 16 unexpected ':='.
I tried replacing my somewhat complex insert statement by a much simpler and I get the same error
INSERT INTO Whatever.SomeColumn(33);
PS: I would like to attach the entire procedure script but can't see how to do this!
It is just looping through some databases to clone them under a different name, and log this action in a table within the current database
答案1
得分: 1
在Snowflake Scripting块中，可以使用静态的INSERT与变量一起使用：
INSERT INTO AUDIT.CLONING_HISTORY(SOURCE_ENVIRONMENT, SOURCE_DATABASE,
DEST_ENVIRONMENT, DEST_DATABASE,
AS_OF_TIMESTAMP, OPERATION)
VALUES(ENV_SOURCE, DB_NAME, ENV_DEST, DEST_DB_NAME, AS_OF_TIMESTAMP, 'CLONE');
应该改为（var => :var）：
INSERT INTO AUDIT.CLONING_HISTORY(SOURCE_ENVIRONMENT, SOURCE_DATABASE,
DEST_ENVIRONMENT, DEST_DATABASE,
AS_OF_TIMESTAMP, OPERATION)
SELECT :ENV_SOURCE, :DB_NAME, :ENV_DEST, :DEST_DB_NAME, :AS_OF_TIMESTAMP, 'CLONE';
更多信息请参阅：在SQL语句中使用变量（绑定）。
英文:
It is possible to use variables with static INSERT inside Snowflake Scripting block:
INSERT INTO AUDIT.CLONING_HISTORY(SOURCE_ENVIRONMENT, SOURCE_DATABASE,
DEST_ENVIRONMENT, DEST_DATABASE,
AS_OF_TIMESTAMP, OPERATION)
VALUES(ENV_SOURCE, DB_NAME, ENV_DEST, DEST_DB_NAME, AS_OF_TIMESTAMP, 'CLONE');
should be (var => :var):
INSERT INTO AUDIT.CLONING_HISTORY(SOURCE_ENVIRONMENT, SOURCE_DATABASE,
DEST_ENVIRONMENT, DEST_DATABASE,
AS_OF_TIMESTAMP, OPERATION)
SELECT :ENV_SOURCE, :DB_NAME, :ENV_DEST, :DEST_DB_NAME, :AS_OF_TIMESTAMP, 'CLONE';
答案2
得分: 0
其实有点尴尬，但那是个人的小错误...
在我的插入语句中留下了一个尾随的单引号...
，AS_OF_TIMESTAMP，'CLONE'）';
在分号之前不应该有一个单引号！！！
英文:
It's actually embarrassing but it was a personal Boo Boo...
I had left a trailing single quote in my INSERT statement...
, AS_OF_TIMESTAMP, 'CLONE')';
There should not have been a ' before the ; !!!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论