LOB stuff in 9i

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 17 Jul 2001 13:24:21 -0700
Message-ID: <9j26tl01lb5@drn.newsguy.com>

this isn't widely announced in the press releases either ;)

tkyte_at_TKYTE901.US.ORACLE.COM> create table t ( x long ); Table created.

tkyte_at_TKYTE901.US.ORACLE.COM> insert into t values ( 'hello world' ); 1 row created.

tkyte_at_TKYTE901.US.ORACLE.COM> alter table t modify( x CLOB ); Table altered.

you can now just modify a LONG/LONG RAW to a clob/blob (gotta rebuild indexes after this but better then CREATE TABLE AS SELECT as this won't lose grants/etc)

tkyte_at_TKYTE901.US.ORACLE.COM> select * from t where x like '%w%';

hello world

tkyte_at_TKYTE901.US.ORACLE.COM> select upper(x), initcap(x) 2 from t where instr(x,'world') > 0;

UPPER(X)

INITCAP(X)

HELLO WORLD
Hello World

And regular SQL functions work on them as well (well, the first 64k of them anyway...)

And any application that was written using LONGS, they will work using LOBS now as well...

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Tue Jul 17 2001 - 15:24:21 CDT