Re: Why is VARCHAR2(4000) bad ?
Date: Tue, 22 Jan 2008 08:33:44 -0800 (PST)
On Jan 22, 6:52 pm, Walt <walt_ask..._at_SHOESyahoo.com> wrote:
> Serge Rielau wrote:
> > Walt wrote:
> >> Harel wrote:
> >>>...is 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.
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:
Oracle9i Enterprise Edition Release 188.8.131.52.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 184.108.40.206.0 - Production
SQL> create table clob_test( id number(10) primary key, text clob);
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;
---------- ------------------------------------------ 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) http://www.dynamicpsp.com Received on Tue Jan 22 2008 - 10:33:44 CST