I want to use object types in window analytics ( partition by & order by ) - as can be done with list/array in Postgres/DuckDB
But baby steps - how to sort ( collate ) an object type / collections
Prep
drop table tab1 ;
DROP TYPE number_TT ;
CREATE TYPE number_TT AS TABLE OF number ;
CREATE TABLE tab1 (
num_string varchar2(25)
-- , num_array number_TT as ( generated always as (apex_string.split_numbers(num_string, ',')) ) virtual -- ORA-54003: specified data type is not supported for a virtual column
, num_array number_TT
)
nested table num_array store as num_array_ntab return as value
;
create unique index uki2_tab1 on tab1 (num_string) ;
truncate table tab1 ;
INSERT INTO demo_bugs.tab1 ( num_string, num_array ) VALUES ( '1,200,3', number_TT(1,200,3) ) ;
INSERT INTO demo_bugs.tab1 ( num_string, num_array ) VALUES ( '1,22,4', number_TT(1,22,4) ) ;
Code to run
select * from tab1 order by num_string asc ;
-- expect 1,200,3 before 1,2,4 because 22 > 200 when collated as string
select * from tab1 order by num_array asc ;
-- expect 1,22,4 before 1,200,3 because 22 < 200 when collated as number
Results (19c)
SQL> select * from tab1 order by num_string asc ;
NUM_STRING NUM_ARRAY
------------------------- ------------------------------
1,200,3 NUMBER_TT(1, 200, 3)
1,22,4 NUMBER_TT(1, 22, 4)
Elapsed: 00:00:00.357
SQL> -- expect 1,200,3 before 1,2,4 because 22 > 200 when collated as string
SQL> select * from tab1 order by num_array asc ;
Error starting at line : 40 in command -
select * from tab1 order by num_array asc
Error at Command Line : 40 Column : 29
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected - got DEMO_BUGS.NUMBER_TT
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Elapsed: 00:00:01.031
SQL> -- expect 1,22,4 before 1,200,3 because 22 < 200 when collated as number