Finding Circular References in a Table

Here's how to use a hierarchical query to detect circular references when inserting new rows into a self-referencing table.

If the entry causes a circular reference, I want to force a ROLLBACK.

PROCEDURE ...
IS
  e_infinite_loop EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_infinite_loop, -01436);   -- CONNECT BY loop in user data 

  v_cnt PLS_INTEGER; 

BEGIN

  SAVEPOINT circ;

  INSERT INTO dependency_link
              (   parent_id,   child_id
              )
  VALUES      ( p_parent_id, p_child_id
              );
              
--+-----------------------------------------------------------
--| You need to actually traverse the tree in order to 
--| detect circular dependencies.
--+-----------------------------------------------------------

            SELECT COUNT (*) cnt
              INTO v_cnt
              FROM dependency_link dl
        START WITH dl.child_id  = p_child_id
  CONNECT BY PRIOR dl.parent_id = dl.child_id;

EXCEPTION
  WHEN e_infinite_loop THEN
    put_long_line ('Loop detected!');

    ROLLBACK TO SAVEPOINT circ;
...