PUBLIC:Re: Detecting the rowid of the row that caused a unique

From: Eugen Nyffeler <eugen.nyffeler_at_ubs.com>
Date: 1998/02/06
Message-ID: <34DB3474.F924234A@ubs.com>#1/1

Mordi wrote:
>
> From a pl/sql program i insert into a table and when i get the ORA-00001
> unique constraint error, i need to know which rowid in the table
> caused the unique constraint error.
> Why i need the rowid is not the isue now.
> Thanks

Hi

If you try to insert a row into table A and a constraint issus an error, the row with the invalid data is NOT inserted into table A and you can't get the rowid from table A.

Is the data-source an other table, then you can print(log) the rowid
of the data-source table during your exception handling.
Is the data-source a file then there's no rowid available.

If you need the incorrect data in you table and have to know the rowid do at the begin of your program and "alter table ...disable constraint", insert you data, then issue "alter table xyz enable constraint zxy exceptions into exception_table".
In this way you get the rowid of the rows with invalid data, but your constraint is disabled until the problem is solved and a new alter table enable constraint is done.

Hope this helps
eugen Received on Fri Feb 06 1998 - 00:00:00 CST