Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 10g DML error logging (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0)
Oracle 10g DML error logging [message #303944] Mon, 03 March 2008 06:33 Go to next message
rs1969
Messages: 21
Registered: December 2007
Location: UK
Junior Member
Hi,

I am using the new 10g feature for DML error logging using the ERR$ tables, so that a bad record does not fail the entire transaction. I am processing a source table which has some XML data within a loop and inserting into various target tables. After each record from this source table has been processed successfully, a processed_flag is set to 'Y' and committed.

My problem is that if I am using automatic bulk error logging mechanism, is there any way to find out if there were any errors which got logged into the ERR$ tables (apart from selecting from those tables)?

If there were any errors then I do not want to set the processed_flag on the source table. Fetching from the ERR$ tables each time within the cursor seems ridiculous. I cannot use SQL%ROWCOUNT as target table may or may not inserted into depending on data. Hope I have made myself clear. Any advice is highly appreciated.

Thanks.
Re: Oracle 10g DML error logging [message #304007 is a reply to message #303944] Mon, 03 March 2008 12:14 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think it will be useful if you could post the actual insert statement and select statement (by giving us an example using the scott schema preferably so that it will be easy for us replicate or point you towards right direction). It will give us more clarity rather than making lot of assumptions like for e.g:

a) You are using multi table insert
b) You are doing bulk collect and bulk insert etc.

Regards

Raj
Re: Oracle 10g DML error logging [message #304011 is a reply to message #304007] Mon, 03 March 2008 12:58 Go to previous message
rs1969
Messages: 21
Registered: December 2007
Location: UK
Junior Member
Pseudo code is like this:
DECLARE
   CURSOR c0 IS
      SELECT xmltype_col, ROWID FROM source_table;
BEGIN
   FOR cr_0 IN c0
   LOOP
      -- Extract XML tags from XMLTYPE column
      SELECT ExtractValue(...)
      FROM   TABLE(XMLSEQUENCE(EXTRACT(VALUE(cr_0.xmltype_col), 'B/C/D'))) tc;

      -- Insert into various target tables
      INSERT INTO target_table (col1, col2, ...)
      VALUES (cr_0.x1, cr_0.x2, ...)
      LOG ERRORS
      REJECT LIMIT UNLIMITED;

      ...

      UPDATE source_table
      SET    processed_ind = 'Y'
      WHERE  ROWID = cr_0.ROWID;

   END LOOP;
END;
/


In this case I would like to set the processed_ind to 'Y' only when there are no errors in the bulk inserts into target tables. Is there any way I can check if 'LOG ERRORS' resulted in any errors being logged without querying the 'ERR$_..' tables? I tried checking value of SQLCODE immediately after the insert but the value is null.

Please let me know if you need further info. Thanks
Previous Topic: ORA-01427: single-row subquery returns more than one row
Next Topic: unable to use dbms_lob.append or writeappend
Goto Forum:
  


Current Time: Tue Dec 06 13:53:23 CST 2016

Total time taken to generate the page: 0.09202 seconds