为什么这两个等价查询中只有一个会引发ORA-22806:不是对象或引用

当您不调用该函数时,此操作有效:

WITH FUNCTION do_something(
  arg varchar2
) RETURN varchar2
IS
BEGIN
  RETURN 'fff';
END;
SELECT *
FROM   SYS.ODCIVARCHAR2LIST('a', 'b', 'd')
PIVOT(
  ANY_VALUE(COLUMN_VALUE)
  FOR COLUMN_VALUE IN ('a', 'b', 'c', 'd')
);

和产出:

“a”	“b”	“c”	“是的”
A.	B	无效的	D

当您输入一个静态值而不是函数时,这会起作用:

SELECT *
FROM   SYS.ODCIVARCHAR2LIST('a', 'b', 'd')
PIVOT(
  ANY_VALUE('fff')
  FOR COLUMN_VALUE IN ('a', 'b', 'c', 'd')
);

并输出您的期望值:

“a”	“b”	“c”	“是的”
fff	fff	无效的	fff

使用内置函数 NVL2 同样有效的还有:

SELECT *
FROM   SYS.ODCIVARCHAR2LIST('a', 'b', 'd')
PIVOT(
  ANY_VALUE(NVL2(COLUMN_VALUE, 'fff', NULL))
  FOR COLUMN_VALUE IN ('a', 'b', 'c', 'd')
);

并输出您的期望值:

“a”	“b”	“c”	“是的”
fff	fff	无效的	fff

如果在 PIVOT :

WITH function do_something(
  arg varchar2
) RETURN varchar2
IS
BEGIN
  RETURN 'fff';
END;
SELECT *
FROM   (
  SELECT COLUMN_VALUE AS v,
         DO_SOMETHING(COLUMN_VALUE) AS dv
  FROM   SYS.ODCIVARCHAR2LIST('a', 'b', 'd')
)
PIVOT(
  ANY_VALUE(dv)
  FOR v IN ('a', 'b', 'c', 'd')
);

然后输出您的期望值:

“a”	“b”	“c”	“是的”
fff	fff	无效的	fff

如果在 支点 :

WITH function do_something(
  arg varchar2
) RETURN varchar2
IS
BEGIN
  RETURN 'fff';
END;
SELECT NVL2(a, DO_SOMETHING(a), NULL) AS a,
       NVL2(b, DO_SOMETHING(b), NULL) AS b,
       NVL2(c, DO_SOMETHING(c), NULL) AS c,
       NVL2(d, DO_SOMETHING(d), NULL) AS d
FROM   SYS.ODCIVARCHAR2LIST('a', 'b', 'd')
PIVOT(
  ANY_VALUE(COLUMN_VALUE)
  FOR COLUMN_VALUE IN ('a' AS a, 'b' AS b, 'c' AS c, 'd' AS d)
);

然后输出您的期望值:

A.	B	C	D
fff	fff	无效的	fff

但是,在pivot内调用函数会产生错误:

WITH function do_something(
  arg varchar2
) RETURN varchar2
IS
BEGIN
  RETURN 'fff';
END;
SELECT *
FROM   SYS.ODCIVARCHAR2LIST('a', 'b', 'd')
PIVOT(
  ANY_VALUE(DO_SOMETHING(COLUMN_VALUE))
  FOR COLUMN_VALUE IN ('a', 'b', 'c', 'd')
);

ORA-22806: not an object or REF

将函数移出子查询factoring子句并移到SQL作用域中仍然不起作用:

CREATE FUNCTION do_something(
  arg varchar2
) RETURN varchar2
IS
BEGIN
  RETURN 'fff';
END;
/

SELECT *
FROM   SYS.ODCIVARCHAR2LIST('a', 'b', 'd')
PIVOT(
  ANY_VALUE(DO_SOMETHING(COLUMN_VALUE))
  FOR COLUMN_VALUE IN ('a', 'b', 'c', 'd')
);

ORA-22806:不是对象或参考

显式强制转换和指定标识符也不适用:

SELECT a, b, c, d
FROM   (
  SELECT CAST(COLUMN_VALUE AS VARCHAR2(1)) AS v
  FROM   SYS.ODCIVARCHAR2LIST('a', 'b', 'd')
)
PIVOT(
  ANY_VALUE(CAST(DO_SOMETHING(v) AS VARCHAR2(3)))
  FOR v IN ('a' AS a, 'b' AS b, 'c' AS c, 'd' AS d)
);

ORA-22806:不是对象或参考

关于你的问题:

为什么这两个等价的查询中只有一个会引发 ORA-22806: not an object or REF

• 当使用 SELECT ... UNION ALL SELECT ... 但在使用 SELECT ... FROM TABLE(...) .
• 当调用的函数是内置函数而不是用户定义的函数时,它就可以工作。
• 当用户定义的函数被定义且未被调用时,它就工作了。
• 当在聚合外部调用用户定义的函数时,它会起作用。

在 VARRAY 中的用户定义函数和聚合 支点 子句有一个完美的风暴让SQL引擎感到困惑,并让它期望 OBJECT 或者 REF (对一位 对象 )然后提出错误。

如果您想知道“为什么”,那么需要咨询Oracle支持。

如果您想解决这个问题,请确保出现以下情况之一:

• 不要使用收集数据类型生成数据(从表或 选择联合所有选择。。。 ).
• 不要在内部使用用户定义的函数 支点 的聚合(在聚合之前或之后使用它们,或使用等效的内置函数)。

db<>不停摆弄 here