Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE alf_child_assoc (
  child_node_id  NUMERIC(10,0),
  parent_node_id NUMERIC(10,0)
);
INSERT INTO alf_child_assoc (child_node_id, parent_node_id) VALUES (2, 1);
1 rows affected

INSERT INTO alf_child_assoc (child_node_id, parent_node_id) VALUES (3, 2);
1 rows affected

INSERT INTO alf_child_assoc (child_node_id, parent_node_id) VALUES (1, 3);
1 rows affected

WITH cycles AS
(
    SELECT SYS_CONNECT_BY_PATH (child_node_id, ',') AS path, SYS_CONNECT_BY_PATH ( CASE WHEN child_node_id < CONNECT_BY_ROOT child_node_id THEN child_node_id END, ','
) AS less_path
FROM alf_child_ASSOC
WHERE CONNECT_BY_ROOT parent_node_id = child_node_id
CONNECT BY NOCYCLE parent_node_id = PRIOR child_node_id
)

SELECT *
FROM cycles
WHERE LTRIM (less_path, ',') IS NULL ;


PATH
LESS_PATH


,1,2,3
,,,


WITH cycles (path, root_id, child_node_id) AS (
  SELECT ',' || child_node_id,
         parent_node_id AS root_id,
         child_node_id
  FROM   alf_child_assoc
  WHERE  parent_node_id > child_node_id
UNION ALL
  SELECT c.path || ',' || a.child_node_id,
         c.root_id,
         a.child_node_id
  FROM   cycles c
         INNER JOIN alf_child_assoc a
         ON (c.child_node_id = a.parent_node_id)
  WHERE  a.parent_node_id != c.root_id
)
SELECT path
FROM   cycles
WHERE  child_node_id = root_id


PATH


,1,2,3