Can I use an Oracle exception like dup val on index to detect if the record exists within the table?

by Enrique Segarra
(San Juan PR)

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_index exception 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.

For more help with PL/SQL see our tutorials or refer to the Oracle PL/SQL Language Reference manual. The syntax of the MERGE statement is available in the SQL Language Reference manual.

We recommend Smartsoft Computing for high quality, instructor-led PL/SQL training in the UK.

Comments for Can I use an Oracle exception like dup val on index to detect if the record exists within the table?

Average Rating starstarstarstarstar

Click here to add your own comments

Jan 18, 2011
Rating
starstarstarstarstar
Put a merge statement inside a PL/SQL cursor
by: Enrique Segarra

I am already using the Merge statement for updating the dimensions in my data mart, but I need an outside logic for loading the fact table. Can I put the Merge command inside a cursor and then call another procedure to process the surrogate keys?

You could put a MERGE statement inside a PL/SQL cursor but it would be pointless as the MERGE statement is designed to process multiple records not just one record at a time.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.