Hi,
My question is related with how the collection function SET works. I have a collection that contains duplicated values (such as the example provided) and I want to have as an output, the distinct list of values from that collection.
The SET function does exactly that job. However, what I'm not sure is if the original order of the values is kept. By that, I mean when having something such as:
'a', 'b', 'c', 'd', 'b', 'c', 'd', 'b', 'c'
do I always end up with
'a', 'b', 'c', 'd'
or is it possible to end up with something like
'b', 'd', 'a', 'c'
In the documentation doesn't really mention that:
https://docs.oracle.com/database/121/SQLRF/functions177.htm#SQLRF06308 Thanks
Pedro
It would *appear* to be the case, eg
SQL> create or replace type varchar2_list is table of varchar2(10);
2 /
Type created.
SQL> select varchar2_list('1','2','3','4','5','6','7','1','2','3','4') from dual;
VARCHAR2_LIST('1','2','3','4','5','6','7','1','2','3','4')
--------------------------------------------------------------------------------------------------
VARCHAR2_LIST('1', '2', '3', '4', '5', '6', '7', '1', '2', '3', '4')
SQL> select set(varchar2_list('1','2','3','4','5','6','7','1','2','3','4')) from dual;
SET(VARCHAR2_LIST('1','2','3','4','5','6','7','1','2','3','4'))
--------------------------------------------------------------------------------------------------
VARCHAR2_LIST('1', '2', '3', '4', '5', '6', '7')
SQL> select set(varchar2_list('4','1','7','3','2','4','7','6','1','9','3')) from dual;
SET(VARCHAR2_LIST('4','1','7','3','2','4','7','6','1','9','3'))
--------------------------------------------------------------------------------------------------
VARCHAR2_LIST('4', '1', '7', '3', '2', '6', '9')
but of course, millions of examples does not prove the non-existence of a counter example. I'll ask the PL/SQL PM about this and get back with more information.
But if it's not in the documentation ...then generally, it's not safe to rely upon