Thursday, December 29, 2011

Long Datatypes

I was looking at LONG datatypes in an Oracle 9 database recently. There are a couple of restrictions with them. You cannot do a create table as select or CTAS on a table or view which contains a LONG column:
  
SQL> desc dba_views
Name                       Null?    Type
-------------------------- -------- ------------------
OWNER                      NOT NULL VARCHAR2(30)
VIEW_NAME                  NOT NULL VARCHAR2(30)
TEXT_LENGTH                         NUMBER
TEXT                                LONG
TYPE_TEXT_LENGTH                    NUMBER
TYPE_TEXT                           VARCHAR2(4000)
OID_TEXT_LENGTH                     NUMBER
OID_TEXT                            VARCHAR2(4000)
VIEW_TYPE_OWNER                     VARCHAR2(30)
VIEW_TYPE                           VARCHAR2(30)
SUPERVIEW_NAME                      VARCHAR2(30)

SQL> create table andrews_views
  2  as select * from dba_views
  3  /
as select * from dba_views
          *
ERROR at line 2:
ORA-00997: illegal use of LONG datatype

SQL>


And you cannot have more than 1 LONG column in a table:

SQL> create table andrews_table
  2  (long1 long)
  3  /


Table created.

SQL> alter table andrews_table add
  2  (long2 long)
  3  /
(long2 long)
*
ERROR at line 2:
ORA-01754: a table may contain only one column of type LONG

SQL>

No comments: