WITH temp1 (i, j, name1, name2)
AS
(
SELECT
1,
1,
'nameA_1',
'nameB_1'
FROM dual
UNION ALL
SELECT
CASE
WHEN MOD(j + 1,10) != 0 THEN i
ELSE i + 1
END,
j + 1,
'nameA_' || i,
'nameB_' || (j + 1)
FROM temp1
WHERE
i <= 3
AND
j <= 3 * 10
)
SELECT *
FROM temp1| I | J | NAME1 | NAME2 | 1 | 1 | nameA_1 | nameB_1 | 1 | 2 | nameA_1 | nameB_2 | 1 | 3 | nameA_1 | nameB_3 | 1 | 4 | nameA_1 | nameB_4 | 1 | 5 | nameA_1 | nameB_5 | 1 | 6 | nameA_1 | nameB_6 | 1 | 7 | nameA_1 | nameB_7 | 1 | 8 | nameA_1 | nameB_8 | 1 | 9 | nameA_1 | nameB_9 | 2 | 10 | nameA_1 | nameB_10 | 2 | 11 | nameA_2 | nameB_11 | 2 | 12 | nameA_2 | nameB_12 | 2 | 13 | nameA_2 | nameB_13 | 2 | 14 | nameA_2 | nameB_14 | 2 | 15 | nameA_2 | nameB_15 | 2 | 16 | nameA_2 | nameB_16 | 2 | 17 | nameA_2 | nameB_17 | 2 | 18 | nameA_2 | nameB_18 | 2 | 19 | nameA_2 | nameB_19 | 3 | 20 | nameA_2 | nameB_20 | 3 | 21 | nameA_3 | nameB_21 | 3 | 22 | nameA_3 | nameB_22 | 3 | 23 | nameA_3 | nameB_23 | 3 | 24 | nameA_3 | nameB_24 | 3 | 25 | nameA_3 | nameB_25 | 3 | 26 | nameA_3 | nameB_26 | 3 | 27 | nameA_3 | nameB_27 | 3 | 28 | nameA_3 | nameB_28 | 3 | 29 | nameA_3 | nameB_29 | 4 | 30 | nameA_3 | nameB_30 |
|---|
WITH temp1 (i, j, name1, name2)
AS
(
SELECT
1,
1,
'nameA_1',
'nameB_1'
FROM dual
UNION ALL
SELECT
CASE
WHEN MOD(j + 1,10) != 0 THEN i
ELSE i + 1
END,
j + 1,
'nameA_' || i,
'nameB_' || (j + 1)
FROM temp1
WHERE
i <= 3
)
SELECT *
FROM temp1ORA-32044: cycle detected while executing recursive WITH queryMore Details: https://docs.oracle.com/error-help/db/ora-32044