SnowSQL, procedure - error 904 when using variable in insert statement-CodePudding

I'm working on snowflake procedure in snowsql and want to use variable as value in insert statement - something like that:

execute immediate $$
declare
  select_statement string;
begin
  select_statement := '''Some text''';
  INSERT INTO SOME_TABLE(MESSAGE) values (select_statement);    
 exception
  when statement_error then
    return object_construct('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  when other then
    return object_construct('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
end;
$$
;



{
  "Error type": "STATEMENT_ERROR",
  "SQLCODE": 904,
  "SQLERRM": "SQL compilation error: error line 1 at position 102\ninvalid identifier 'SELECT_STATEMENT'",
  "SQLSTATE": "42000"
}

I was trying to use single quote as well

select_statement := 'Some text';

and also without declaring it and using let

let select_statement := 'Some text';

Each time getting the same error...

CodePudding user response：

This just needs some minor adjustments to the syntax. To use execute immediate as a string, create the string with one or more bind variables. Use the "using" clause to bind them in the execute immediate.

create or replace table some_table(message string);

execute immediate $$
declare
  rs resultset;
  select_statement string;
  ins string default 'INSERT INTO SOME_TABLE(MESSAGE) values (?)';
begin
  select_statement := 'Some text';
  rs := (execute immediate :ins using (select_statement));
  return table(rs);
 exception
  when statement_error then
    return object_construct('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  when other then
    return object_construct('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
end;
$$
;

The return of the table is optional, but helps with the return for programmatic access or human readability.

number of rows inserted
1

CodePudding user response：

please try this.

create or replace table SOME_TABLE(MESSAGE varchar2 );
select * from SOME_TABLE;
execute immediate $$
declare
  select_statement string;
begin
  select_statement := '''Some text''';
  INSERT INTO SOME_TABLE(MESSAGE) values (:select_statement);    
 exception
  when statement_error then
    return object_construct('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  when other then
    return object_construct('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate); 
end;
$$;