Home » SQL & PL/SQL » SQL & PL/SQL » Insert error in Bulk collect
Insert error in Bulk collect [message #278225] Fri, 02 November 2007 07:40 Go to next message
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 #278227 is a reply to message #278225] Fri, 02 November 2007 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can't do that in the version you have.
You have to declare a table per variable, you can't use an array of records.

Regards
Michel
Re: Insert error in Bulk collect [message #278527 is a reply to message #278227] Sun, 04 November 2007 22:21 Go to previous messageGo to next message
rachit_mittal
Messages: 2
Registered: November 2007
Location: Noida
Junior Member
Dear Michel,
In which Version this is possible
Regards
Rachit
Re: Insert error in Bulk collect [message #278534 is a reply to message #278527] Sun, 04 November 2007 23:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
From Oracle papers, 11g, but I didn't yet test it.

Regards
Michel
Re: Insert error in Bulk collect [message #278540 is a reply to message #278527] Mon, 05 November 2007 00:38 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Michel Wanted to say ,

What You are trying to implement is possioble only in Oracle 11g as it is. But you could try a different method of acheving the same without using one table for each variable .

 declare
     type l_table is table of main_tab%ROWTYPE ;
     p_table l_table := l_table();
     begin
     select a,b,NULL bulk collect into p_table from odi_tab;
     forall i in 1..p_table.count save exceptions
     insert into main_tab values p_table(i);
  end;


Am i right Michel ?

Thumbs Up
Rajuvan.

[Updated on: Mon, 05 November 2007 00:39]

Report message to a moderator

Re: Insert error in Bulk collect [message #278542 is a reply to message #278540] Mon, 05 November 2007 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes it is possible that way but take care that inserting a NULL is not the same thing that not giving a value.
Not giving a value means let Oracle put the default value which may not be NULL.

Regards
Michel
Re: Insert error in Bulk collect [message #278912 is a reply to message #278534] Tue, 06 November 2007 04:53 Go to previous message
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
Previous Topic: privileges......
Next Topic: What is RULE (not ROLE) sql object?
Goto Forum:
  


Current Time: Wed Feb 19 01:55:57 CST 2025