A long story

This is an attempt to write a short article about the LONG data type history in Oracle Database.

1988

In Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions, Tom Kyte writes page 513 that “the LONG types date back to version 6 of Oracle, when they were limited to 64 KB of data.”.

1992

In Oracle 7 (see Oracle 7 Server Reference page 71 – chapter 2 page 23) the LONG data type is able to store up to 2GB of data.

1998

In Oracle 8.0.4, Oracle Corp. implemented 3 new data types to store large objects (LOB):

  • CLOB and NCLOB to store character data
  • BLOB to store binary data
  • The Oracle 8.0 SQL Reference lists the LONG data types limitations (quite long actually) but does not explicitely recommend to use LOBs instead of LONG: only in the Oracle 8.0 Concepts Guide it is written that “in new applications, you should use CLOB and NCLOB datatypes for large amounts of character data.”.

    1999

    The Oracle 8.1.5 SQL Referencestrongly recommends that you convert LONG columns to LOB columns.”.

    2001

    The Oracle 9.2 SQL Reference
    states “Oracle Corporation strongly recommends that you convert LONG columns to LOB columns as soon as possible. Creation of new LONG columns is scheduled for desupport.”.

    2003

    The Oracle 10.1 SQL Reference instructs “Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead.” and “Oracle also recommends that you convert existing LONG columns to LOB columns.”. The Oracle 10.1 Application Developer’s Guide – Large Objects answers the question “Why Not Use LONGs ?” with “The database supports LONG as well as LOB datatypes. When possible, change your existing applications to use LOBs instead of LONGs because of the added benefits that LOBs provide.”.

    2005-2009

    Oracle Corp. did not change the 10.2, 11.1 and 11.2 documentation about LONG data type recommended usage and possible desupport.

    2013

    The same wording is still used in the Oracle 12.1 SQL Reference and in the Oracle 12.1 Database SecureFiles and Large Objects Developer’s Guide.

    Note that it is only starting in Oracle 12.1 released in 2013 that VARCHAR2 data type can store more than 4000 characters.

    In a 12.1 database the database dictionary is still using a lot of LONG columns likely for “backward compatibility”:

    SYS@CDB12C>select * from v$version;
    
    BANNER                                                                               CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
    PL/SQL Release 12.1.0.1.0 - Production                                                    0
    CORE    12.1.0.1.0      Production                                                                0
    TNS for Linux: Version 12.1.0.1.0 - Production                                            0
    NLSRTL Version 12.1.0.1.0 - Production                                                    0
    
    SYS@CDB12C>select owner, count(*)
      2  from dba_tab_columns
      3  where data_type='LONG'
      4  group by owner;
    
    OWNER                  COUNT(*)
    -------------------- ----------
    APEX_040200                   1
    OUTLN                         1
    SYS                         242
    SYSTEM                       11
    WMSYS                         2
    
    

    Here are the tables with LONG colums differences between 12.1 and 11.2:

    C##ADMIN@cdbrac1>select * from v$version;
    
    BANNER                                                                               CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
    PL/SQL Release 12.1.0.1.0 - Production                                                    0
    CORE    12.1.0.1.0      Production                                                                0
    TNS for Linux: Version 12.1.0.1.0 - Production                                            0
    NLSRTL Version 12.1.0.1.0 - Production                                                    0
    
    C##ADMIN@cdbrac1>--
    C##ADMIN@cdbrac1>create database link ldb11 connect to admin identified by admin using 'db11';
    
    Database link created.
    
    C##ADMIN@cdbrac1>select * from v$version@ldb11;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for Linux: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    
    C##ADMIN@cdbrac1>--
    C##ADMIN@cdbrac1>select tc.owner, tc.table_name, tc.column_name
      2  from dba_tab_cols tc,
      3       dba_tables t
      4  where data_type = 'LONG'
      5  and (t.owner = tc.owner and t.table_name = tc.table_name)
      6  and (tc.owner, tc.table_name, tc.column_name) not in
      7      (select owner, table_name, column_name from dba_tab_cols@ldb11);
    
    OWNER                TABLE_NAME                     COLUMN_NAME
    -------------------- ------------------------------ ------------------------------
    SYS                  SQLOBJ$PLAN                    OTHER
    
    C##ADMIN@cdbrac1>--
    C##ADMIN@cdbrac1>select tc.owner, tc.table_name, tc.column_name
      2  from dba_tab_cols@ldb11 tc,
      3       dba_tables@ldb11 t
      4  where data_type = 'LONG'
      5  and (t.owner = tc.owner and t.table_name = tc.table_name)
      6  and (tc.owner, tc.table_name, tc.column_name) not in
      7      (select owner, table_name, column_name from dba_tab_cols);
    
    OWNER                TABLE_NAME                     COLUMN_NAME
    -------------------- ------------------------------ ------------------------------
    EXFSYS               EXF$PLAN_TABLE                 OTHER
    SYSTEM               MVIEW$_ADV_PARTITION           QUERY_TEXT
    
    

    At the same time some tables with LONG columns (EXFSYS.EXP$PLAN_TABLE.OTHER and SYSTEM.MVIEWS$_ADV_PARTITION.QUERY_TEXT) have been removed but a new table with LONG column (SYS.SQLOBJ$PLAN.OTHER) has been added.

    Conclusion

    One can really wonder if some future major Oracle version will migrate database dictionary LONG columns to LOB columns and desupport LONG data types.

    Here are some interesting links about LONG data type usage especially if you have to work with database dictionary tables still using this data type:

  • working with long columns by Adrian Billington
  • a PL/SQL procedure to print a LONG variable by Tom Kyte
  • a PL/SQL function to convert LONG into CLOB by an AskTom site reader
  • Coping with Legacy LONG Types (free excerpt from Oracle Expert Database Architecture 9i and 10g book by Tom Kyte)
  • I hope that this article is not too long.

    Advertisements
    Post a comment or leave a trackback: Trackback URL.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: