Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005

    Unanswered: Exception handling for bulk data transfers - PL/SQL

    I am trying to transfer large data from one table to another in Oracle 9i.
    The transfer could be an insert / update.
    If the record of source exists in destination, then it's an update.
    Else, it's an insert.
    I'm using MERGE statement for this purpose.

    The issue I'm facing is:
    In case an exception occurs in between, I should be able to store exception details in another table, and then continue with next record.
    What I'm unable to do is: continuing with next record.

    I have a source table: TEMP_CUSTOMERS.
    Destination table is : CUSTOMERS
    Suppose I have 1000 records in TEMP_CUSTOMERS table and 500 records in CUSTOMERS.
    I am using a MERGE statement - so the records in TEMP_CUSTOMERS would be transferred to CUSTOMERS (update / insert).

    In case there is an exception at 550th row in TEMP_CUSTOMERS, then I should store the exception details (550th record transfer failed, reason is..........) in an ERROR_LOG table, and then I should continue the transfer from 551st row.

    What is the best way of achieving this? All my transfer is done through a stored procedure.
    Last edited by sriramkanala; 12-07-05 at 06:20. Reason: Thread subscription preference changed

  2. #2
    Join Date
    Jul 2003
    I have never gotten MERGE to work.
    Since you need a more robust logging system then I suggest
    writing a few cursors, parsing through them, as you hit each error you
    can have an exception handler running.

    My advice: don't use merge (maybe it's fixed in 10g, but I still say don't
    use it)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts