Re: DUP_VAL_ON_INDEX Problem

I try not to get that fancy with exception logic.

Since you are dealing with pl/sql, why not do it like this.

1. Write a function that performs a simple query for the key value you supply.
2. If the row exists, return an indication from the function that the row exists. After the function call check the status. If the row exists, call a function that performs a update. If the row doesn't exist, call a function that performs an insert.

Maybe that isn't the grandest, most efficient, least resource intensive way, but it doesn't tend to fail for problems such as you describe.

Ken

yarch_at_hotmail.com wrote:

> I have a strange problem.......If you might have an idea of why it's
> happening, I would appreciate any information. my logic loops thru a
> cursor...if it's unique it inserts it...if it generates a
> dup_val_on_index it goes to the exception logic and updates...problem
> is
> when it does an insert, it also does an update in the exception
> block.......??????? so, for every
> record in the cursor it does the update....I looked at the index and
> the unique columns seem to be correct. can it be the way it's
> defined???
> here's the general code
> while x%found loop
> begin
> insert (.....)
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN
> begin
> update(....
> end
> fetch another record
> end
> end loop
Received on Thu Jul 01 1999 - 10:18:25 CDT