Thanks for the question, Avirup.

Asked: July 09, 2016 - 12:32 pm UTC

Last updated: May 27, 2019 - 6:28 am UTC

Version: 11G

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Is there a way to retrieve Unique records from a multidimensional collection?
DISTINCT and MULTISET operators seem to work on collections only when they have 1 field.

I have the below

TYPE TYP_TABLE_REC IS RECORD
(
SCHEMA_NAME VARCHAR2(30 char),
TABLE_NAME VARCHAR2(30 char)
);
TYPE nt_TBL_REC IS TABLE OF TYP_TABLE_REC;
v_nt_TBL_REC nt_TBL_REC := nt_TBL_REC();
v_nt_DIST_TBL_REC nt_TBL_REC := nt_TBL_REC();

v_nt_TBL_REC will has some duplicates.

I want to place the unique values from v_nt_TBL_REC into v_nt_DIST_TBL_REC.
Tried the below, but it's not giving the desired results.

v_nt_DIST_TBL_REC := v_nt_TBL_REC MULTISET union v_nt_TBL_REC;

and Connor said...

Yes, but I think you'll need to use the SQL-based object relational features to do it.

eg

SQL> create or replace type typ_table_rec is object
  2  (
  3  schema_name varchar2(30 char),
  4  table_name varchar2(30 char)
  5  );
  6  /

Type created.

SQL>
SQL> create or replace type nt_tbl_rec is table of typ_table_rec;
  2  /

Type created.

SQL>
SQL> set serverout on
SQL> declare
  2
  3  v_nt_tbl_rec      nt_tbl_rec := nt_tbl_rec();
  4  v_nt_dist_tbl_rec nt_tbl_rec := nt_tbl_rec();
  5
  6  begin
  7    v_nt_tbl_rec.extend(5);
  8    v_nt_tbl_rec(1) := typ_table_rec('a','a');
  9    v_nt_tbl_rec(2) := typ_table_rec('b','b');
 10    v_nt_tbl_rec(3) := typ_table_rec('c','c');
 11    v_nt_tbl_rec(4) := typ_table_rec('d','d');
 12    v_nt_tbl_rec(5) := typ_table_rec('a','a');
 13
 14    select v_nt_tbl_rec multiset union distinct v_nt_tbl_rec into v_nt_dist_tbl_rec from dual;
 15    dbms_output.put_line(v_nt_dist_tbl_rec.count);
 16
 17  end;
 18  /
4

PL/SQL procedure successfully completed.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Avirup Sen, July 11, 2016 - 3:20 am UTC

okay. SQL based is fine, but the SQL query, "select v_nt_tbl_rec multiset union distinct v_nt_tbl_rec into v_nt_dist_tbl_rec from dual;" is not giving the desired output!! The count is coming as 2. However, I am expecting 4 records in the output, as per your example.

('a','a')
('b','b')
('c','c')
('d','d')

It should be analogous to select distinct <COL1>, <COL2> FROM <TBL>

July 11, 2016 - 5:43 am UTC

That's bizarre - you can see from my demo I'm getting back the expected "4".

Can you post your entire example

Great Insight !!!!

Vengat Maran, September 06, 2018 - 7:54 am UTC

Great solution Connor, This answer really helps me to built my solution.

Appreciate your work !!!

Thanks & Regards
Vengat Maran

September 08, 2018 - 10:24 am UTC

glad to help

Jess, May 23, 2019 - 11:35 am UTC

Hi Connor,
In your example, a subset of full distinct records is selected.
What if we only want distinct values from one column?

Say you have a record of table rows

    type t_mytable_row is table of mytable%ROWTYPE index by pls_integer;
    v_mytable t_mytable_row;

and your column set contains a "description" column.

We want to grab all the rows (thinking select bulk collect into v_mytable).

Then we want to sub-select from that to get a distinct list of descriptions...

    type t_descr_array is table of varchar2(100);
    v_descriptions t_descr_array;

Can't work out how to sub-select distinct values of "description" from v_mytable into v_descriptions. Any ideas?

May 27, 2019 - 6:28 am UTC

If they are database types, just plain old SQL should be fine

SQL> create or replace type typ_table_rec is object
  2  (
  3  schema_name varchar2(30 char),
  4  table_name varchar2(30 char)
  5  );
  6  /

Type created.

SQL>
SQL> create or replace type nt_tbl_rec is table of typ_table_rec;
  2  /

Type created.

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

Type created.

SQL>
SQL> set serverout on
SQL> declare
  2    v_nt_tbl_rec      nt_tbl_rec := nt_tbl_rec();
  3    v_descriptions t_descr_array := t_descr_array();
  4  begin
  5    v_nt_tbl_rec.extend(5);
  6    v_nt_tbl_rec(1) := typ_table_rec('a','x');
  7    v_nt_tbl_rec(2) := typ_table_rec('b','x');
  8    v_nt_tbl_rec(3) := typ_table_rec('c','y');
  9    v_nt_tbl_rec(4) := typ_table_rec('d','y');
 10    v_nt_tbl_rec(5) := typ_table_rec('a','y');
 11
 12
 13    select distinct table_name bulk collect into v_descriptions
 14    from table(v_nt_tbl_rec);
 15    dbms_output.put_line(v_descriptions.count);
 16
 17
 18  end;
 19  /
2

PL/SQL procedure successfully completed.

SQL>
SQL>

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Great Insight !!!!

More to Explore

PL/SQL demos

PL/SQL docs

Distinct Values from Multidimensional Collection

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Great Insight !!!!

More to Explore

PL/SQL demos

PL/SQL docs