I want to group my database entries by an attribute and to know which entries are in each group at the same time. I collect the ids of the grouped entries with Oracle COLLECT function COLLECT Function
DECLARE
TYPE ids_type IS TABLE OF number(19, 0);
ids ids_type;
BEGIN
select cast(collect(r.id) as ids_type) into ids from rechnungsdaten r group by r.status;
END;
But then I get the error:
Error report -
ORA-06550: Line 5, Column 44:
PL/SQL: ORA-00902: Invalid datatype
ORA-06550: Line 5, Column 5:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
What is wrong here?
You cannot use COLLECT
function on a type declared in a PL/SQL anonymous block.
You have other options like
Create a database type and run your collect query.
create or replace TYPE ids_type IS TABLE OF number(19, 0);
SELECT
r.status,
CAST(COLLECT(r.id) AS ids_type)
FROM
rechnungsdaten r
GROUP BY
r.status;
Use a simple LISTAGG
query to see the list of ids as a string
SELECT
r.status,
LISTAGG(r.id,',') WITHIN GROUP(
ORDER BY
id
)
FROM
rechnungsdaten r
GROUP BY
r.status;