In Oracle (pre-11.2): When using cast(collect(...)), how do I order the result?

Question

When using cast(collect(...)), how do I order the result?

I have a function called GetStringForTable, defined like this:

FUNCTION GetStringForTable(vTable in TVarCharTable, vDelimeter in varchar default ',') return VarChar2 is
    aResult varchar2(32767);
    i int;
  begin
    if vTable.count = 0 then 
      return '';
    end if;

    for i in 1 .. vTable.Count loop
      if i > 1 then
        aResult := aResult || vDelimeter;
      end if;
      aResult := aResult || vTable(i);
    end loop;
    return aResult;
  end GetStringForTable;

And I use it like this:

select
  name,
  rep.GetStringForTable
      ((
          Select 
            cast(collect(name) as TVarCharTable)  
          from 
            contacts
          where 
            debtoraccount = dt.accountnumber
      )
      ,', ' --Delimiter
      ) "Contacts" 
from debtable dt
where name like '%Svein%';

The problem is that the result is not ordered. I get "Dave, Bob, Carol, Alice" when I would like "Alice, Bob, Carol, Dave". How do I order the result? If tried the following, but none works:

select
  name,
  rep.GetStringForTable
      ((
          Select 
            cast(collect(name) as TVarCharTable)  
          from 
            contacts
          where 
            debtoraccount = dt.accountnumber
          order by name  <= ORA-00907: missing right parenthesis
      )
      ,', ' --Skilletegn
      ) "Contacts"
from debtable dt
where name like '%Svein%';

and

select
  name,
  rep.GetStringForTable
      ((
        select * from
        (
          Select 
            cast(collect(name) as TVarCharTable)  
          from 
            contacts
          where 
            debtoraccount = dt.accountnumber  <= ORA-00904: string: invalid identifier
          order by name
        )
      )
      ,', ' --Skilletegn
      ) "Contacts"
from debtable dt
where name like '%Svein%';

Ideally,I would like to do the ordering without changing the GetStringForTable-function.

Can anyone help?

There are several out there where our SW runs, but none with 11.2 as of yet. Our system requirements will change from 01.01.11 to Oracle 11.2 which will make this sort of things easier. — Svein Bringsli, Dec 10 '10 at 12:31

Jon Heller · Answer 1 · 2013-09-27T04:11:44.083

10

    cast(collect(name order by name) as TVarCharTable)

This syntax is first mentioned in the 11gR1 manual. But it seems to work fine with 10g even though it is not documented.

edited Sep 27 '13 at 04:11

answered Dec 12 '10 at 19:03

Jon Heller

34,999
6
74
132

Vincent Malgrat · Accepted Answer · 2010-12-10T13:08:12.407

one method would be to modify GetStringForTable so that the output comes ordered (you could have two functions: one ordered and the other not)

SQL> CREATE OR REPLACE TYPE TVarCharTable AS TABLE OF VARCHAR2(30);
  2  /
Type created

SQL> CREATE OR REPLACE FUNCTION GetStringForTable(
  2        vTable IN TVarCharTable,
  3        vDelimeter IN VARCHAR DEFAULT ','
  4     ) RETURN VARCHAR2 IS
  5     aResult VARCHAR2(32767);
  6     i       INT := 1;
  7  BEGIN
  8     IF vTable.COUNT = 0 THEN
  9        RETURN '';
 10     END IF;
 11     FOR cc IN (SELECT COLUMN_VALUE cv
 12                  FROM TABLE(CAST(vtable AS TVarCharTable))
 13                 ORDER BY COLUMN_VALUE) LOOP
 14        IF i > 1 THEN
 15           aResult := aResult || vDelimeter;
 16        END IF;
 17        aResult := aResult || cc.cv;
 18        i := i+1;
 19     END LOOP;
 20     RETURN aResult;
 21  END GetStringForTable;
 22  /
Function created

SQL> SELECT GetStringForTable(TVarCharTable('B', 'A', 'D', 'C')) FROM dual;

GETSTRINGFORTABLE(TVARCHARTABL
---------------------------------------------------------------------
A,B,C,D

Update

I've found a workaround, but unfortunately upon additional tests the ORDER is not guaranteed. It will depend upon the access path chosen. Still, it may work in your case :

SQL> SELECT dNAME,
  2         GetStringForTable((SELECT CAST(COLLECT(eNAME) AS TVarCharTable)
  3                              FROM (SELECT *
  4                                      FROM scott.emp
  5                                     ORDER BY ename) e
  6                                   /* ^^^^^^^^ */
  7                              WHERE e.deptno = dt.deptno),
  8                             ', ' --Delimiter
  9                             ) "Contacts"
 10    FROM scott.dept dt;

DNAME          Contacts
-------------- ----------------------------------------------------
ACCOUNTING     CLARK, KING, MILLER
RESEARCH       ADAMS, FORD, JONES, SCOTT, SMITH
SALES          ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
OPERATIONS

You can sort of force the ORDER with a trick to materialize the subquery, but this would prevent the optimizer from using most of the efficient paths, for example:

SQL> WITH employee AS (
  2     SELECT *
  3       FROM scott.emp
  4      WHERE ROWNUM > 0 /* will materialize the subquery */
  5      ORDER BY ename
  6  )
  7  SELECT dNAME,
  8         GetStringForTable((SELECT CAST(COLLECT(eNAME) AS TVarCharTable)
  9                              FROM employee e
 10                              WHERE e.deptno = dt.deptno),
 11                             ', ' --Delimiter
 12                             ) "Contacts"
 13    FROM scott.dept dt;

DNAME          Contacts
-------------- -----------------------------------------------------
ACCOUNTING     CLARK, KING, MILLER
RESEARCH       ADAMS, FORD, JONES, SCOTT, SMITH
SALES          ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
OPERATIONS

Yes, that would work, but I was hoping for a solution that didn't change the function. If we change the function we would have to duplicate that change for all our customers, something that could be problematic. Do you think it's possible to do another way? — Svein Bringsli, Dec 10 '10 at 11:43
Also, the sorting may be different from instance to instance, not necessarily alphabetical. For one report, the customer might want "Alice (237), Bob (695), Carol (107), Dave (471)", but for antoher report they might want "Bob (695), Dave (471), Alice (237), Carol (107)" — Svein Bringsli, Dec 10 '10 at 11:46
@Svein: depending upon your setup, you may find that a inner ORDER BY in the subquery will be kept in the outer subquery. See my updated example. — Vincent Malgrat, Dec 10 '10 at 13:10

In Oracle (pre-11.2): When using cast(collect(...)), how do I order the result?

2 Answers2

Update