Using group by with an array in oracle

Question

Is there a way to get an array column while using GROUP BY command? I am trying to get geometry from oracle database as array of coordinates, but get an error when try to use command GROUP BY

SELECT A.C_ID, 
       A.ID,
       COUNT(T.Text),
       LISTAGG(T.TEXT, '/// ') WITHIN GROUP (ORDER BY NULL) text, 
       P.GEOMETRY.sdo_ordinates p_geom
FROM G_P_THEME A 
LEFT JOIN G_POLYGON P on P.C_ID=A.C_ID and P.ID=A.ID 
LEFT JOIN  G_P_TEXT T on P.C_ID=T.C_ID and P.ID=T.ID 
WHERE (A.THEME_ID=440 OR A.THEME_ID=47) 
Group by A.C_ID, A.ID
Having (COUNT(T.Text) > 1);

it returns an error

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:

I know that this is because there is no reference to P.GEOMETRY in GROUP BY command if I add P.GEOMETRY.sdo_ordinates in GROUP BY, result will be

ORA-00932: inconsistent datatypes: expected - got MDSYS.SDO_ORDINATE_ARRAY
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 6 Column: 32

and if there is only P_GEOMETRY in GROUP BY

ORA-22901: cannot compare VARRAY or LOB attributes of an object type
22901. 00000 -  "cannot compare VARRAY or LOB attributes of an object type"
*Cause:    Comparison of VARRAY or LOB attributes of an object type
           was attempted in the absence of a MAP or ORDER method.
*Action:   Define a MAP or ORDER method for the object type.
Error at Line: 6 Column: 32

There is probably something that is needed to use in in SELECT to avoid using it in GROUP BY, but couldn't figure out what

Answer 1

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> 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

Answer 2

The best solution for this can be found at link. Reading from the database is much faster than with the solution that Balazs Papp suggested.

Just to mention in Balazs answer "fetch first 1 row only" need to be changed with ROWNUM=1 in older versions.