Recursive CTE throws temp tablespace is empty error

h2oraclesql-standardstring-aggregation

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.

Best Answer

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.

Related Question