Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Insert error - URGENT
Bulk Insert error - URGENT [message #183443] Thu, 20 July 2006 23:57 Go to next message
mathan
Messages: 24
Registered: March 2005
Junior Member
Hi all,
One of my customer is using bulk insert and forall statement to insert into a table.After debugging I found that "ORA-01722: invalid number" is happening. But after capturing all the values in the array, I find that the values in the array are matching the col types to be inserted. Please help!!!

This is the code:

FORALL i IN I_PVT.NODES_LIST.FIRST..NODES_LIST.LAST SAVE EXCEPTIONS
insert into RT_NODES values I_PVT.NODES_LIST(i);
exception
WHEN array_dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1..errors LOOP
variable := SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);

Thanks in advance!
Re: Bulk Insert error - URGENT [message #183444 is a reply to message #183443] Fri, 21 July 2006 00:03 Go to previous messageGo to next message
mathan
Messages: 24
Registered: March 2005
Junior Member
Forgot to mention that this same code is working at our internal site and at other customers.
Re: Bulk Insert error - URGENT [message #183446 is a reply to message #183443] Fri, 21 July 2006 00:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
same code, different data.
I am not surprised.
Re: Bulk Insert error - URGENT [message #183448 is a reply to message #183446] Fri, 21 July 2006 00:13 Go to previous messageGo to next message
mathan
Messages: 24
Registered: March 2005
Junior Member
Hi,
I know what you are implying but I have checked and rechecked the data but there is no problem with that. I have tried manually inserting it and it works.
Could this problem be becoz we have not mentioned the column names in the insert?
As in "insert into table (col1,col2..) values list(i);"
Re: Bulk Insert error - URGENT [message #183451 is a reply to message #183443] Fri, 21 July 2006 00:32 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Just Check

1. Wether the datatypes are matching (Table datatype V/s Array datatpe)
2. Whether the elements of array are in same order of Table fileds.
(Otherwise use fiels list in the insert statement ).

Thumbs Up
Rajuvan.

[Updated on: Fri, 21 July 2006 00:32]

Report message to a moderator

Need help : wanted all the list of ORA errors thrown by select command [message #183541 is a reply to message #183443] Fri, 21 July 2006 04:47 Go to previous messageGo to next message
agarwalharish79
Messages: 1
Registered: July 2006
Junior Member
Can anyone help me to get ORA errors ...

I am executing a select command on a large table which has around 1000000 records. Table and query what I am giving is correct. This works fine normal scnerio.

Can anybody suggest a typical scnerio when this Select query will throw any ORA error ???
Re: Need help : wanted all the list of ORA errors thrown by select command [message #183586 is a reply to message #183541] Fri, 21 July 2006 08:01 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Do you just want to see any Oracle error? You have opened up a SQL*Plus session, then the database was shut down. You do a SELECT and you will get this.
tstdb SCOTT> /
select * from tab
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


tstdb SCOTT>

Re: Bulk Insert error - URGENT [message #184016 is a reply to message #183451] Mon, 24 July 2006 22:05 Go to previous messageGo to next message
mathan
Messages: 24
Registered: March 2005
Junior Member
Following up to the bulk insert problem mentioned earlier..I modified the following..
forall list(i).first..list(i).last
insert into table values list(i);"

to
for i in 1..list(i).count loop
insert into table (col1,col2..) values
(list(i).col1,list(i).col2,list(i).col3..);
end loop;

Earlier I was getting an "invalid number" error. But now I am getting a "no data found" error in the exception handler.

Experts, please advise on the possible scenarios where the problem could be and how to override this.
Re: Bulk Insert error - URGENT [message #184037 is a reply to message #184016] Tue, 25 July 2006 00:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

for i in 1..list(i).count

This doesn't make sense. May be a typo, but the list(i).count should be list.count
Re: Bulk Insert error - URGENT [message #184040 is a reply to message #184037] Tue, 25 July 2006 00:53 Go to previous messageGo to next message
mathan
Messages: 24
Registered: March 2005
Junior Member
Thats right.
Re: Bulk Insert error - URGENT [message #184041 is a reply to message #184016] Tue, 25 July 2006 01:02 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Try with ..

for i in list.FIRST .. list.LAST 
Loop
        insert into table (col1,col2..) values 
        (list(i).col1,list(i).col2,list(i).col3..);
end Loop;


Thumbs Up
Rajuvan.
Previous Topic: add value to one record based on another record
Next Topic: describe a nested table
Goto Forum:
  


Current Time: Sat Dec 10 05:02:20 CST 2016

Total time taken to generate the page: 0.18082 seconds