Re: JDBC and CLOB retrieval question
Lew wrote:
TEXT is also Postgres's version of LONG VARCHAR and works like an
unlimited-length VARCHAR.
Arne VajhQj wrote:
Are there any difference between a CLOB and an
unlimited-length VARCHAR ?
Yes, up to many, depending on the RDBMS. Some RDBMSes have different rules
for indexing and searching CLOBs than they do for VARCHAR.
For example, in Oracle:
Restrictions on LOB Columns LOB columns are subject to a number of rules and restrictions.
See Oracle Database SecureFiles and Large Objects Developer's Guide for a complete listing.
<http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements001.htm#sthref170>
and
You cannot specify a LOB as a primary key column.
<http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28393/adlob_working.htm#ADLOB2010>
which lists several more difference between LOBs, including CLOBs, and other
data types like VARCHAR2.
Furthermore,
Most SQL character functions are enabled to accept CLOBs as parameters,
and Oracle performs implicit conversions between CLOB and character types.
Therefore, functions that are not yet enabled for CLOBs can accept CLOBs
through implicit conversion. In such cases, Oracle converts the CLOBs to
CHAR or VARCHAR2 before the function is invoked. If the CLOB is larger
than 4000 bytes, then Oracle converts only the first 4000 bytes to CHAR.
<http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements002.htm#i55214>
Also, CLOBs are often implemented differently from VARCHAR columns.
A "true" unlimited VARCHAR would not have such restrictions, nor would it need
conversion functions like TO_CLOB and TO_CHAR to make the types commensurate.
Postgres does not have such differences between its TEXT and VARCHAR types,
and in fact supports declaring an unlimited VARCHAR (one without a length
specifier).
Every RDBMS has its own deviations from the SQL "standard".
--
Lew