Search code examples
databaseoraclesqldatatypes

Why Long data type has been replaced by LOB in Oracle?


LOB having the features of Long data type as well it has its own special features. Since its having features of Long, the additional special features can be given to Long data type itself. So, no need of having one more data type LOB and Long itself can be used all the time. Then why Long has been replaced by LOB?

Even LOB is the replacement for LONG, still Long is made to be available with latest versions of Oracle for backward compatibility. This is additional overhead right?


Solution

  • LOBs are actually four distinct datatypes: CLOB for LONG and BLOB for LONG RAW, plus BFILE and XMLType. Oracle have introduced these types back in the 1990s because LONG (and LONG RAW) are Teh Suck! and terribly hard to work with. There is no reason to use LONG intsead of LOB if the database version is 8.0 or higher.

    So why do we still have LONGs?

    LONG and CLOB are primitive datatypes. So while it is theoretically true that Oracle could have amended LONG to have "the additional special features" of CLOB in practice that would have had a catastrophic impact on upgrading databases to 8.0 (the version which introduced LOBs).

    To say catastrophic is perhaps hyperbolic but the fact is retrofitting CLOB-style features to LONGs means changing datatypes. So the upgrade would have had to include an automatic data conversion. Plus there's probably all sorts of low level routines whose behaviour would have needed to change. It's just a massive vector for data corruption. It is much simpler (and hence safer) to introduce a new datatype and let individual sites handle the migration.

    Oracle have deprecated LONG since 8.0 and provided mechanisms for converting LONGs to CLOBs, so in an ideal world everybody would have moved on and Oracle could drop the LONG datatypes from the database. However, in real life many shops are still using LONGs and too much would break.

    So Oracle have to retain them. The scale of the problem can be derived from the fact that Oracle still uses LONG itself in the data dictionary (such as USER_/ALL_/DBA_VIEWS).