plsql insert multiple rows using one execute immediate command

Is there a way to insert multiple row开发者_Python百科s in one EXECUTE IMMEDIATE? Rather than writing EXECUTE IMMEDIATE for each insert...

Hard to tell what you are inserting. You can use EXECUTE IMMEDIATE to do an INSERT...SELECT easily enough, but I suspect that isn't what you are after, and probably you're not simply wanting a loop around the EXECUTE IMMEDIATE.

If the multi-table insert isn't what you are looking for, you can use EXECUTE IMMEDIATE on a PL/SQL block and/or within a FORALL

create table test_forall_dyn (val varchar2(1));

declare
  type tab_char is table of varchar2(1) index by binary_integer;
  t_char tab_char;
begin
  for i in 1..26 loop
    t_char(i) := chr(64 + i);
  end loop;
  forall i in 1..26
    execute immediate 
      'begin 
         insert into test_forall_dyn (val) values(:1);  
         insert into test_forall_dyn (val) values(:1); 
       end;' 
       using t_char(i);
end;
/

select count(*) from test_forall_dyn;

EXECUTE IMMEDIATE
INSERT INTO table (col1, col2, col3) (
            SELECT 1 AS col1, 2 AS col2, 3 AS col3 FROM dual
  UNION ALL SELECT 4,         5,         6         FROM dual
  UNION ALL SELECT 7,         8,         9         FROM dual ) ;

Mariya, why use dynamic sql in the first place? Most of the times scalability is not exactly improved using dynamic sql. The same is for readability. Debugging is harder .... In many cases there are also weird security issues.... I don't know why you use dynamic sql but if this is part of a production application I would reconsider using it.

Ronald.

@maria First frame select quesry which gives u multiple row wich u are going to insert... You select stament shoud give data in order of data u want to insert in table

Then use..

Insert into Tabl1 (col1,col2,col3)(select name,address,phone from tabl2) commit;

Sure, you can go for batch insert...