Recursive CTE throws temp tablespace is empty error

Question

I need to aggregate the contents of multiple rows into a row as a delimited text. Here's the simplified table with sample data which represents what i want to do.

CREATE TABLE SPD_OWNER.EMP (
 EMPID NUMBER,
 NAME VARCHAR2(20),
 MGRID NUMBER,
 DEPT VARCHAR2(5)
);
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(1, 'GPM', NULL, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(2, 'SPM', 1, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(3, 'PM', 2, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(4, 'Dev1', 3, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(5, 'Dev2', 4, 'IT');
-- query which has issue
WITH tmp (rnum,dept,id) AS 
 (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
 (SELECT rnum, dept, id FROM tmp WHERE rnum=1
 UNION ALL
 SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1 
WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);

Am expecting below output.

5 IT 1/2/3/4/5

But running above query gives me an error SQL Error [25153] [99999]: ORA-25153: Temporary Tablespace is Empty

Isn't the condition tmp.rnum=cte.rnum+1 sufficient to break the recursive loop? Here's what am imagining to be happening.

1. 1st select (above union all) will select rnum=1
2. 1st recursive iteration will select tmp.rnum=cte.rnum+1, i.e. tmp.rnum=2
3. 2nd recursive iteration will select tmp.rnum=3
4. and so on till this condition is invalid, i.e. when cte.rnum=5 (because then tmp.rum=5+1 will not have matching record)

Am assuming that am getting the error since it's going into infinite loop. What am i missing?

Side Note: Am using Oracle DB, so i can easily do this using LISTAGG. But i also want to run this query in H2 DB. So i want to have a solution that uses standard sql. Am open to other ways to achieve the same result.

• user1822: Nitpicking: listagg() is in fact part of the SQL standard: modern-sql.com/feature/listagg
• Cyriac George: @Akina, i don't follow your recommendation. could you pls show me the modified query?
• Cyriac George: @a_horse_with_no_name, i will try this with H2 and provide an update in sometime.
• Cyriac George: @Akina, am still getting the same error. I had tried to remove the select max(rnum) part from my original query. The error was still there, which led me to think that issue is with infinite loop happening with the cte
• ibre5041: OT: why do you need H2? Just to run tests? Recursive CTEs might scale badly, use the simplest solution.

Answer 1

That error does not mean the temp tablespace got full, it means it is empty. Empty as no tempfile in it.

CREATE TABLE EMP (
 EMPID NUMBER,
 NAME VARCHAR2(20),
 MGRID NUMBER,
 DEPT VARCHAR2(5)
);
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(1, 'GPM', NULL, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(2, 'SPM', 1, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(3, 'PM', 2, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(4, 'Dev1', 3, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(5, 'Dev2', 4, 'IT');

Query:

SQL> WITH tmp (rnum,dept,id) AS
 (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
 (SELECT rnum, dept, id FROM tmp WHERE rnum=1
 UNION ALL
 SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1
 8 WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);
 RNUM DEPT ID
---------- ----- --------------------
 5 IT 1/2/3/4/5

Now if I drop the tempfile:

SQL> select file_id from dba_temp_files;
 FILE_ID
----------
 1
SQL> alter database tempfile 1 drop;
Database altered.
SQL> select file_id from dba_temp_files;
no rows selected
SQL> WITH tmp (rnum,dept,id) AS
 (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
 (SELECT rnum, dept, id FROM tmp WHERE rnum=1
 UNION ALL
 SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1
 8 WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);
WITH tmp (rnum,dept,id) AS
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty

Check DBA_TEMP_FILES and fix your temporary tablespace.

• Cyriac George: Thank you. I was going insane for a minute there. I tried the same query in another DB env and it worked there. It also worked on H2. So it looks like an issue with a specific DB (DEV) that I was playing with.