plsql difference between two Nested Table collections

Question

I have two PLSQL Arrays of type nested table:

TYPE nested_typ IS TABLE OF VARCHAR2(21);

nt1 nested_typ := nested_typ('abc','def','123');
nt2 nested_typ := nested_typ('123');

I want to have the difference of these two collections, for above Ex: 'def', 'abc'

Please suggest any simple way to do this?

Thanks...

Answer 1

These are simple types, so you can use PL/SQL's set comparison operators. In your case you want to use MULTISET EXCEPT (works the same way as the SQL MINUS operator). Given a third nested table you would code something like:

nt3 := nt1 multiset except nt2;

Find out more.

---

"This works fine when there are no duplicate elements in the array ... Is there any other way to remove all the occurrences of '123' in the nt1?"

Yes, use multiset except distinct.

There are lots of collection operators. As we would expect they are covered in the PL/SQL documentation. Find it here.

Answer 2

Basically you want to use the minus functionality for nested tables. In 10g there is a new feature know as MULTISET EXCEPT. If you want to store the output of minus in a variable then please follow the below steps

declare var1 <nested table type>

in begin section of the code you write the below code to get the minus output

var1:=var2 multiset except var3;

Check this out

Declare     
  TYPE nested_typ IS TABLE OF VARCHAR2(21); 
  nt1 nested_typ := nested_typ('abc','def','123');     
  nt2 nested_typ := nested_typ('123');    
  nt3 nested_typ;    
Begin
  nt3 := nt1 multiset except nt2; 
  dbms_output.put_line(nt3(1)||' '||nt3(2));  
end;

Hope this helps.