Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE MATERIAL( ID_PRODUCT_MATERIAL int, PRODUCTION int, ID_MATERIAL int) 
begin
INSERT INTO MATERIAL (ID_PRODUCT_MATERIAL, PRODUCTION, ID_MATERIAL) VALUES (1,2,2);
INSERT INTO MATERIAL (ID_PRODUCT_MATERIAL, PRODUCTION, ID_MATERIAL) VALUES (1,2,3);
INSERT INTO MATERIAL (ID_PRODUCT_MATERIAL, PRODUCTION, ID_MATERIAL) VALUES (1,2,4);
INSERT INTO MATERIAL (ID_PRODUCT_MATERIAL, PRODUCTION, ID_MATERIAL) VALUES (2,1,9);
INSERT INTO MATERIAL (ID_PRODUCT_MATERIAL, PRODUCTION, ID_MATERIAL) VALUES (2,1,10);
INSERT INTO MATERIAL (ID_PRODUCT_MATERIAL, PRODUCTION, ID_MATERIAL) VALUES (3,1,8);
INSERT INTO MATERIAL (ID_PRODUCT_MATERIAL, PRODUCTION, ID_MATERIAL) VALUES (8,1,1);
end;
/
1 rows affected

WITH CTE (ID_PRODUCT_MATERIAL,PRODUCTION,ID_MATERIAL) AS (
  SELECT ID_PRODUCT_MATERIAL,
  PRODUCTION,
  ID_MATERIAL
  PRODNR_MAT
  FROM MATERIAL

  UNION ALL

  SELECT m.ID_PRODUCT_MATERIAL,
  M.PRODUCTION,
  M.ID_MATERIAL
  FROM MATERIAL M
  INNER JOIN CTE C
  ON M.ID_PRODUCT_MATERIAL = C.ID_MATERIAL
  )
SELECT * FROM CTE;
ORA-32044: cycle detected while executing recursive WITH query

select m.*, sys_connect_by_path(id_material, '-'), connect_by_iscycle
  from material m
  connect by nocycle prior id_material = id_product_material


ID_PRODUCT_MATERIAL
PRODUCTION
ID_MATERIAL
SYS_CONNECT_BY_PATH(ID_MATERIAL,'-')
CONNECT_BY_ISCYCLE


1
2
2
-2
0

2
1
9
-2-9
0

2
1
10
-2-10
0

1
2
3
-3
0

3
1
8
-3-8
0

8
1
1
-3-8-1
1

1
2
2
-3-8-1-2
0

2
1
9
-3-8-1-2-9
0

2
1
10
-3-8-1-2-10
0

1
2
4
-3-8-1-4
0

1
2
4
-4
0

2
1
9
-9
0

2
1
10
-10
0

3
1
8
-8
0

8
1
1
-8-1
0

1
2
2
-8-1-2
0

2
1
9
-8-1-2-9
0

2
1
10
-8-1-2-10
0

1
2
3
-8-1-3
1

1
2
4
-8-1-4
0

8
1
1
-1
0

1
2
2
-1-2
0

2
1
9
-1-2-9
0

2
1
10
-1-2-10
0

1
2
3
-1-3
0

3
1
8
-1-3-8
1

1
2
4
-1-4
0

ID_PRODUCT_MATERIAL	PRODUCTION	ID_MATERIAL	SYS_CONNECT_BY_PATH(ID_MATERIAL,'-')	CONNECT_BY_ISCYCLE
1	2	2	-2	0
2	1	9	-2-9	0
2	1	10	-2-10	0
1	2	3	-3	0
3	1	8	-3-8	0
8	1	1	-3-8-1	1
1	2	2	-3-8-1-2	0
2	1	9	-3-8-1-2-9	0
2	1	10	-3-8-1-2-10	0
1	2	4	-3-8-1-4	0
1	2	4	-4	0
2	1	9	-9	0
2	1	10	-10	0
3	1	8	-8	0
8	1	1	-8-1	0
1	2	2	-8-1-2	0
2	1	9	-8-1-2-9	0
2	1	10	-8-1-2-10	0
1	2	3	-8-1-3	1
1	2	4	-8-1-4	0
8	1	1	-1	0
1	2	2	-1-2	0
2	1	9	-1-2-9	0
2	1	10	-1-2-10	0
1	2	3	-1-3	0
3	1	8	-1-3-8	1
1	2	4	-1-4	0