| bulk insert in dynamic sql [message #73920] |
Tue, 03 August 2004 04:49  |
argya
Messages: 7
Registered: August 2004
|
Junior Member |
|
|
Plz
tell me how to do bulk collect or bulk insert
in dynamic sql .
plz elaborate if possible.
i am new to this and dont have an idea.
thankxxx
argya
|
|
|
|
| Re: bulk insert in dynamic sql [message #73939 is a reply to message #73920] |
Thu, 05 August 2004 06:38  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
First of all create these two below mentioned Types in your Database:
SQL>create or replace type emp_rec1 as object
( ename varchar2(10),
hiredate date,
sal number(7,2)
);
SQL>create or replace type testarray as table of emp_rec1;
Now use them in your PL?SQL or Procedure:
declare
EMP_PLSQL_TAB testarray;
cursor cu_cursor is select emp_rec1(ename, Hiredate, Sal) from emp;
begin
EMP_PLSQL_TAB := testarray();
open cu_cursor; loop fetch cu_cursor bulk collect into EMP_PLSQL_TAB LIMIT 1000;
insert into emp_MK( ename, hiredate, sal )
select * from TABLE( cast (EMP_PLSQL_TAB as testarray) );
exit when cu_cursor%notfound;
end loop;
close cu_cursor;
end;
/
So what only you need to do in the above mentioned code is to chnage the Cursor Statement to make it a Dynamic Cursor & the rest will remain the same.
HTH
Regards
Himanshu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
