ORA-00932 inconsistent datatypes from collect call

I got really happy when I was able to aggregation the results in one line using cast(collect(value) as DBMSOUTPUT_LINESARRAY), as in this example:

select site, lote, material, cast(collect(value) as DBMSOUTPUT_LINESARRAY) AS valueagg
from table1
GROUP BY site, lote, material, status;

SITE   LOTE    MA VALUEAGG
------ ------- -- ----------------------------------------------------------------------------------
AAKI01 0000443 HW SYS.DBMSOUTPUT_LINESARRAY('9362','10k','1st','USERS','8','100MW','2','V2','CELL')

But when I try another aggregation over the results of that first query like cast(collect(valueagg) as DBMSOUTPUT_LINESARRAY) I get an error, as in this example:

SELECT site, material, cast(collect(valueagg) as DBMSOUTPUT_LINESARRAY) AS valueagg2
FROM table1_agg -- result of first query
GROUP BY site, material;

Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected - got -
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

I tried to convert to varchar2 even though it would limit the result - I am not worried by this, as the first 4000 chars of the string would be enough for me - but I wasn't able to do this.

Solution

DBMSOUTPUT_LINESARRAY is a varray of varchar2, so your first query is fine; the collect() call gets a collection of strings, and it can cast that to a the DBMSOUTPUT_LINESARRAY varray collection type.

Your second query is trying to create a collection of collections. As the documentation says:

If column is itself a collection, then the output of COLLECT is a nested table of collections.

What that collect() call is trying to create is a collection of collections of strings, and that is not the same type as DBMSOUTPUT_LINESARRAY. You could create your own type to support that:

create type demo_type as varray(100) of DBMSOUTPUT_LINESARRAY -- or as big as you need
/

And you can then do:

SELECT site, material, cast(collect(valueagg) as DEMO_TYPE) AS valueagg2
FROM table1_agg GROUP BY site, material;

SITE   MA VALUEAGG2
------ -- ---------------------------------------------------------------------------------------------------------
AAKI01 HW MYSCHEMA.DEMO_TYPE(PUBLIC.DBMSOUTPUT_LINESARRAY('9362','10k','1st','USERS','8','100MW','2','V2','CELL'))

Or perhaps showing that more obviously with different lote values so your first query gets two rows:

select site, lote, material, cast(collect(value) as DBMSOUTPUT_LINESARRAY) AS valueagg
from table1 GROUP BY site, lote, material;

SITE   LOTE    MA VALUEAGG
------ ------- -- ----------------------------------------------------------------------------------------------------
AAKI01 0000443 HW SYS.DBMSOUTPUT_LINESARRAY('9362','8','100MW','2','V2','CELL')                                       
AAKI01 0000444 HW SYS.DBMSOUTPUT_LINESARRAY('USERS','10k','1st')

SELECT site, material, cast(collect(valueagg) as DEMO_TYPE) AS valueagg2
FROM table1_agg GROUP BY site, material;

SITE   MA VALUEAGG2
------ -- ---------------------------------------------------------------------------------------------------------------------------------------
AAKI01 HW MYSCHEMA.DEMO_TYPE(PUBLIC.DBMSOUTPUT_LINESARRAY('9362','8','100MW','2','V2','CELL'),PUBLIC.DBMSOUTPUT_LINESARRAY('USERS','10k','1st'))

I'm not sure that's really what you want; I suspect you want a single collection containing the values from both aggregated values. That would mean unnesting them and recombining them:

SELECT site, material, cast(collect(column_value) as DBMSOUTPUT_LINESARRAY) AS valueagg2
FROM table1_agg CROSS JOIN TABLE(valueagg) v
GROUP BY site, material;

SITE   MA VALUEAGG2
------ -- ----------------------------------------------------------------------------------
AAKI01 HW SYS.DBMSOUTPUT_LINESARRAY('9362','1st','10k','USERS','CELL','V2','2','100MW','8')

It might be simpler to just go back to the raw data and aggregate from scratch, since you're using a varray type which can't be used for a multiset operation.