I’m writing a script to create 5 tables, but cannot seem to get the SQL DDL part of the statement to work. I get a syntax error " unexpected 'table' "
, and I’ve tried the SQL statement with execute immediate
as well. What am I doing wrong?
execute immediate $$
declare
tnames cursor for select value as tname from table( flatten ( ['OPERATIONS.TABLE1','OPERATIONS.TABLE2','OPERATIONS.TABLE3' ] ) );
src_db_name text default 'SRC_DB';
tgt_db_name text default 'TARGET_DB';
dev_schema_name text default 'MYSCHEMA';
begin
for r in tnames do
let src_name := src_db_name ||'.'|| r.tname;
let tgt_name := tgt_db_name ||'.'|| dev_schema_name || '_' || r.tname;
create table :tgt_name as select * from :src_name ;
commit;
end for;
end;
$$
2
Answers
To quote the documentation:
If you are using the variable as the name of an object (e.g. the name of a table in the FROM clause of a SELECT statement), use the IDENTIFIER keyword to indicate that the variable represents an object identifier. For example:
If you trust the source data, just build the whole thing as a string: