sqloracle-databaseplsqlexecute-immediateora-00923

Execute immediate statement


I'm trying to insert data into table using execute immediate statement. But I get an error

FROM keyword not found where expected

Could anyone take a look what's wrong?

declare
  c1 SYS_REFCURSOR;
  v_tabl_name varchar2(30);
begin
  open c1 for
    select tablename from table1;

  LOOP
    FETCH c1 INTO v_tabl_name;
    EXIT WHEN c1%NOTFOUND;

    execute immediate 'insert tabl2(tabl_name) (select ''tem'' from'||v_tabl_name||')' ;
  END LOOP;

  close c1;
end;

Solution

  • create table table1(tem varchar2(50), tablename varchar2(50));
    create table tabl2(tabl_name varchar2(50));
    
    insert into table1(tem, tablename) values ('table1','table1');
    
    begin
      for rc in (select tablename from table1) loop
         --dbms_output.put_line('insert into tabl2(tabl_name) (select ''tem'' from '||rc.tablename||')');
         execute immediate 'insert into tabl2(tabl_name) (select ''tem'' from '||rc.tablename||')' ;    
      end LOOP; 
    end;