Creating Tables Containing Large Objects

I ll use three tables for the examples in this section:

• The clob_content table, which contains a CLOB column named clob_column . This column is used to store the character data contained in the textContent.txt file.

• The blob_content table, which contains a BLOB column named blob_column . This column is used to store the binary data stored in the binaryContent.doc file.

• The bfile_content table, which contains a BFILE column named bfile_column . This column is used to store pointers to the two external files.

  Note  

  I ve provided a SQL*Plus script named lob_schema.sql in the SQL directory where you unzipped the files for this book. This script may be run against an Oracle8 or higher database. This script creates a user named lob_user with a password of lob_password and creates the various items used in the first part of this chapter; this script also populates the tables with sample data. I ve also provided an additional script named lob_schema_10g.sql that is referred to later in this chapter (don t run this additional script yet).

The example tables contain a column to store the name of the file that was used to populate the LOB column. The three tables are defined as follows :

 CREATE TABLE clob_content (id INTEGER PRIMARY KEY,  clob_column CLOB NOT NULL); CREATE TABLE blob_content (id INTEGER PRIMARY KEY,  blob_column BLOB NOT NULL); CREATE TABLE bfile_content (id INTEGER PRIMARY KEY,  bfile_column BFILE NOT NULL);