skip to Main Content

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


  1. 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:

    select count(*) from identifier(:table_name)
    
    Login or Signup to reply.
  2. If you trust the source data, just build the whole thing as a string:

    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';
      tname text;
      src_name text;
      tgt_name text;
      sql text;
    begin
        for r in tnames do
            src_name := src_db_name ||'.'|| r.tname;
            tgt_name := tgt_db_name ||'.'|| dev_schema_name || '_' || r.tname;
            sql := 'create table ' || tgt_name || ' as select * from ' || src_name;
            
            execute immediate :sql;
            commit;
        end for;
        return sql;
    end;
    $$
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search