How to get distinct items and number of occurrences on a nested table?

Question

I have a nested table that includes a list of emails, which could be inside several times. The problem is I need to get the distinct list of items, and the number of times it appears on the list.

|    emails    |
| ------------ |
| [email protected]   |
| [email protected]   |
| [email protected]   |
| [email protected]   |
| [email protected]   |
| [email protected]   |
| [email protected]   |
| [email protected]   |
| [email protected]   |
| [email protected]   |
| [email protected]   |

Ideally, my result would be a table or an output that tells me the following:

| Email | Number |
| ---------- | - |
| [email protected] | 3 |
| [email protected] | 3 |
| [email protected] | 4 |
| [email protected] | 1 |

To select from a table I would use a select statement, but if I try this in my code I get an error "ORA- 00942: table or view does not exist" same with even a simple select from emails table so I'm just guessing you can't use select on nested tables that way.

The nested table was created like this:

type t_email_type is table of varchar2(100);
  t_emails t_email_type := t_email_type();

and then populated under a loop that adds an email for each iteration of the loop:

t_emails.extend;
t_emails(t_emails.LAST) := user_r.email;

Answer 1

I tried to do what you described so far; here you go:

Table that contains e-mail addresses (some of them are duplicates):

SQL> select * from test_mails;

EMAIL
----------
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

Type you created; I think you used type within your PL/SQL procedure. That won't work if it is a function which is supposed to return result of that type because it then must be created at SQL level, so - that's what I'm doing:

SQL> create or replace type t_email_type is table of varchar2(100);
  2  /

Type created.

Function: FOR loop selects e-mail addresses from the table and puts them into t_emails. What you're interested in is what follows in lines #12-14 as it shows how to return the result:

SQL> create or replace function f_test
  2    return t_email_type
  3  is
  4    t_emails t_email_type := t_email_type();
  5    retval t_email_type;
  6  begin
  7    for user_r in (select email from test_mails) loop
  8      t_emails.extend;
  9      t_emails(t_emails.last) := user_r.email;
 10    end loop;
 11
 12    select distinct column_value
 13      bulk collect into retval
 14      from table(t_emails);
 15    return retval;
 16  end;
 17  /

Function created.

OK, let's test it:

SQL> select * from table(f_test);

COLUMN_VALUE
--------------------------------------------------------------------------------
[email protected]
[email protected]
[email protected]

SQL>

Distinct addresses; that's what you asked for.

Answer 2

Try it like this:

SET SERVEROUTPUT ON
Create or replace type t_email_type is table of varchar2(100);
/
Declare     --  Outer block - just to populate variable emails of type t_email_type
    Cursor c_emails IS          
        Select '[email protected]' "EMAIL" From Dual Union All
        Select '[email protected]' "EMAIL" From Dual Union All
        Select '[email protected]' "EMAIL" From Dual Union All
        Select '[email protected]' "EMAIL" From Dual Union All
        Select '[email protected]' "EMAIL" From Dual Union All
        Select '[email protected]' "EMAIL" From Dual Union All
        Select '[email protected]' "EMAIL" From Dual Union All
        Select '[email protected]' "EMAIL" From Dual Union All
        Select '[email protected]' "EMAIL" From Dual Union All
        Select '[email protected]' "EMAIL" From Dual Union All
        Select '[email protected]' "EMAIL" From Dual;
    email     VarChar2(100);
    emails    t_email_type := t_email_type();
    i         Number(3) := 0;
Begin
    OPEN c_emails;
    LOOP
        FETCH c_emails Into email;
        EXIT WHEN c_emails%NOTFOUND;
        i := i + 1;
        emails.extend;
        emails(i) := email;
    END LOOP;
    CLOSE c_emails;
    --  Inner block - get distinct emails and number of appearances from variable emails of type t_email_type
    Declare
        Cursor c Is
            Select COLUMN_VALUE "EMAIL", Count(*) "NUM_OF_APPEARANCES"
            From    TABLE(emails)
            Group By COLUMN_VALUE
            Order By COLUMN_VALUE;
            cSet    c%ROWTYPE;
            i       Number(3) := 0;
    Begin
        OPEN c;
        LOOP
            FETCH c Into cSet;
            EXIT WHEN c%NOTFOUND;
            i := i + 1;
            If i = 1 Then
                DBMS_OUTPUT.PUT_LINE(RPAD('EMAIL', 20, ' ') || ' ' || LPAD('NUM_OF_APPEARANCES', 20, ' '));
                DBMS_OUTPUT.PUT_LINE(RPAD('-', 20, '-') || ' ' || LPAD('-', 20, '-'));
            End If;
            DBMS_OUTPUT.PUT_LINE(RPAD(cSet.EMAIL, 20, ' ') || ' ' || LPAD(cSet.NUM_OF_APPEARANCES, 20, ' '));
        END LOOP;
        CLOSE c;
    End;
End;
/*  R e s u l t :
anonymous block completed
EMAIL                  NUM_OF_APPEARANCES
-------------------- --------------------
[email protected]                              3
[email protected]                              3
[email protected]                              4
[email protected]                              1
*/

Outer block of this code is here just to generate data and to insert it into emails variable that is of type you have defined (t_email_type). You, probably, need just the inner block to get you list of emails with number of appearances within the table type.

SQL that gives you expected result is, actualy, the cursor in the inner block:

Select COLUMN_VALUE "EMAIL", Count(*) "NUM_OF_APPEARANCES"
From    TABLE(emails)
Group By COLUMN_VALUE
Order By COLUMN_VALUE;

Result:

EMAIL	NUM_OF_APPEARANCES
[email protected]	3
[email protected]	3
[email protected]	4
[email protected]	1

Regards...