oracle sql: collect aggregation

Question

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?

To begin with, why are you declaring a type for `number(19,0)`. Second, you need to present how `rechnungsdaten` looks like (table definition). — FDavidov, Jul 20 '18 at 07:29
I corrected that, now it is a list of numbers: TYPE ids_type IS TABLE OF number(19, 0); Rechnungsdaten has many attributes, but status is a varchar2(35 char) and id is a number(19, 0) — Ewgenij Sokolovski, Jul 20 '18 at 07:33
Well, in your select statement you are attempting to convert a value (`r.id) into a table type. That, of course, is NOT a legal statement. — FDavidov, Jul 20 '18 at 07:45
I am doing it using the collect function like it is described in collect documentation, isn't that correct? — Ewgenij Sokolovski, Jul 20 '18 at 08:10
Well, if you want to populate a local table with the contents of a column in another table, you can simply use the sequence `INSERT INTO .... SELECT xyz FROM ...`. In ORACLE you can have nested tables (i.e. a table column can be a table in itself), and I guess that's the main usage of the `COLLECT` function (this is just a guess and I might be wrong). — FDavidov, Jul 20 '18 at 09:09

score 5 · Accepted Answer · answered Jul 20 '18 at 07:43

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;

Demo

oracle sql: collect aggregation

1 Answers1