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: 2You 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