3:31 AM
Deep Dey
Can I use an Oracle exception like dup val on index to detect if the record exists within the table?
I'm loading a fact table in a data mart and I cannot use MERGE or extract using the update dates because the activity dates and update dates are different. I'm using PL/SQL for this.
You certainly can use Oracle's dup_val_on_indexexception to detect if a record you are inserting into the database already exists. You would need some additional code in the exception handler to get a new key for the record and you would need to put the insert statement inside its own PL/SQL block to ensure that the controlling process can continue after the exception. You would end up with something like the following.
PROCEDURE update_database IS
BEGIN
LOOP
<read source record>
BEGIN
INSERT INTO <dest tabled> <source record>;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN <get new key and re-insert>
END;
END LOOP;
END update_database;
Having said that, it is possibly not the ideal solution as it will be quite slow especially if a large number of exceptions are generated. It would be much faster if you used Oracle's MERGE syntax (remember you can embed any SQL statements directly into your PL/SQL code) and from what you've said it is difficult to see why you can't use it.
The MERGE statement looks complicated but is designed for exactly this sort of operation and enables you to specify the exact conditions on which records are deemed to match and it is not necessary to have both an update clause and an insert clause (in fact both clauses are optional) so you could use the MERGE statement for those records that match and then have some other logic outside the MERGE statement to deal with the records that don't match.
I'm loading a fact table in a data mart and I cannot use MERGE or extract using the update dates because the activity dates and update dates are different. I'm using PL/SQL for this.
You certainly can use Oracle's dup_val_on_indexexception to detect if a record you are inserting into the database already exists. You would need some additional code in the exception handler to get a new key for the record and you would need to put the insert statement inside its own PL/SQL block to ensure that the controlling process can continue after the exception. You would end up with something like the following.
PROCEDURE update_database IS
BEGIN
LOOP
<read source record>
BEGIN
INSERT INTO <dest tabled> <source record>;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN <get new key and re-insert>
END;
END LOOP;
END update_database;
Having said that, it is possibly not the ideal solution as it will be quite slow especially if a large number of exceptions are generated. It would be much faster if you used Oracle's MERGE syntax (remember you can embed any SQL statements directly into your PL/SQL code) and from what you've said it is difficult to see why you can't use it.
The MERGE statement looks complicated but is designed for exactly this sort of operation and enables you to specify the exact conditions on which records are deemed to match and it is not necessary to have both an update clause and an insert clause (in fact both clauses are optional) so you could use the MERGE statement for those records that match and then have some other logic outside the MERGE statement to deal with the records that don't match.
0 comments:
Post a Comment