Insert error in Bulk collect [message #278225] |
Fri, 02 November 2007 07:40  |
rachit_mittal
Messages: 2 Registered: November 2007 Location: Noida
|
Junior Member |
|
|
When I try to insert data in main_tab table then error is raised.
main table have million of records so i have to use bulk collect
but it don't allow me use selective columns for Insert.
Can you Please tell me how to handle this error.
SQL> conn scott@fin1db
Connected.
SQL> create table main_tab(a number(1),b varchar2(10),c number(5));
Table created.
SQL> create table odi_tab(a number(1),b varchar2(10));
Table created.
1 declare
2 type l_rec is record( a number(1),b varchar2(10));
3 type l_tab is table of l_rec;
4 p_table l_tab;
5 begin
6 select a,b bulk collect into p_table from odi_tab;
7 forall i in 1..p_table.count save exceptions
8 insert into main_tab(a,b) values (p_table(i).a,p_table(i).b);
9* end;
SQL> /
insert into main_tab(a,b) values (p_table(i).a,p_table(i).b);
*
ERROR at line 8:
ORA-06550: line 8, column 35:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 8, column 35:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 48:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 8, column 48:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 35:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 8, column 1:
PL/SQL: SQL Statement ignored
|
|
|
|
|
|
|
|
Re: Insert error in Bulk collect [message #278912 is a reply to message #278534] |
Tue, 06 November 2007 04:53  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Indeed, it is supported in 11g:
SQL> create table main_tab (a number(1),b varchar2(10),c number(5));
Table created.
SQL> create table odi_tab (a number(1),b varchar2(10));
Table created.
SQL> declare
2 type l_rec is record( a number(1),b varchar2(10));
3 type l_tab is table of l_rec;
4 p_table l_tab;
5 begin
6 select a,b bulk collect into p_table from odi_tab;
7 forall i in 1..p_table.count save exceptions
8 insert into main_tab(a,b) values (p_table(i).a,p_table(i).b);
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> @v
Version Oracle : 11.1.0.6.0
Regards
Michel
|
|
|