[SOLVED] ORA-00932 if collection used in recursive CTE in where clause

ORA-00932 if collection used in recursive CTE in where clause

I have recursive CTE with column of collection type (sys.ku$_vcnt is used here because it is built-in, problem can be reproduced for any collection type). When the collection column is used in recursive part of CTE in where clause, query fails with ORA-00932: inconsistent datatypes: expected UDT got SYS.KU$_VCNT error.

This is minimized example, in real case the collection content is inspected in where clause. Any occurence of collection seems enough for query to fail - for instance not null check as shown in following example:

with r (l, dummy_coll, b) as (
  select 1 as l, sys.ku$_vcnt(), null from dual
  union all
  select l + 1
       , r.dummy_coll
       , case when r.dummy_coll is not null then 'not null' else 'null' end as b
  from r
  where l < 5 and r.dummy_coll is not null
)
select * from r;

If and r.dummy_coll is not null is removed from where clause, the query succeeds. Occurence of collection in select clause is not problem (the b column shows the collection is actually not null).

Why does not it work and how to force Oracle to see collection column from previous recursion level in where clause?

Reproduced in Oracle 11 and Oracle 18 (dbfiddle).

Thanks!

Solution

Yeah that looks like a bug to me. A scalar select seems to be a workaround. Would that work in your case?

SQL> with r (l, dummy_coll, b) as (
  2    select 1 as l, sys.ku$_vcnt(), null from dual
  3    union all
  4    select l + 1
  5         , r.dummy_coll
  6         , case when r.dummy_coll is not null then 'not null' else 'null' end as b
  7    from r
  8    where l < 5 and ( select r.dummy_coll from dual ) is not null
  9  )
 10  select * from r;

L,DUMMY_COLL,B
1,KU$_VCNT(),
2,KU$_VCNT(),not null
3,KU$_VCNT(),not null
4,KU$_VCNT(),not null
5,KU$_VCNT(),not null