Long to LOB Conversion

I went on an Oracle 11g release 2 seminar recently. They said that LONG data types are still supported but that Oracle recommends converting them to LOB (i.e. CLOB or NCLOB). I’m not totally convinced by this as they are still using LONG columns themselves e.g. in table SYS.VIEW$, which is one of the underlying tables for the DBA_VIEWS view:

 

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 29 14:27:22 2011

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> desc sys.view$

Name                       Null?    Type

-------------------------- -------- ------------------

OBJ#                       NOT NULL NUMBER

AUDIT$                     NOT NULL VARCHAR2(38)

COLS                       NOT NULL NUMBER

INTCOLS                    NOT NULL NUMBER

PROPERTY                   NOT NULL NUMBER

FLAGS                      NOT NULL NUMBER

TEXTLENGTH                          NUMBER

TEXT                                LONG

 

SQL>

 

Converting a LONG to a CLOB is easy:

 

SQL> create table andrews_table

  2  (name varchar2(10),

  3   address long)

  4  /

 

Table created.

 

SQL> insert into andrews_table values

  2  ('Noddy', '10 High St, Anytown')

  3  /

 

1 row created.

 

SQL> alter table andrews_table modify (address clob)

  2  /

 

Table altered.

 

SQL> desc andrews_table

Name                       Null?    Type

-------------------------- -------- ------------------

NAME                                VARCHAR2(10)

ADDRESS                             CLOB

 

SQL> select * from andrews_table

  2  /

 

NAME       ADDRESS

---------- --------------------

Noddy      10 High St, Anytown

 

SQL>

 

But you need to be certain that you want to do this as there is no going back:

 

SQL> alter table andrews_table modify (address long)

  2  /

alter table andrews_table modify (address long)

                                  *

ERROR at line 1:

ORA-22859: invalid modification of columns

 

SQL>