Large Objects   «Prev  Next»

Lesson 4Converting LONG and LONG RAW to LOB
ObjectiveMove data from LONG and LONG RAW columns to a LOB column.

Converting LONG | LONG RAW to LOB

Move data from LONG and LONG RAW columns to a LOB column.
In Oracle, the appropriate data types for storing large objects are the
  1. BLOB,
  2. CLOB, and
  3. NCLOB data types.
In earlier versions of Oracle, the LONG and LONG RAW data types were used for the same purposes.

Differences between LONGs and LOBs

There are a number of differences between the ways a LONG or LONG RAW column can be used and the way a column with any of the LOB data types can be used. Some of these differences are:
  1. LONGs can contain only up to 2 GB of data, whereas the LOB data types can contain up to 4 GB.
  2. You can only have 1 LONG column per table, but you can have more than one LOB per table.
  3. When a LOB is selected, only the locator for the LOB is returned.
  4. A LOB can be part of an object type, whereas a LONG cannot.
  5. You can use a LOB as a bind variable, whereas you cannot use a LONG as a bind variable.

Because of the increased capability of LOBs, and because Oracle will probably discontinue support for the LONG data types in the future, Oracle recommends that you convert existing LONG and LONG RAW columns to a LOB data type.

Converting LONGs to LOBs

You can convert a LONG or LONG RAW column to one of the LOB data types with the TO_LOB function. You can use this function only in a subSELECT clause of an INSERT statement, as in the following diagram:
The new table with the LOB data type
  1. Required keywords
  2. The new table with the LOB data type. This table must exist before using this statement.
  3. Required keyword
  4. A list of non-LONG columns
  5. The required function
  6. The LONG or LONG RAW column that is to be converted
  7. Required keyword
  8. The original table containing the LONG or LONG RAW column

TO_LOB function in Oracle

The TO_LOB function is still used in Oracle performance tuning, but it is less commonly used than it once was. This is because there are now other, more efficient ways to convert LONG and LONG RAW data to LOB data.
The TO_LOB function was introduced in Oracle 8i and was originally the only way to convert
  1. LONG and
  2. LONG RAW

data to LOB data. However, in Oracle 9i, the DBMS_LOB package was introduced, which provides a more efficient way to convert LONG and LONG RAW data to LOB data. The DBMS_LOB package also provides a number of other LOB-related functions, such as the ability to create, read, write, and delete LOB data.
In addition to the DBMS_LOB package, there are now a number of third-party tools that can be used to convert LONG and LONG RAW data to LOB data. These tools can often be more efficient than the TO_LOB function, especially for large amounts of data. As a result of these newer, more efficient methods, the TO_LOB function is now less commonly used in Oracle performance tuning. However, it is still a valid option, and it may be the best option in some cases.
Here are some of the reasons why the TO_LOB function is still used:
  • It is a simple and easy-to-use function.
  • It is supported by all Oracle versions from 8i onwards.
  • It is compatible with all LOB types.

Here are some of the reasons why the TO_LOB function is not as commonly used as it once was:
  • It is less efficient than the DBMS_LOB package and third-party tools.
  • It is not as versatile as the DBMS_LOB package and third-party tools.
  • It is not as scalable as the DBMS_LOB package and third-party tools.

If you are considering using the TO_LOB function, I recommend that you first try to use the DBMS_LOB package or a third-party tool. If these options are not available or do not meet your needs, then you can use the TO_LOB function.
The following diagram describes the TO_LOB function, which was introduced in Oracel 8i.

TO_LOB 
INSERT INTO lob_table
  SELECT column_list
  TO_LOB long_column
  FROM long_table
Oracle TO_LOB

INSERT INTO Required keywords.
lob_table The new table with the LOB data type. This table must exist before using this statement.
SELECT Required keyword
column_listA list of non-LONG columns
TO_LOB The required function
long_columnThe LONG or LONG RAW column that is to be converted
FROMRequired keyword
long_table The original table containing the LONG or LONG RAW column

You should convert LONG RAW columns into BLOB columns and LONG columns into either CLOB or NCLOB columns.
In the next lesson, you will learn how to manage the size of the data in LOBs.