How do I find a bad/corrupted record ? [message #324209] |
Sat, 31 May 2008 11:23  |
walter01
Messages: 28 Registered: April 2008
|
Junior Member |
|
|
I have a stored procedure that finds order lines and inserts them into a table. After about 149865 inserts an error is produces. I have a good idea of what the problem might be but how do I find the record that causes the error ?
Quote: | ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "BAAN.WG01_P1", line 124
ORA-06512: at line 2
|
Some of my code... Line 124 is the <<sls041>> line in my code.
<<sls041>>
for i in v_041_orno.first .. v_041_orno.last loop
-- dbms_output.put_line('041 - ' || v_041_orno(i) ||'_'|| v_041_pono(i) ||'_'|| v_041_cuno(i) ||'_'|| v_041_cdel(i) );
insert into BAAN_TDSLS041 (t$orno,t$pono,t$cuno,t$cdel,t$cpva,t$cwar,t$item,t$oqua,t$ldam$1,t$cvat,t$ccty)
values(v_041_orno(i),v_041_pono(i),v_041_cuno(i),v_041_cdel(i),v_041_cpva(i),v_041_cwar(i),v_041_item(i),v_041_oqua(i),v_041_ldam1(i),v_041_cvat(i),v_041_ccty(i));
commit_counter := commit_counter + 1;
if commit_counter = 200 then
commit;
commit_counter := 0;
end if;
end loop sls041;
Thanks,
Walter
|
|
|
|
|
Re: How do I find a bad/corrupted record ? [message #324236 is a reply to message #324209] |
Sat, 31 May 2008 18:43  |
coleing
Messages: 213 Registered: February 2008
|
Senior Member |
|
|
Also look into re-writing using BULK COLLECT and FORALL INSERT.
Using a %ROWTYPE array will allow you to handle the error before you ever get near inserting it. Much quicker.
Value errors, and number too large errors etc, can all be captured before doing any dml, and when you do some dml (the bulk insert) you can use the SAVE EXCEPTIONS clause to capture any primary key/foreign key violations (although with a bit of extra coding, these can be catered for in the array processing also lookup associative arrays for combined key columns array indexes if you are interested in this.).
although the new error logging clause is useful, its painfully slow for large volume.
|
|
|