Internal Large Objects LOBs

1
Internal Large Objects (LOBs)

• Stored in the database tablespaces in a way that
  optimizes space and provides efficient access.
• Use copy semantics
• If the internal LOB value is less than
  approximately 4000 bytes, then the value is
  stored inline otherwise it is stored outside the
  row.
• A LOB locator as a pointer to the actual location
  of the LOB value, which is always stored in the
  row.
• Recoverable in the event of transaction or media
  failure

2
External LOBs

• Also referred to as BFILES
• Large binary data objects stored in operating
  system files outside of the database tablespaces
• Use reference semantics
• 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.

3
Internal LOB Datatypes

• BLOB, a LOB whose value is composed of
  unstructured binary ("raw") data.
• CLOB, a LOB whose value is composed of
  single-byte fixed-width character data that
  corresponds to the database character set defined
  for the Oracle8 database.
• NCLOB, a LOB whose value is composed of
  fixed-width multi-byte character data that
  corresponds to the national character set defined
  for the Oracle8 database. (Cannot be an attribute
  of a type.)

4
Create Table with LOB Attributes
CREATE TABLE lob_table ( key_value INTEGER,
b_lob BLOB, c_lob CLOB, n_lob NCLOB,
f_lob BFILE)
5
Initialize LOBs

• You can set an internal LOB - -that is, a LOB
  column in a table, or a LOB attribute in an
  object type defined by you- to be empty, or NULL.
  
• LOB columns, but not LOB attributes, may be
  initialized to a value.
• An external LOB (i.e. BFILE) can be initialized
  to NULL or to a filename

6
An Example
INSERT INTO lob_table VALUES (1001,
EMPTY_BLOB(), NULL, EMPTY_CLOB(), NULL)
7
Access External LOBs

• A DIRECTORY object specifies a logical alias name
  for a physical directory on the server's file
  system.
• Oracle does not verify that the directory and
  pathname you specify actually exist.

8
Some Examples
INSERT INTO lob_table VALUES (21, NULL, NULL,
NULL, BFILENAME('IMG', 'image1.gif')) INSERT
INTO lob_table VALUES (22, NULL, NULL, NULL,
BFILENAME('IMG', 'image2.gif')) UPDATE
lob_table SET f_lob BFILENAME('IMG',
'image3.gif') WHERE key_value 22
9
DBMS_LOB Package

• Provides routines to access BLOBs, CLOBs, NCLOBs,
  and BFILEs.
• For external LOBs, you must ensure that a
  DIRECTORY object that represents a valid,
  existing physical directory has been defined, and
  physical files exist with read permission for
  Oracle.