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

Try using the DUP_VAL_ON_INDEX exception and select the row where the unique column(s) is equal to the values you are attempting to insert.

Eugen Nyffeler wrote:
>
> 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