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.
CodePudding user response:
Seems to be a simple distraction: "SELECT TABLE_RES(id)" should be "SELECT TABLE_RES_OBJ(id)".
CodePudding user response:
It isn't anything to do with the CTE. You're trying to select a table object, not a simple object that is an element/row in that table. This:
SELECT TABLE_RES(id)
BULK COLLECT INTO cteresult
FROM cte;
should be:
SELECT TABLE_RES_OBJ(id)
BULK COLLECT INTO cteresult
FROM cte;