Hello
I am trying to use the new functionality from 12c "Functions in the WITH Clause" (
https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1#functions ). In conjunction with the UNION clause, I get the error ORA-00904 related to the function name.
Below is a very simplified example of what I am trying to do:
WITH
FUNCTION tmp_func RETURN XMLTYPE IS
BEGIN
RETURN XMLTYPE('<ROW><VALUE>TEST</VALUE></ROW>');
END;
temp_1 AS (
SELECT t.result
FROM XMLTABLE('/'
PASSING tmp_func()
COLUMNS result VARCHAR2(4) PATH 'VALUE'
) t
)
SELECT t1.result FROM temp_1 t1
UNION ALL
SELECT t2.result FROM temp_1 t2
I am getting an error:
ORA-00904: "TMP_FUNC": invalid identifier
Please give me a hint.
This appears to be a limitation of using XMLTable - speak to support if you want this addressed.
In the meantime, you can get around this by:
Make the function a stand-alone object or part of a packageCREATE OR REPLACE FUNCTION f RETURN XMLTYPE IS
BEGIN
RETURN XMLTYPE('<ROW><VALUE>TEST</VALUE></ROW>');
END f;
/
WITH x as (
SELECT t.result
FROM XMLTABLE('/'
PASSING f()
COLUMNS result VARCHAR2(4) PATH 'VALUE'
) t
)
SELECT * FROM x
UNION ALL
SELECT * FROM x;
RESULT
TEST
TEST Reference the function in the final select, not an intermediate WITH clause:WITH
FUNCTION tmp_func RETURN XMLTYPE IS
BEGIN
RETURN XMLTYPE('<ROW><VALUE>TEST</VALUE></ROW>');
END;
SELECT t.result
FROM XMLTABLE('/'
PASSING tmp_func
COLUMNS result VARCHAR2(4) PATH 'VALUE'
) t
UNION ALL
SELECT t.result
FROM XMLTABLE('/'
PASSING tmp_func
COLUMNS result VARCHAR2(4) PATH 'VALUE'
) t;
/
RESULT
TEST
TEST Just use SQL!WITH x AS (
SELECT XMLTYPE('<ROW><VALUE>TEST</VALUE></ROW>') x FROM dual
), temp_1 AS (
SELECT t.result
FROM x, XMLTABLE('/'
PASSING x.x
COLUMNS result VARCHAR2(4) PATH 'VALUE'
) t
)
SELECT t1.result FROM temp_1 t1
UNION ALL
SELECT t2.result FROM temp_1 t2
/
RESULT
TEST
TEST