Known Issues using CLOB data type
851431Mar 29 2011 — edited Mar 30 2011Dear Gurus,
We are designing a database that will be receiving comments from external data source. These comments are stored as CLOB in the external database. We found that only 1% of incoming data will be larger than 4000 characters and are now evaluating the Pros and Cons of storing only 4000 characters of incoming comments in VARCHAR2 data type or using CLOB data type.
Some of the concerns brought up during discussion were:
- having to store CLOBs in separate tablespace;
- applications, such Toad require changing defaults settings to display CLOBs in the grid. Default value is not to display them;
- applications that build web page with CLOBs will be struggling to fit 18 thousand chararcters of which 17 thousand are blank lines;
- cashing CLOBs in memory will consume big chunk of data buffers which will affect performance;
- to manipulate CLOBs you need PL/SQL anonymous block or procedure;
- bind variables cannot be assigned CLOB value;
- dynamic SQL cannot use CLOBs;
- temp tables don't work very well with CLOBs;
- fuzzy logic search on CLOBs is ineffective;
- not all ODBC drivers support Oracle CLOBs
- UNION, MINUS, INTERSECT don't work with CLOBs
I have not delt with CLOB data type in the past, so I am hoping to hear from you of any possible issues/hastles we may encounter?