Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

How to sort Object Type : ORA-00932: inconsistent datatypes: expected ...

Jim DicksonMay 10 2024 — edited May 27 2024

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

This post has been answered by Stew Ashton on May 25 2024
Jump to Answer
Comments
Post Details
Added on May 10 2024
31 comments
396 views