Oracle SET operation on the collection.

Preamble

In Oracle PL/SQL, an operation on SET collections returns a collection with distinct (i.e. a collection without takes).

SET syntax in Oracle PL/SQL

SET (nt)

Parameters and arguments of the operation

• nt – collection from which duplicates are removed

Note:

• Operations are possible only with collections of nested tables. The result of the operation is also a collection of nested tables.

Consider an example operation on SET collections to understand how to use SET in Oracle PL/SQL

For example:

DECLARE
reslt varchar2(100);
TYPE nested_typ IS TABLE OF NUMBER;
nt3 nested_typ := nested_typ(2,3,1,3,4);
answer nested_typ;
BEGIN
answer := SET (nt3);
for iter in answer.first ... answer.last
loop
if iter = 1 then
reslt:=answer(iter);
else
reslt := reslt ||', '||to_char(answer(iter));
end if;
end loop;
dbms_output.put_line('SET (nt3) = '||reslt);
--Output: SET (nt3) = 2, 3, 1, 4
END;

Another example of SET with string collections

DECLARE
reslt varchar2(100);
TYPE nested_typ IS TABLE OF varchar2(20);
nt3 nested_typ := nested_typ('beta', 'alpha', 'gamma', 'delta', 'gamma', 'alpha');
answer nested_typ;
BEGIN
answer := SET (nt3);
for iter in answer.first ... answer.last
loop
if iter = 1 then
reslt:=answer(iter);
else
reslt := reslt ||', '||answer(iter);
end if;
end loop;
dbms_output.put_line('SET (nt3) = '||reslt);
--Output: SET (nt3) = beta, alpha, gamma, delta
END;

Oracle Set operator union unionall intersect minus

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.