12-07-05, 06:19 #1Registered User
- 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
12-08-05, 12:08 #2Registered User
- 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 ...