BLOB

BLOB stands for binary large object. This datatype holds raw binary data that the database does not understand without the use of data methods. The data methods interpret the binary data and make it usable within the database. The binary data will not be converted with other character set conversions. Any character sets of the objects in the BLOB can be converted with methods. BLOB data participates fully in transactions. This means that changes to BLOB data may be rolled back or committed along with the other types of data.

CLOB

CLOB stands for character large object. It stores up to 4GB of single-character data. A CLOB is used to store large text items such as documents and Web pages. Like the BLOB, the CLOB fully participates in transactions.

NCLOB

NCLOB stands for national character large object. It is similar to a CLOB except that it has the ability to store fixed-width, multi-byte character data that corresponds to a national character set.

BFILE

The BFILE is used to give Oracle8 access to files stored outside the database. The database stores a pointer to the BFILE inside the table. BFILEs can reside on operating system devices such as disks and CD-ROMs. Due to the fact that BFILE resides outside the database, Oracle8 does not support transaction consistency with BFILEs. This is irrelevant, however, because BFILEs are also read only. Oracle8 uses directories as a layer between the operation system and the BFILE object. An Oracle8 directory is created with the CREATE DIRECTORY statement.

CREATE DIRECTORY company_logos AS

‘/u01/company_pictures/logos’;

The extra layer between the BFILE and the operating system allows flexibility. If the logos are moved to another directory, the DBA will simply issue this command.

CREATE OR REPLACE DIRECTORY company_logos AS

‘/u02/pictures/company_logos’;

No tables will need to be updated to reflect the change in directories.

---

CAUTION:  
Do not grant the CREATE ANY DIRECTORY to anyone outside of the DBA group. This privilege allows the user to point to any directory on the operating system that the Oracle user has permission to access. Operating system security can be violated by granting this privilege.

---

Creating Tables with LOBs

Creating a table with a LOB (the generic name for this group of datatypes) is quite simple. The following example shows the creation of a résumé table.

CREATE TABLE resume_table (

first_name    varchar(20),

last_name    varchar(20),

resume        CLOB

);

Unlike the LONG and LONG RAW datatypes, multiple LOBs may exist in each table.

CREATE TABLE candidate (

first_name    varchar(20),

last_name    varchar(20),

picture    BLOB,

resume    CLOB,

acceptance_letter    BFILE

);

It is beneficial to use the storage options of the LOBs. In Listing 15.10, there is a picture (BLOB) and a résumé (CLOB). Each has a separate STORE AS clause. The STORE AS clause has several parameters, as listed in Table 15.2.

The following CREATE TABLE statement uses the STORE AS clause.

Listing 15.10. CREATE TABLE statement.

CREATE TABLE candidate (

first_name varchar(20),

last_name varchar(20),

picture BLOB,

pesume CLOB,

acceptance_letter BFILE

)



—Options specific to the Picture BLOB

LOB (picture)

STORE AS (TABLESPACE picture_blob

STORAGE (initial 500K next 500k)

ENABLE STORAGE IN ROW

CACHE

LOGGING

CHUNK 4

PCTVERSION 15

INDEX (TABLESPACE picture_indexes))



—Options specific to the Resume CLOB

LOB (resume)

STORE AS (TABLESPACE resume_clob

STORAGE (initial 50K next 50K)

ENABLE STORAGE IN ROW

NOCACHE

NOLOGGING

CHUNK 2

PCTVERSION 5

INDEX (TABLESPACE resume_indexes)

); 

Figure 15.7 shows the how the candidate table data is stored. The picture and résumé LOBs are stored inside the database; the acceptance letter BFILE is stored in operating system files.

Summary

The object-oriented features of Oracle8 have added to the versatility and complexity of the Oracle database. Used properly, objects can improve application speed and enhance the productivity of application development. Objects are designed to model the real world and will continue to make strides in the database arena.