title: In Oracle (pre-11.2): When using cast(collect(...)), how do I order the result?
body: <p>When using cast(collect(...)), how do I order the result?</p>
  
  <p>I have a function called GetStringForTable, defined like this:</p>
  
  <pre><code>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 &gt; 1 then
          aResult := aResult || vDelimeter;
        end if;
        aResult := aResult || vTable(i);
      end loop;
      return aResult;
    end GetStringForTable;
  </code></pre>
  
  <p>And I use it like this:</p>
  
  <pre><code>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%';
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>select
    name,
    rep.GetStringForTable
        ((
            Select 
              cast(collect(name) as TVarCharTable)  
            from 
              contacts
            where 
              debtoraccount = dt.accountnumber
            order by name  &lt;= ORA-00907: missing right parenthesis
        )
        ,', ' --Skilletegn
        ) "Contacts"
  from debtable dt
  where name like '%Svein%';
  </code></pre>
  
  <p>and</p>
  
  <pre><code>select
    name,
    rep.GetStringForTable
        ((
          select * from
          (
            Select 
              cast(collect(name) as TVarCharTable)  
            from 
              contacts
            where 
              debtoraccount = dt.accountnumber  &lt;= ORA-00904: string: invalid identifier
            order by name
          )
        )
        ,', ' --Skilletegn
        ) "Contacts"
  from debtable dt
  where name like '%Svein%';
  </code></pre>
  
  <p>Ideally,I would like to do the ordering without changing the GetStringForTable-function. </p>
  
  <p>Can anyone help?</p>