title: How to Use COLLECT with VARCHAR2 Oracle 10g
body: <p>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 for example as follows. </p>
  
  <pre><code>Order  Lot
  123    A23088
  123    A23089
  089    AABBCC
  305    120848
  305    CCDDYY
  </code></pre>
  
  <p>What I need returned is as follows</p>
  
  <pre><code>Order   Lot
  123     A23088, A23089
  089     AABBCC
  305     120848, CCDDYY
  </code></pre>
  
  <p>Using the following, I get the error: TO_STRING is an invalid identifier</p>
  
  <pre><code>TO_STRING ( CAST(COLLECT(DISTINCT LOT) AS varchar2(100)) ) AS LOT
  </code></pre>
  
  <p>Using the following, I get the error: expected CHAR "inconsistent datatypes: expected %s got %s"</p>
  
  <pre><code>TO_CHAR ( CAST(COLLECT(DISTINCT LOT) AS varchar2(100)) ) AS LOT
  </code></pre>
  
  <p>Using the following, I get the error: expected NUMBER "inconsistent datatypes: expected %s got %s"</p>
  
  <pre><code>COLLECT(DISTINCT WHSE_LOT)
  </code></pre>
  
  <p>Is there any way to get this function to work for me? </p>