CTE inside Table-valued function in Oracle

November 14, 2022

I’m trying to write a Table-valued function in oracle, which contains a CTE.
I’ve been able to do it in SQL Server in this way:

ALTER FUNCTION [dbo].[region_parents]
(   
    @regionId INT
)
RETURNS TABLE 
AS
RETURN 
(
    WITH cte AS
                        (
                          SELECT id, owner_region_id FROM regions WHERE id =  @regionId
 
                          UNION ALL
 
                          SELECT r.id, r.owner_region_id
                          FROM cte INNER JOIN 
                            regions r ON cte.owner_region_id = r.id
                        )

    SELECT id
    FROM cte
)

This is needed in order to call it in a cross apply:

SELECT *
FROM shops s
...
...
...

INNER JOIN locations l ON s.location_id = l.id
LEFT JOIN location_criteria lc ON lc.location_id = l.id

CROSS APPLY region_parents(l.region_id) r

In Oracle, I’ve tried to do it in this way, using User-Defined Datatypes:


CREATE OR REPLACE TYPE TABLE_RES_OBJ AS OBJECT (
     id                  NUMBER
);


CREATE OR REPLACE TYPE TABLE_RES AS TABLE OF TABLE_RES_OBJ;


CREATE OR REPLACE FUNCTION region_parents (regionId IN INTEGER)
RETURN TABLE_RES
IS
cteresult TABLE_RES;
BEGIN
    WITH cte(id, owner_region_id) AS
                        (
                          SELECT id AS id, owner_region_id AS owner_region_id FROM regions WHERE id = regionId
 
                          UNION ALL
 
                          SELECT r.id, r.owner_region_id
                          FROM cte INNER JOIN 
                            regions r ON cte.owner_region_id = r.id
                        )

    SELECT TABLE_RES(id)
    BULK COLLECT INTO cteresult
    FROM cte;
    RETURN cteresult;
END;
/

The problem is that I obtain the following error:

PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER

I’ve also tried to achieve it in this way, without success.