The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Thanks for the question, jagannath.

Asked: March 16, 2016 - 2:49 pm UTC

Last updated: March 17, 2016 - 10:41 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Hi Tom,
Is there any built in function in oracle for selecting distinct values of an associative array.

example : i want to store distinct records in rec_type2 from rectype

DECLARE
TYPE L_REC IS RECORD (A NUMBER, B VARCHAR2(20));
TYPE L_REC_TAB IS TABLE OF L_REC ;
REC_TYPE L_REC_TAB := L_REC_TAB() ;
REC_TYPE2 L_REC_TAB := L_REC_TAB() ;
BEGIN
REC_TYPE(1).A :=1;
REC_TYPE(1).B :='A';
REC_TYPE(2).A :=2;
REC_TYPE(2).B :='B';

REC_TYPE(3).A :=1;
REC_TYPE(3).B :='A';
REC_TYPE(4).A :=2;
REC_TYPE(4).B :='B';

FOR I IN REC_TYPE2.FIRST.. REC_TYPE2.LAST LOOP
DBMS_OUTPUT.PUT_LINE(REC_TYPE2(I));
END LOOP;
REC_TYPE2 := SET(REC_TYPE);
FOR I IN REC_TYPE2.FIRST.. REC_TYPE2.LAST LOOP
DBMS_OUTPUT.PUT_LINE(REC_TYPE2(I));
END LOOP;
END;

and Chris said...

The set operator doesn't work on nested tables of PL/SQL records.

You can use it on nested tables of single values:

declare
  type l_rec_tab is
    table of number;
  rec_type l_rec_tab  := l_rec_tab ( ) ;
  rec_type2 l_rec_tab := l_rec_tab ( ) ;
begin
  rec_type.extend(4);
  rec_type ( 1 ) :=1;
  rec_type ( 2 ) :=2;
  rec_type ( 3 ) :=1;
  rec_type ( 4 ) :=2;
  for i in rec_type.first.. rec_type.last
  loop
    dbms_output.put_line ( 'Orig: ' || rec_type ( i ) ) ;
  end loop;
  rec_type2 := set ( rec_type ) ;
  for i in rec_type2.first.. rec_type2.last
  loop
    dbms_output.put_line ( 'New: ' || rec_type2 ( i ) ) ;
  end loop;
end;
/

Orig: 1
Orig: 2
Orig: 1
Orig: 2
New: 1
New: 2

You can use set() on nested tables of object types. To do this, you have to call if from SQL:

create or replace type l_rec as object (
  a number,
  b varchar2 ( 20 ) 
);
/

create or replace type l_rec_tab is
  table of l_rec;
/


declare
  rec_type l_rec_tab  := l_rec_tab ( ) ;
  rec_type2 l_rec_tab := l_rec_tab ( ) ;
begin
  rec_type.extend(4);
  rec_type ( 1 )  := l_rec (1, 'A');
  rec_type ( 2 )  := l_rec (2, 'B');
  rec_type ( 3 )  := l_rec (1, 'A');
  rec_type ( 4 )  := l_rec (2, 'B');
  for i in rec_type.first.. rec_type.last
  loop
    dbms_output.put_line ('Orig: ' || rec_type ( i ).a ) ;
  end loop;
 
  select set(rec_type) into rec_type2 from dual;
 
  for i in rec_type2.first.. rec_type2.last
  loop
    dbms_output.put_line ('New: ' || rec_type2 ( i ).a ) ;
  end loop;
end;
/

Orig: 1
Orig: 2
Orig: 1
Orig: 2
New: 1
New: 2

We're not taking comments currently, so please try again later if you want to add a comment.

Breadcrumb

Question and Answer

You Asked

and Chris said...

More to Explore

PL/SQL demos

PL/SQL docs

selecting distinct values from an assocaitive array

Breadcrumb

Question and Answer

You Asked

and Chris said...

More to Explore

PL/SQL demos

PL/SQL docs