Re: Why is VARCHAR2(4000) bad ?

From: Vladimir M. Zakharychev <>
Date: Tue, 22 Jan 2008 08:33:44 -0800 (PST)
Message-ID: <>

On Jan 22, 6:52 pm, Walt <> wrote:
> Serge Rielau wrote:
> > Walt wrote:
> >> Harel wrote:
> >>> there a VARCHAR2(*) ?
> >> Sort of. It's called a CLOB, and is the better way to deal with
> >> arbitrarily long strings. Dealing with CLOBs can be a little tricky
> >> since they're proprietary to Oracle and not standard SQL (i.e. you
> >> can't expect to use them with ODBC etc.) I wouldn't use a CLOB for
> >> this purpose.
> > CLOBs are SQL Standard.
> I stand corrected. CLOBS were not defined in the SQL-92 standard, but
> are defined in subsequent "standards".
> > The DBMS_LOB package (or whatever it is called) is not, of course.
> That's the real point. Once you've defined the data as a CLOB, you have
> to interact with it, and you can't just treat it like a VARCHAR2 in your
> application code.
> //Walt

You can, at least in 9.2+. Selecting a CLOB into a VARCHAR2 variable will implicitly apply DBMS_LOB.SUBSTR() (or its internal equivalent) to it. The same applies to DML against CLOBs - you can insert string literals or VARCHAR2 variables into CLOBs, Oracle does implicit type conversions automatically:

Connected to:
Oracle9i Enterprise Edition Release - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release - Production

SQL> create table clob_test( id number(10) primary key, text clob);

Table created.

SQL> var x varchar2(2000)
SQL> exec :x := 'some text';

PL/SQL procedure successfully completed.

SQL> insert into clob_test values (1, :x);

1 row created.

SQL> select * from clob_test;

        ID TEXT

---------- ------------------------------------------
         1 some text

Of course, this still limits the size of data to 4000 bytes for SQL and 32767 bytes for PL/SQL, for anything beyond you'll need to resort to DBMS_LOB or special LOB-oriented subprograms in your data access layer.


   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm) Received on Tue Jan 22 2008 - 10:33:44 CST

Original text of this message