Testcase against 19c
CREATE TABLE tab1 (
num_array apex_t_number
, num_string varchar2(25)
)
nested table num_array store as num_array_ntab return as locator ;
-- no unique index by default
create unique index uki1_tab1 on tab1 (num_string) ;
truncate table tab1 ;
INSERT INTO demo_bugs.tab1 ( num_array, num_string ) VALUES ( apex_t_number(1,2,3), '1,2,3' ) ;
INSERT INTO demo_bugs.tab1 ( num_array, num_string ) VALUES ( apex_t_number(2,3,4), '2,3,4' ) ;
INSERT INTO demo_bugs.tab1 ( num_array, num_string ) VALUES ( apex_t_number(5,6,7), '5,6,7' ) ;
set timing on
set echo on
select a.*, b.*
from tab1 a inner join tab1 b on ( a.num_array multiset intersect b.num_array ) = apex_t_number() ;
with root_rcte ( num_array, num_string ) as
( select r.num_array, r.num_string from tab1 r
union all
select b.num_array, b.num_string from tab1 b inner join root_rcte r1 on ( r1.num_array multiset intersect b.num_array ) = apex_t_number()
)
select * from root_rcte ;
Results
SQL> select a.*, b.*
2 from tab1 a inner join tab1 b on ( a.num_array multiset intersect b.num_array ) = apex_t_number() ;
NUM_ARRAY NUM_STRING NUM_ARRAY NUM_STRING
------------------------------ ------------------------- ------------------------------ -------------------------
WWV_FLOW_T_NUMBER(1, 2, 3) 1,2,3 WWV_FLOW_T_NUMBER(5, 6, 7) 5,6,7
WWV_FLOW_T_NUMBER(2, 3, 4) 2,3,4 WWV_FLOW_T_NUMBER(5, 6, 7) 5,6,7
WWV_FLOW_T_NUMBER(5, 6, 7) 5,6,7 WWV_FLOW_T_NUMBER(1, 2, 3) 1,2,3
WWV_FLOW_T_NUMBER(5, 6, 7) 5,6,7 WWV_FLOW_T_NUMBER(2, 3, 4) 2,3,4
Elapsed: 00:00:29.562
SQL>
SQL> with root_rcte ( num_array, num_string ) as
2 ( select r.num_array, r.num_string from tab1 r
3 union all
4 select b.num_array, b.num_string from tab1 b inner join root_rcte r1 on ( r1.num_array multiset intersect b.num_array ) = apex_t_number()
5 )
6 select * from root_rcte ;
Error starting at line : 38 in command -
with root_rcte ( num_array, num_string ) as
( select r.num_array, r.num_string from tab1 r
union all
select b.num_array, b.num_string from tab1 b inner join root_rcte r1 on ( r1.num_array multiset intersect b.num_array ) = apex_t_number()
)
select * from root_rcte
Error at Command Line : 38 Column : 1
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected UDT got APEX_230200.WWV_FLOW_T_NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Elapsed: 00:00:01.056