title: Distincting values inside a clob
body: <p>I have some SQL that pulls back data from a number of places and then shows it in one row, two cells. In each of the cells is a list of the data. If I break down the SQL, Originally the data would show like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Name</th>
  <th>Colortype</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Orange</td>
  <td>Gold</td>
  </tr>
  <tr>
  <td>Yellow</td>
  <td>Banana [CHR(10)] Gold</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Sorry for the weird table, but basically Name column has one value per row, but Colortype can have 0 to many values. The issue in my SQL is the ColorType field is a blob list of values that has been separated by a newline CHR(10). So under Yellow Name, the colortype contains Banana separated by a newline and Gold. But you can also see that Name:Orange also has a Gold colortype.</p>
  <p>In the end of my SQL I am taking the individual rows and rolling up so that in the end I get one row, each column containing a list of values using this..</p>
  <pre><code>SELECT distinct RTRIM(XMLAGG(XMLELEMENT(E, (myTableX.uo_unicode_to_sch (colortype)), CHR (10) ).EXTRACT('//text()')  ORDER BY pct_vu).GetClobVal(),',') as risk_tx,
                  RTRIM(XMLAGG(XMLELEMENT(E, myTableY.uo_unicode_to_sch (name)), CHR(10)).EXTRACT('//text()') ORDER BY pct_vu desc).GetClobVal(),',') AS name
  </code></pre>
  <p>Which gives me one row, the names with it's already unique 19 values. And the colortype, with it's list of 1+x non-unique values. Like this but instead of spaces between values in the cell, they are newlines.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Name</th>
  <th>Colortype</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Orange Yellow</td>
  <td>Gold Banana Gold</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>This is most of the original sql:</p>
  <pre><code>SELECT *
  FROM (SELECT distinct  to_char(colortype),  name
      FROM (SELECT DISTINCT DBMS_LOB.substr(x.name, 4000) as name ,
                          DBMS_LOB.substr(x.colortype, 4000, 1) as colortype
            FROM (SELECT distinct RTRIM(XMLAGG(XMLELEMENT(E, (myTableA.uo_unicode_to_A (colortype)), CHR (10) ).EXTRACT('//text()')  ORDER BY w).GetClobVal(),',') as colortype,
                                  RTRIM(XMLAGG(XMLELEMENT(E, myTableA.uo_unicode_to_A (name)|| ' '  || CASE WHEN w &lt; 0.1 THEN '&lt; 0.1' ELSE TRIM (TO_CHAR (w, CASE WHEN w &lt; 1 THEN '0.9' ELSE '999'END)) END
                                          || '%', CHR (10)).EXTRACT('//text()') ORDER BY w desc).GetClobVal(),',') AS name,
                                  RTRIM(XMLAGG(XMLELEMENT(E,CHR (13) , CHR (10) ).EXTRACT('//text()') ).GetClobVal(),',') AS rz
                  FROM (SELECT MAX (CASE WHEN z.item_type = 'Name' THEN z.value_tx END) AS name,
                               MAX (CASE WHEN z.item_type = 'Exact %' THEN TO_NUMBER(REGEXP_SUBSTR(z.value_tx,'^\d+')) END) AS w,                             
                               MAX (CASE WHEN z.item_type = 'Class' THEN z.value_tx END) AS colortype
                        FROM myTableA.product p
                          JOIN TABLE (myTableA.z_data.get_unique_name ('r@'|| TO_CHAR (p.prod_id)))z
                                  ON z.country_cd = 'US' AND z.locale_cd = 'en' AND z.section_vu = 3
                                          AND z.item_type IN ('Name','Exact %','Class')
                        GROUP BY z.sub_section_nm))x
                        JOIN TABLE (CAST (MULTISET(SELECT LEVEL FROM DUAL CONNECT BY LEVEL &lt;=(LENGTH (x.colortype)- LENGTH (REPLACE (x.colortype,x.rz)))
                                              / LENGTH (x.rz) + 1)  AS SYS.odcinumberlist)) y
                  ON 1 = 1
                  )
          );
  </code></pre>
  <p>I cannot provide the full sql, but what I'm looking for is anyone's thoughts on how to distinct a clob field in the end? I am using 12.1 Oracle.</p>