The LOB Datatype

Oracle8i regards LOBs as being of two kinds depending on their location with regard to the database -- internal LOBs and external LOBs, also referred to as BFILEs (binary files). Note that when we discuss some aspect of working with LOBs without specifying whether the LOB is internal or external, the characteristic under discussion pertains to both internal and external LOBs.

Internal LOBs are further divided into those that are persistent and those that are temporary.

Internal LOBs

Internal LOBs, as their name suggests, are stored inside database tablespaces in a way that optimizes space and provides efficient access. Internal LOBs use copy semantics and participate in the transactional model of the server. You can recover internal LOBs in the event of transaction or media failure, and any changes to a internal LOB value can be committed or rolled back. In other words, all the ACID properties that pertain to using database objects pertain to using internal LOBs.

Internal LOB Datatypes

There are three SQL datatypes for defining instances of internal LOBs:

• BLOB, a LOB whose value is composed of unstructured binary ("raw") data.

• CLOB, a LOB whose value is composed of character data that corresponds to the database character set defined for the Oracle8i database.

• NCLOB, a LOB whose value is composed of character data that corresponds to the national character set defined for the Oracle8i database.

External LOBs (BFILEs)

External LOBs (BFILES) are large binary data objects stored in operating system files outside database tablespaces. These files use reference semantics. Apart from conventional secondary storage devices such as hard disks, BFILEs may also be located on tertiary block storage devices such as CD-ROMs, PhotoCDs and DVDs.

The BFILE datatype allows read-only byte stream access to large files on the filesystem of the database server.

The Oracle Server can access BFILEs provided the underlying server operating system supports stream-mode access to these operating system (OS) files.

Note: External LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying file system as governed by the operating system. You cannot locate a single BFILE on more than one device, for instance, striped across a disk array.

External LOB Datatypes

There is one datatype, BFILE, for declaring instances of external SQL LOBs.

• BFILE, a LOB whose value is composed of binary ("raw") data, and is stored outside the database tablespaces in a server-side operating system file.

Internal LOBs Use Copy Semantics, External LOBs Use Reference Semantics

• Copy semantics: Both LOB locator and value are copied

• Reference semantics: Only LOB locator is copied

Copy Semantics

Internal LOBs, namely BLOBs, CLOBs, NCLOBs, whether persistent or temporary, use copy semantics.

When you insert or update a LOB with a LOB from another row in the same table, the LOB value is copied so that each row has a different copy of the LOB value.

Internal LOBs have copy semantics so that if the LOB in the row of the table is copied to another LOB, in a different row or perhaps in the same row but in a different column, then the actual LOB value is copied, not just the LOB locator. This means in this case that there will be two different LOB locators and two copies of the LOB value.

Reference Semantics

External LOBs (BFILEs) use reference semantics. When the BFILE in the row of the table is copied to another BFILE, only the BFILE locator is copied, not the actual BFILE data, i.e., not the actual operating system file.