Thursday, December 29, 2011

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>

No comments: