Modeling and Design, 2 of 12

Selecting a Datatype

LOBs in Comparison to LONG and LONG RAW Types

LOBs are similar to LONG and LONG RAW types, but differ in the following ways:

Table 7-1 LOBs Vs. LONG RAW

LOBs Data Type	LONG and LONG RAW Data Type
You can store multiple `LOB`s in a single row	You can store only one `LONG` or `LONG` `RAW` per row.
`LOB`s 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, it is the locator which is returned.	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 (2³²-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 `LOB`s. 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 `LOB`s in both local and distributed environments.	Replication in both local and distributed environments is not possible with a`LONG` or `LONG` `RAW` (see Oracle8i Replication)

Existing LONG columns can be converted to LOBs using the TO_LOB() function (see "Copy LONG to LOB" in Chapter 9, "Internal Persistent LOBs").

Note that Oracle8i does not support conversion of LOBs back to LONGs.

Character Set Conversions: Working with Varying-Width Character Data

In using 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: :
Oracle8i National Language Support Guide, for more detail on character set conversions.