Using group by with an array in oracle
Restrictions on the GROUP BY Clause


  This clause is subject to the following restrictions:
  
  
  You cannot specify LOB columns, nested tables, or varrays as part of
  expr.
  The expressions can be of any form except scalar subquery expressions.
  If the group_by_clause references any object type columns, then the
  query will not be parallelized.
  


So here comes a cheap and dirty workaround, and no, I do not recommend using this in production.

drop table t1 purge;
create table t1(c1 SDO_ORDINATE_ARRAY);
insert into t1 values(SDO_ORDINATE_ARRAY(1, 1));
insert into t1 values(SDO_ORDINATE_ARRAY(1, 1));
insert into t1 values(SDO_ORDINATE_ARRAY(1, 2));
commit;

SQL&gt; select c1 from t1;

C1
------------------------
SDO_ORDINATE_ARRAY(1, 1)
SDO_ORDINATE_ARRAY(1, 1)
SDO_ORDINATE_ARRAY(1, 2)


This obviously fails:

select c1, count(*) from t1 group by c1;
select c1, count(*) from t1 group by c1
                                     *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got MDSYS.SDO_ORDINATE_ARRAY


But this works:

select ora_hash(c1), count(*) from t1 group by ora_hash(c1);

ORA_HASH(C1)   COUNT(*)
------------ ----------
   111675277          1
   390137838          2


Using this:

select
  (
    select c1 from t1
    where ora_hash(c1) = group_by_sq.ora_hash_c1
    fetch first 1 row only
  ) as c1,
  group_by_sq.t1_count
from
(
  select ora_hash(c1) as ora_hash_c1, count(*) as t1_count
  from t1  group by ora_hash(c1) 
) group_by_sq
;

C1                               T1_COUNT
------------------------------ ----------
SDO_ORDINATE_ARRAY(1, 2)                1
SDO_ORDINATE_ARRAY(1, 1)                2