Re: Why is VARCHAR2(4000) bad ?

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.
>
> //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 9.2.0.8.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.8.0 - 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.

Regards,

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