a function inside an aggregate function in a pivot block cause the error ORA-22806: not an object or REF

user_8458083 0
Viewed 33

This code works perfectly fine

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));

But this code doesn't

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] Execution (42: 27): ORA-22806: not an object or REF

I have no clues why it happens

code

oracle
1 Answers

It is something to do with the t subquery factoring clause and nothing to do with the PIVOT or the function.

If you do:

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')
);

Then the code works and outputs:

'a' 'b' 'c' 'd'
fff fff null fff

db<>fiddle here

user_15092640
Related