Re: DUP_VAL_ON_INDEX Problem

A thought, which is not an answer to your question. If you have a large number of rows to process, and the fraction of pre-existing rows is more than about 1 in 15 then it is probably more efficient to do:

update record where
if no row updated

    insert row

because of the overhead that Oracle
suffers when preparing discovering
that the key is duplicate, rolling back and building the exception.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Mark G wrote in message <377b84f0.0_at_145.227.194.253>...
>1. I know it wasn't the exact code you posted but your little example had
>its end's in the wrong place. Try this structure.
>
>while x%found loop
> begin
> insert (.....)
> EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
> update(....
> end;
> fetch another record
>end loop
>
>2. If that doesn't help, are there any other trggers on the table which
may
>violate some constraint when you do an update?
>
>HTH
>
>Mark
Received on Thu Jul 01 1999 - 15:38:11 CDT