How To Use Collect With Varchar2 Oracle 10g

I'm trying to get the COLLECT function to work for me. I'm using 10g and therefore found that LISTAGG and WM_CONCAT will not work (invalid identifier errors). The data I have is f

Solution 1:

The collect function creates a nested table, in your case a table of strings, which you would then cast to a specific type - that is, a type defined as a table of varchar2. You can't cast to a single string.

There are some well-known lists of string aggregation techniques, like this one. There is one that uses collect, but you still need the table type and a function to convert the generated table to a delimited string.

Copying that example verbatim:

CREATEOR REPLACE TYPE t_varchar2_tab ASTABLEOF VARCHAR2(4000);
/CREATEOR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT',') RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGINFOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;
/

With that type and function you then do:

SELECT tab_to_string(CAST(COLLECT(DISTINCT lot) AS t_varchar2_tab)) AS lot FROM ...

---

Interestingly, the 10g version of collect doesn't support DISTINCT; it doesn't complain (!?), but leaves duplicates.

You can pass the collection through the set function to remove the duplicates:

SELECT tab_to_string(SET(CAST(COLLECT(DISTINCT lot) AS t_varchar2_tab))) AS lot FROM ...

Quick demo run in 10.2.0.5:

createtable table1(order_no number, lot varchar2(10));

insertinto table1 values (590288, '2016538');
insertinto table1 values (590288, '2016535');
insertinto table1 values (590288, '6016535');
insertinto table1 values (590288, '2016535');
insertinto table1 values (590288, '2016538');

SELECT order_no, tab_to_string(SET(CAST(COLLECT(DISTINCT lot) AS t_varchar2_tab))) AS LOT
FROM table1 WHERE order_no =590288GROUPBY order_no;

  ORDER_NO LOT                                              
---------- --------------------------------------------------5902882016538,2016535,6016535