Selecting a Datatype

When selecting a datatype, take into consideration the following topics:

• LOBs Compared to LONG and LONG RAW Types

• Character Set Conversions: Working with Varying-Width and Multibyte Fixed-Width Character Data

LOBs Compared to LONG and LONG RAW Types

Table 7-1lists the similarities and differences between LOBs, LONGs, and LONG RAW types.

Table 7-1 LOBs Vs. LONG RAW

LOB Data Type	LONG and LONG RAW Data Type
You can store multiple LOBs in a single row	You can store only one LONG or LONG RAW per row.
LOBs can be attributes of a user-defined datatype	This is not possible with either a LONG or LONG RAW
Only the LOB locator is stored in the table column; BLOB and CLOB data can be stored in separate tablespaces and BFILE data is stored as an external file. For inline LOBs, Oracle will store LOBs that are less than approximately 4,000 bytes of data in the table column.	In the case of a LONG or LONG RAW the entire value is stored in the table column.
When you access a LOB column, you can choose to fetch the locator or the data.	When you access a LONG or LONG RAW, the entire value is returned.
A LOB can be up to 4 gigabytes in size. The BFILE maximum is operating system dependent, but cannot exceed 4 gigabytes. The valid accessible range is 1 to (232-1).	By contrast, a LONG or LONG RAW is limited to 2 gigabytes.
There is greater flexibility in manipulating data in a random, piece-wise manner with LOBs. LOBs can be accessed at random offsets.	Less flexibility in manipulating data in a random, piece-wise manner with LONG or LONG RAW data.LONGs must be accessed from the beginning to the desired location.
You can replicate LOBs in both local and distributed environments.	Replication in both local and distributed environments is not possible with a LONG or LONG RAW (see Oracle9i Replication)

Replication

Oracle does not support the replication of columns that use the LONG and LONG RAW datatypes. Oracle simply omits columns containing these datatypes from replicated tables. In Oracle9i, you must convert LONG datatypes to LOBs and then replicate.

Converting LONG Columns to LOBs

Existing LONG columns can be converted to LOBs using either of the following methods:

• LONG-to-LOB API described in Chapter 8, "Migrating From LONGs to LOBs"

• TO_LOB() function (see "LONGs to LOBs" in Chapter 10, "Internal Persistent LOBs").

  Note: Oracle9i does not support conversion of LOBs back to LONGs.

Character Set Conversions: Working with Varying-Width and Multibyte Fixed-Width Character Data

In OCI (Oracle Call Interface), or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another.

However, no implicit translation is ever performed from binary data to a character set. When you use the loadfromfile operation to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. In that case, you will need to perform character set conversions on the BFILE data before executing loadfromfile.

See: Oracle9i Globalization and National Language Support Guide, for more detail on character set conversions.

Note: The ALTER DATABASE command will not work when there are CLOB or NCLOB columns in the tables.