create table t(
   id int, 
   parent_id int, 
   constraint t_pk primary key(id), 
   constraint t_parent_fk foreign key (parent_id) references t(id) 
)
/
create index t_ix_par on t(parent_id,id)
/
insert into t select level,nullif(level,1)-1 from dual connect by level<=5;
commit;

SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1       NULL
         2          1
         3          2
         4          3
         5          4

create or replace procedure update_parent(pID int, pNewParent int) as
  cnt int;
  -- connect_by in user data:
  e_cycle exception;
  pragma exception_init(e_cycle, -1436 );
  
  procedure lock_parents(pLockID in int)
  as
     parents sys.odcinumberlist;
     cr sys_refcursor;
  begin
      select id
         bulk collect into parents
      from t
      start with id=pLockID
      connect by prior parent_id = id;

      open cr for select * from t where id in (select * from table(parents)) for update;
  end;
begin
   -- разрешаем иметь несколько ROOTs:
   if pNewParent is not null then
      -- залочим будущих родителей
      lock_parents(pNewParent);

      -- проверим на iscycle:
      select count(*)
         into cnt
      from t
      where connect_by_iscycle = 1
      or pID = id
      start with id=pNewParent
      connect by nocycle prior parent_id = id;

      if cnt>0 then
         raise e_cycle;
      end if;
   end if;
   -- update если все ок:
   update t set parent_id=pNewParent where id=pID;
end;
/

SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1
         2          1
         3          2
         4          3
         5          4

SQL> exec update_parent(3,5);
BEGIN update_parent(3,5); END;

*
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data
ORA-06512: at "XTENDER.UPDATE_PARENT", line 36
ORA-06512: at line 1

SQL> exec update_parent(3,1);

PL/SQL procedure successfully completed.

SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1
         2          1
         3          1
         4          3
         5          4

5 rows selected.

create or replace function check_parents(pID in int) return int deterministic as
  -- connect_by in user data:
  e_cycle exception;
  pragma exception_init(e_cycle, -1436 );

  function check_parents(pLockID in int) return int
  as
  cnt int;
     parents sys.odcinumberlist;
  begin
      -- проверим на iscycle:
      select count(*) into cnt
      from t
      where connect_by_iscycle = 1
      start with id=pLockID
      connect by nocycle prior parent_id = id;

      return cnt;
  end;
begin
   if check_parents(pID) >0 then
      raise e_cycle;
   end if;
   return 1;
end;
/

create materialized view log on t with rowid, primary key;
create materialized view t_mv 
refresh complete on commit
as
select check_parents(id) chk,id,parent_id from t
/

SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1
         2          1
         3          2
         4          3
         5          4

5 rows selected.

SQL> update t set parent_id=5 where id=3;

1 row updated.

SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1
         2          1
         3          5
         4          3
         5          4

5 rows selected.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-01436: CONNECT BY loop in user data
ORA-06512: at "XTENDER.CHECK_PARENTS", line 13
ORA-06512: at "XTENDER.CHECK_PARENTS", line 32


SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1
         2          1
         3          2
         4          3
         5          4

5 rows selected.

create or replace function check_parents_virtual(pID in int, pParentId in int) return int deterministic as
  -- connect_by in user data:
  e_cycle exception;
  pragma exception_init(e_cycle, -1436 );

  function check_parents(pLockID in int) return int
  as
  cnt int;
     parents sys.odcinumberlist;
  begin
      -- проверим на iscycle:
      select count(*) into cnt
      from t
      where connect_by_iscycle = 1
      start with id=pLockID
      connect by nocycle prior parent_id = id;

      return cnt;
  end;
begin
   if check_parents(pID) >0 then
      raise e_cycle;
   end if;
   return 1;
end;
/
alter table t
  add chk number(1) generated always as (check_parents_virtual(id,parent_id));
alter table t
  add constraint t_chk check (chk=1) INITIALLY DEFERRED DEFERRABLE;
 
create index t_ix_chk on t(chk);

SQL> update t set parent_id=5 where id=3;
update t set parent_id=5 where id=3
*
ERROR at line 1:
ORA-04091: table XTENDER.T is mutating, trigger/function may not see it
ORA-06512: at "XTENDER.CHECK_PARENTS_VIRTUAL", line 11
ORA-06512: at "XTENDER.CHECK_PARENTS_VIRTUAL", line 27