create table TEST23(uq INTEGER); -- target table
create table TEST_U(id INTEGER); -- helper table

create or replace trigger tr_test23
  before insert on test23  
  for each row
declare
  PRAGMA AUTONOMOUS_TRANSACTION;
  n integer;
  l integer := -1;
begin
  if :new.uq is null then raise VALUE_ERROR; end if; -- better ORA-01400: cannot insert NULL

  -- check among commited values
  select count(*) into n from dual
   where exists (select 1 from test23 where uq = :new.uq);

  if n > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- take lock to avoid TOCTOU
  l := dbms_lock.request( 1001234567, dbms_lock.X_MODE );
  if l != 0 then raise TIMEOUT_ON_RESOURCE; end if;

  -- clean helper table from rows older than commited data
  delete from test_u u 
   where u.ora_rowscn <= nvl((select max(t.ora_rowscn) from test23 t),'Inf');
  commit;
   
  -- count matching uncommited values from other sessions
  select count(*) into n from test_u u where u.id = :new.uq;

  if n > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- this will remember uncommited value from this session
  insert into test_u (id) values(:new.uq);
  commit;

  l := dbms_lock.release( 1001234567 );
exception
  when others then
    if l = 0 then l := dbms_lock.release( 1001234567 ); end if;
    raise;
end tr_test23;