PL/SQL developer how to get the row that made the insert fail?

oracle, sql

Solution

From oracle 10g r2 you can use log errors clause of insert command to log errors in a separate table. Here is an example:

SQL> create table test_table(
  2    id   number primary key,
  3    col1 varchar2(7)
  4  )
  5  ;

Table created


-- creates a table for logging errors (table name will be prefaced with err$_)
SQL> begin dbms_errlog.create_error_log('TEST_TABLE'); end;
  2  /

PL/SQL procedure successfully completed

-- violates primary key constraint
SQL> insert into test_table(id, col1)
  2  (  select 1, level
  3      from dual
  4    connect by level <= 3)
  5    log errors reject limit unlimited;

1 row inserted

SQL> commit;

SQL> select * from test_table;

        ID COL1
---------- -------
         1 1


SQL> select * from err$_test_table;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                           ORA_ERR_ROWID$  ORA_ERR_OPTYP$ ORA_ERR_TAG$ ID  COL1
--------------- ------------------------------------------------------------------------------------------------------------
              1 ORA-00001: unique constraint (HR.SYS_C008315) violated  I                                            1     2
              1 ORA-00001: unique constraint (HR.SYS_C008315) violated  I                                            1     3

Problem

I'm doing a method that inserts into the table which has a unique column. What I don't know is if I can access the insert value that made the insert fail. For example: ``` table1(id,name, phone); name is unique. insert (1,a,123); insert (2,a,1234); ``` What I want is when I do the second insert I to return the id value '1' without having to recur to a query. Thank you in advance.

Original source