Databases
StackOverflow2013
Postsdbo
POOracle: Coercing VARCHAR2 and CLOB to the same type without truncation
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle: Coercing VARCHAR2 and CLOB to the same type without truncation
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USenigment
    1. USenigment
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOracle: Coercing VARCHAR2 and CLOB to the same type without truncation
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf you were to put all of shorttext into longtext and only have a CLOB column does that makes things quicker?
      singulars
      1. POOracle: Coercing VARCHAR2 and CLOB to the same type without truncation
      1. USBen
    2. COEasier yes, but as indicated above, the short one is indexed, which I'm pretty the CLOB one can't be. It can also be joined against, and supports equals tests in the WHERE clause. This is part of some generic storage infrastructure, where subsets of the data get used for specific purposes, so those features are valuable when it's known that the particular data in question always fits in the short column.
      singulars
      1. POOracle: Coercing VARCHAR2 and CLOB to the same type without truncation
      1. USenigment
    3. CONote that Oracle 12c increases the maximum size of a varchar2 to 32,767.
      singulars
      1. POOracle: Coercing VARCHAR2 and CLOB to the same type without truncation
      1. USDavid Aldridge
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload