在pivot块中的聚合函数内部的函数导致错误ORA-22806: not an object or REF.

Question

代码运行良好

with 
function do_something(arg integer) return integer
is 
begin
return f(arg);
end;
t  as (SELECT 1 a FROM DUAL)
SELECT *
FROM t PIVOT (ANY_VALUE(do_something(a)) FOR a IN (1, 2));

但是这段代码没有

WITH 
function do_something(arg varchar2) return varchar2 is
begin
return 'fff';
end;
t (a) AS (SELECT COLUMN_VALUE FROM sys.odcivarchar2list ('a', 'b', 'd'))
SELECT *
FROM t PIVOT (ANY_VALUE(do_something(a)) FOR a IN ('a', 'b', 'c', 'd'));

[Error] execute (42:27): ORA-22806: not a object or REF

我不知道为什么会这样

代码

Answer 1

这与t子查询保理子句有关，与PIVOT或函数无关。

如果你这样做:

WITH 
function do_something(arg varchar2) return varchar2
is
begin
return 'fff';
end;
t (a) AS (
SELECT 'a' FROM DUAL UNION ALL
SELECT 'b' FROM DUAL UNION ALL
SELECT 'd' FROM DUAL
)
SELECT *
FROM   t
PIVOT (
ANY_VALUE(do_something(a))
FOR a IN ('a', 'b', 'c', 'd')
);

然后代码工作并输出:

<表类>ab' c '' d 'tbody><<tr>ffffff空fff