title: Oracle: Coercing VARCHAR2 and CLOB to the same type without truncation
body: <p>In an app that supports MS SQL Server, MySQL, and Oracle, there's a table with the following relevant columns (types shown here are for Oracle):</p>
  
  <pre><code>ShortText VARCHAR2(1700) indexed
  LongText CLOB
  </code></pre>
  
  <p>The app stores values 850 characters or less in ShortText, and longer ones in LongText. I need to create a view that returns that data, whichever column it's in. This works for SQL Server and MySQL:</p>
  
  <pre><code>SELECT
    CASE
      WHEN ShortText IS NOT NULL THEN ShortText
      ELSE LongText
    END AS TheValue
  FROM MyTable
  </code></pre>
  
  <p>However, on Oracle, it generates this error:</p>
  
  <pre><code>ORA-00932: inconsistent datatypes: expected CHAR got CLOB 
  </code></pre>
  
  <p>...meaning that Oracle won't implicitly convert the two columns to the same type, so the query has to do it explicitly. Don't want data to get truncated, so the type used has to be able to hold as much data as a CLOB, which as I understand it (not an Oracle expert) means CLOB, only, no other choices are available.</p>
  
  <p>This works on Oracle:</p>
  
  <pre><code>SELECT
    CASE
      WHEN ShortText IS NOT NULL THEN TO_CLOB(ShortText)
      ELSE LongText
    END AS TheValue
  FROM MyTable
  </code></pre>
  
  <p>However, performance is amazingly awful. A query that returns LongText directly took 70-80 ms for about 9k rows, but the above construct took between 30 and 60 <em>seconds</em>, unacceptable.</p>
  
  <p>So:</p>
  
  <ol>
  <li>Are there any other Oracle types I could coerce both columns to
  that can hold as much data as a CLOB? Ideally something more
  text-oriented, like MySQL's LONGTEXT, or SQL Server's NTEXT (or even
  better, NVARCHAR(MAX))?</li>
  <li>Any other approaches I should be looking at?</li>
  </ol>
  
  <hr>
  
  <p>Some specifics, in particular ones requested by @Guido Leenders:</p>
  
  <pre>Oracle version: Oracle Database 11g 11.2.0.1.0 64bit Production
  Not certain if I was the only user, but the relative times are still striking.
  
  Stats for the small table where I saw the performance I posted earlier:
    rowcount: 9,237
    varchar column total length: 148,516
    clob column total length: 227,020</pre>