How to insert data in table with constraint on column? - in parallel queries
Commit as soon as possible. Otherwise, just to give you an idea:

create table t(n number, CONSTRAINT n_unq UNIQUE (n));

create or replace function f(pn number) return number
is
  pret number;
  l_ret number;
begin
  select n into pret from t where n = pn;
  return pret;
exception when no_data_found then
  l_ret := dbms_lock.request(id =&gt; pn, timeout =&gt; 0, release_on_commit =&gt; true);
  dbms_output.put_line('lock return code: ' || l_ret);
  if l_ret = 0 then 
    insert into t(n) values (pn);
    return pn;
  else
    return -1; -- can not insert
  end if;
end;
/


Session 1:

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

lock return code: 0
f return code: 1

select * from t;

         N
----------
         1


Session 2:

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

lock return code: 1
f return code: -1

select * from t;

no rows selected


Session 1:

commit;


Session 2:

select * from t;

         N
----------
         1

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

f return code: 1


I am not sure this will really help you, the ideal solution would be to prevent multiple sessions trying to insert the same value in long transactions.