Connected to Oracle9i Enterprise Edition Release  9 . 2 . 0 . 7 . 0  
set serveroutput off
set echo on

create global temporary table ane_test_tmp( nom_otd number not null
                                          , name1 varchar2( 300 ) not null
                                          , inc number
                                          )
                              on commit delete rows;
Table created

create table ane_test( nom_otd number not null -- номер отдела
                     , name1 varchar2( 300 ) not null -- фамилия
);
Table created

create or replace package ane_test_p as
 function f( p_nom_otd number
           , p_name1 varchar2
           ) return varchar2
           deterministic;
end ane_test_p;
/
Package created
sho err
No errors for PACKAGE ANE_TEST_P

create or replace package body ane_test_p as
 upd_stage pls_integer :=  0 ;
 function get_persistent ( p_nom_otd number, p_name1 varchar2
                         ) return number deterministic
                         is pragma autonomous_transaction;
   cnt number;
 begin
   select count(*)
   into cnt
   from ane_test
   where nom_otd=p_nom_otd
     and name1=p_name1;
   return cnt;
 end;

 function calc_key (p_nom_otd number, p_name1 varchar2, offset number) return varchar2 is
 begin
   return p_nom_otd||'/#/'||p_name1||'/#/'||offset;
 end;

 function f( p_nom_otd number
           , p_name1 varchar2
           ) return varchar2
           deterministic is
   pers_cnt number;
   temp_cnt number;
   rzt varchar2( 2000 );
 begin
   select nvl(sum(inc), 0 )
   into temp_cnt
   from ane_test_tmp
   where nom_otd=p_nom_otd and name1=p_name1;

   pers_cnt := get_persistent(p_nom_otd, p_name1);

   if inserting then
     dbms_output.put_line('FBI: INSERTING: p_nom_sotr='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
     if pers_cnt + temp_cnt >=  3  then
       rzt := calc_key(p_nom_otd, p_name1, 1 ); -- DUP_VAL_ON_INDEX
       dbms_output.put_line('FBI: INSERTING: too many, key='||rzt);
     else
       rzt := calc_key(p_nom_otd, p_name1,pers_cnt + temp_cnt +  1 );
       dbms_output.put_line('FBI: INSERTING: OK, key='||rzt);
     end if;
     insert into ane_test_tmp values(p_nom_otd, p_name1,  1 );
   end if;

   if deleting then
     dbms_output.put_line('FBI: DELETING: p_nom_sotr='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
     delete ane_test_tmp where nom_otd = p_nom_otd and name1=p_name1 and rownum= 1  and inc= 1 ;
     if SQL%ROWCOUNT =  0  then
       insert into ane_test_tmp values(p_nom_otd, p_name1, - 1 );
     end if;
     rzt := calc_key(p_nom_otd, p_name1,pers_cnt + temp_cnt);
   end if;

   if UPDATING then
     dbms_output.put_line('FBI: UPDATING: p_nom_sotr='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
     if upd_stage =  0  then
       dbms_output.put_line('FBI: UPDATE DELETING: p_nom_sotr='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
       delete ane_test_tmp where nom_otd = p_nom_otd and name1=p_name1 and rownum= 1  and inc= 1 ;
       if SQL%ROWCOUNT =  0  then
         insert into ane_test_tmp values(p_nom_otd, p_name1, - 1 );
       end if;
       rzt := calc_key(p_nom_otd, p_name1,pers_cnt + temp_cnt);
       upd_stage :=  1 ;
     else
       dbms_output.put_line('FBI: UPDATE INSERTING: p_nom_sotr='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
       insert into ane_test_tmp values(p_nom_otd, p_name1,  1 );
       if pers_cnt + temp_cnt >=  3  then
         rzt := calc_key(p_nom_otd, p_name1, 1 ); -- DUP_VAL_ON_INDEX
       else
         rzt := calc_key(p_nom_otd, p_name1,pers_cnt + temp_cnt +  1 );
       end if;
       upd_stage :=  0 ;
     end if;
   end if;
   return rzt;
 end;
end ane_test_p;
/
Package body created

sho err
No errors for PACKAGE BODY ANE_TEST_P

--ВОТ ОНО!!!

create unique index ane_test_f_unq on ane_test (substr(ane_test_p.f(nom_otd,name1), 1 , 2000 ));

Index created

insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
 1  row inserted
insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
 1  row inserted
insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
 1  row inserted

--сейчас будет reject
insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
*insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q')
ORA- 00001 : unique constraint (ANE_TEST_F_UNQ) violated

delete ane_test where nom_otd= 1  and rownum< 2 ;
 1  row deleted

insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
 1  row inserted

insert into ane_test( nom_otd, name1) values( 1 ,'^Q!Q');
 1  row inserted

update ane_test set nom_otd=nom_otd+ 1 ;
 4  rows updated

commit;
Commit complete

insert into ane_test( nom_otd, name1) values( 3 ,'Q!Q');
 1  row inserted

--сейчас будет reject

update ane_test set nom_otd=nom_otd+ 1  where nom_otd <  3 ;
*update ane_test set nom_otd=nom_otd+ 1  where nom_otd <  3 
ORA- 00001 : unique constraint (ANE_TEST_F_UNQ) violated

commit;
Commit complete

select * from ane_test;

   NOM_OTD NAME1
---------- -----------------------------------------------
          2  Q!Q
          2  Q!Q
          2  Q!Q
          2  ^Q!Q
          3  Q!Q

drop table ane_test;
Table dropped

drop package ane_test_p;
Package dropped

drop table ane_test_tmp;
Table dropped

...
   if NOT inserting and NOT updating and NOT deleting then
     dbms_output.put_line('FBI: REBUILDING INSERTING: p_nom_otd='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
     if /*pers_cnt +*/ temp_cnt >=  3  then
       rzt := calc_key(p_nom_otd, p_name1, 1 ); -- DUP_VAL_ON_INDEX
       dbms_output.put_line('FBI: REBUILDING INSERTING: too many, key='||rzt);
     else
       rzt := calc_key(p_nom_otd, p_name1,/*pers_cnt +*/ temp_cnt +  1 ); 
       dbms_output.put_line('FBI: REBUILDING INSERTING: OK, key='||rzt);
     end if;
     insert into ane_test_tmp values(p_nom_otd, p_name1,  1 );
   end if;   
...