SQL> create table TEST23(uq INTEGER);

Table created.

SQL> create table TEST_U(id INTEGER);

Table created.

SQL>
SQL> create or replace trigger tr_test23
  2    before insert on test23
  3    for each row
  4  declare
  5    PRAGMA AUTONOMOUS_TRANSACTION;
  6    n integer;
  7    l integer := -1;
  8  begin
  9    if :new.uq is null then raise VALUE_ERROR; end if; -- better ORA-01400: cannot insert NULL
 10
 11    -- check among commited values
 12    select count(*) into n from dual
 13     where exists (select 1 from test23 where uq = :new.uq);
 14
 15    if n > 0 then raise DUP_VAL_ON_INDEX; end if;
 16
 17    -- take lock to avoid TOCTOU
 18    l := dbms_lock.request( 1001234567, dbms_lock.X_MODE );
 19    if l != 0 then raise TIMEOUT_ON_RESOURCE; end if;
 20
 21    -- clean helper table from rows older than commited data
 22    delete from test_u u
 23     where u.ora_rowscn <= nvl((select max(t.ora_rowscn) from test23 t),power(10,100));
 24    commit;
 25
 26    -- count matching uncommited values from other sessions
 27    select count(*) into n from test_u u where u.id = :new.uq;
 28
 29    if n > 0 then raise DUP_VAL_ON_INDEX; end if;
 30
 31    -- this will remember uncommited value from this session
 32    insert into test_u (id) values(:new.uq);
 33    commit;
 34
 35    l := dbms_lock.release( 1001234567 );
 36  exception
 37    when others then
 38      if l = 0 then l := dbms_lock.release( 1001234567 ); end if;
 39      raise;
 40  end tr_test23;
 41  /

Trigger created.

SQL>