Execute immediate statement

Question

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;

Give space after from (select ''tem'' from '||v_tabl_name||' and also use into clause in your insert statement. — Tharunkumar Reddy, Oct 30 '15 at 09:52
Right, I put space after from... weird but it works :) thanks . I don't know why where I put my code without "into" clause. — bazyl, Oct 30 '15 at 09:58
it's not weird, if you think about it. It's the difference between `fromtable_name` and `from table_name`. One is syntactically correct, the other is not... — Boneist, Oct 30 '15 at 10:37
Still have problem with other execute immediate statement... execute immediate 'select count(*) into v_zmienna from user_tables where table_name =||'''||v1_tabl_name||''; How the hell can I do that? Suppose there is some problem with inverted commas... — bazyl, Oct 30 '15 at 11:50
this is 2nd question that duplicates http://stackoverflow.com/questions/20615600/oracle-how-to-use-procedure-local-variables-for-execute-immediate-statements — are, Oct 30 '15 at 12:16

score 0 · Accepted Answer · answered Oct 30 '15 at 10:05

0

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;

answered Oct 30 '15 at 10:05

are

2,535
2
22
27

Still have problem with other execute immediate statement... execute immediate 'select count(*) into v_zmienna from user_tables where table_name =||'''||v1_tabl_name||''; How the hell can I do that? Suppose there is some problem with inverted commas...I'm really confused – bazyl Oct 30 '15 at 11:53
you need pass local variable as a parameter into execute immediate see this link http://stackoverflow.com/questions/20615600/oracle-how-to-use-procedure-local-variables-for-execute-immediate-statements – are Oct 30 '15 at 12:15
Of course that I did it, declare v1_table_name varchar2(30). But there is some problem when I want to use that variable for the purpose of "table_name". When I use it as eg. execute immediate 'insert into'||v1_table_name|| (...) - it works fine, but there is some problem when i'm trying to put it into where clause.... Problably there is some problem with inverted commas ; / – bazyl Oct 30 '15 at 13:07
can you start next question and put your script into description? I'm sure somebody help with solution – are Oct 30 '15 at 13:32

Execute immediate statement

1 Answers1