title: Oracle 11G DB : Result from 'clob' type column in view changed while using the view in a where clause
body: <p>I have the current query that i'm running in Oracle:</p>
  <pre><code>    WITH viewa
       AS (SELECT c.columna
           FROM   sometable c
           LEFT JOIN othertable u
           ON ( c.id = u.id )
           WHERE id= '111' 
           ORDER BY c.created_date)
  SELECT columna
  FROM   (SELECT rownum AS row_num,
                 t.*
          FROM   viewa t)
  WHERE  row_num &gt; (SELECT CASE
                             WHEN ( Count(*) &gt; 100 ) THEN Count(*) - 100
                             ELSE 0
                           END AS num
                    FROM   viewa)
  </code></pre>
  <p>the idea is to always get the first 100 rows.
  as you can see, i'm creating a view at the beginning and use it twice:
  in the from and in the where.</p>
  <p>i'm doing that so i wouldn't need to fetch the first select twice and it also make the query more readable.</p>
  <p>notice that  <strong>columna is of type CLOB!!</strong>
  when i'm doing the same query with other column types its working!
  so its probably something related to the clob column</p>
  <p>The weird think is that the results that im getting are empty values even though i have values in the DB!</p>
  <p>when i'm removing the subselect in the where i'm getting the right result:</p>
  <pre><code>WITH viewa
       AS (SELECT c.columna
           FROM   sometable c
           LEFT JOIN othertable u
           ON ( c.id = u.id )
           WHERE id = '111' 
           ORDER BY c.created_date)
  SELECT columna
  FROM   (SELECT rownum AS row_num,
                 t.*
          FROM   viewa t)
  WHERE  row_num &gt; 0
  </code></pre>
  <p>seems like Oracle is turning the values for the Clob column &quot;columnA&quot; into null when using the view in the where.</p>
  <p>is someone familiar with that?
  know how to go around it ?</p>
  <p>i solved it with a different query but i still would like to know if Oracle does change the view while fetching from it?</p>
  <p>thank you</p>