ORA-00932: несовместимые типы данных при вызове сбора

DBMSOUTPUT_LINESARRAY — это varray varchar2, так что ваш первый запрос в порядке; вызов collect() получает набор строк и может привести его к типу коллекции DBMSOUTPUT_LINESARRAY varray.

Ваш второй запрос пытается создать коллекцию коллекций. Как сказано в документации:

Если столбец сам является коллекцией, то результатом COLLECT является вложенная таблица коллекций.

То, что этот вызов collect() пытается создать, представляет собой набор наборов строк, и это не тот же тип, что и DBMSOUTPUT_LINESARRAY. Вы можете создать свой собственный тип для поддержки этого:

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

И тогда вы можете сделать:

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

Или, возможно, показать это более явно с разными значениями lote, чтобы ваш первый запрос получил две строки:

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

Я не уверен, что это действительно то, чего вы хотите; Я подозреваю, что вам нужна одна коллекция, содержащая значения из обоих агрегированных значений. Это означало бы их распаковку и рекомбинацию:

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

Возможно, было бы проще просто вернуться к необработанным данным и агрегировать их с нуля, поскольку вы используете тип varray, который нельзя использовать для операции с несколькими наборами.