GROUP BY Nested Table Oracle DB 19 Error ORA-00932

Question

I have a Data

create or replace NONEDITIONABLE type TST_OBJ force
                   as table of varchar2(128)

And a table APPS that has a column 'Tst' of TST_OBJ type, as follows:

Column_Name	Type of data
Name	varchar
Tst	TST_OBJ
Offs	number

The issue comes when I try to SELECT and Group By the results as shown next:

select Tst, Offs from APPS group by Tst;

I'm getting error ORA-00932:

ORA-00932: inconsistent datatypes: expected - got TST_OBJ
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

Is it possible that Group By does not work with this Type of Data or is there a way to achieve it?

Answer 1

Documentation says:

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.

I guess bold part answers your question.

---

SQL> create or replace type TST_OBJ force
  2    as table of varchar2(128);
  3  /

Type created.

SQL> create table apps
  2    (name varchar2(20),
  3     tst  tst_obj,                  --> here it is
  4     offs number)
  5  nested table tst store as tst_obj_tab;

Table created.

SQL> insert into apps values ('Little', tst_obj('A'), 1);

1 row created.

SQL> insert into apps values ('Foot'  , tst_obj('A', 'B', 'C'), 2);

1 row created.

Queries:

SQL> select tst, offs
  2  from apps;

TST                                            OFFS
---------------------------------------- ----------
TST_OBJ('A')                                      1
TST_OBJ('A', 'B', 'C')                            2

SQL> select tst, offs, count(*)
  2  from apps
  3  group by tst, offs;
group by tst, offs
         *
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected - got SCOTT.TST_OBJ


SQL>