0

Oracle nested tables are allowed to be compared for equality, and two nested tables consider equal if they differ only in order of their elements.
This post is about my attempt to use nested tables comparison in a hierarchical query.

First, let's define a collection at schema level:

create type strings as table of varchar2(10);

We also need a table having column of the collection datatype:

create table t 
   nested table arr store as t_nt_arr
as
    select 1 id, strings('a','b') arr from dual 
    union all 
    select 2 id, strings('b','a') arr from dual
    union all 
    select 3 id, strings('c','d') arr from dual
    union all 
    select 4 id, strings('d','c') arr from dual;

Now look at the following query:

select sys_connect_by_path(id, '/') path 
from t
--start with id > 0       -- line #3
connect by nocycle 1=1
--and arr = prior arr     -- line #5
--and id > prior id       -- line #6

There are 3 lines here you may uncomment: #3,5,6.
If you uncomment any one of these lines - the query works.
If you uncomment any two of these lines - the query works.
If you uncomment all three lines - the query raises error at line 5:

ORA-00932: inconsistent datatypes: expected UDT got SCHEMANAME.STRINGS

But the same datatypes were pretty consistent a moment ago!

My questions:

  1. Does Oracle documentation allow using collection comparison inside a hierarchical query "connect-by" clause?
  2. Please explain the strange behavior of the query.

P.S.: I'm using Oracle version 19.19.0.0.0

ESkri
  • 1,461
  • 1
  • 1
  • 8
  • Same in 21.3.0.0.0. You should raise a ticket, the problem is probably more related to the nested table than to the connect by - transforming into a recursive CTE gives the same error. They are plenty of messages related to nested tables and ORA-00932 in ORACLE's forums. – p3consulting Jun 22 '23 at 06:17

0 Answers0