Lesson 4	Converting LONG and LONG RAW to LOB
Objective	Move 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

BLOB,
CLOB, and
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:

LONGs can contain only up to 2 GB of data, whereas the LOB data types can contain up to 4 GB.
You can only have 1 LONG column per table, but you can have more than one LOB per table.
When a LOB is selected, only the locator for the LOB is returned.
A LOB can be part of an object type, whereas a LONG cannot.
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:

Required keywords

The new table with the LOB data type. This table must exist before using this statement.

Required keyword

A list of non-LONG columns

The required function

The LONG or LONG RAW column that is to be converted

Required keyword

The original table containing the LONG or LONG RAW column

to_lob 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.