Is there a way to insert multiple rows in one EXECUTE IMMEDIATE? Rather than writing EXECUTE IMMEDIATE for each insert...
Add a comment
|
5 Answers
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;
Comments
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 ) ;
Comments
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.
Comments
@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;
Comments
Sure, you can go for batch insert...
