Home » SQL & PL/SQL » SQL & PL/SQL » How do I find a bad/corrupted record ? (Oracle 10.2.0.1.0)
How do I find a bad/corrupted record ? [message #324209] Sat, 31 May 2008 11:23 Go to next message
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 #324210 is a reply to message #324209] Sat, 31 May 2008 11:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#sthref9456

error_logging_clause

The error_logging_clause lets you capture DML errors and the log column values of the affected rows and save them in an error logging table.


The implementation to do intermediate COMMITs is a very sub-optimal approach & could lead to ORA-01555 [SNAPSHOT TOO OLD] errors.

The correct approach is a single COMMIT after all INSERTs have completed.

[Updated on: Sat, 31 May 2008 11:31] by Moderator

Report message to a moderator

Re: How do I find a bad/corrupted record ? [message #324211 is a reply to message #324209] Sat, 31 May 2008 11:37 Go to previous messageGo to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Thanks anacedent,

I'll look into this. Both the error_logging_clause and the commit.

Walter
Re: How do I find a bad/corrupted record ? [message #324236 is a reply to message #324209] Sat, 31 May 2008 18:43 Go to previous message
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.

Previous Topic: help required in PLSQL
Next Topic: ACCEPT and PROMPT
Goto Forum:
  


Current Time: Sun Feb 16 00:41:25 CST 2025