Using Large Objects in SQL

In this section, you ll learn how to use large objects from within SQL, starting with CLOB and BLOB objects and followed by BFILE objects.

Using CLOBs and BLOBs

LOB columns store a locator that points to the LOB contents.

Initializing a CLOB and BLOB

Before you can actually write content to a LOB, you must first initialize the LOB column. You do this by calling an Oracle database function that generates and returns a value for the locator. To initialize a CLOB or NCLOB column, you use the EMPTY_CLOB() function. A BLOB column must be initialized using the EMPTY_BLOB() function.

The following INSERT statements use EMPTY_CLOB() to initialize clob_column of two new rows in the clob_content table:

 INSERT INTO clob_content(id, clob_column) VALUES (1, EMPTY_CLOB()); INSERT INTO clob_content (id, clob_column) VALUES (2, EMPTY_CLOB()); 

The next INSERT uses EMPTY_BLOB() to initialize blob_column in the blob_content table:

 INSERT INTO blob_content(id, blob_column) VALUES (1, EMPTY_BLOB()); 

Adding Content to a CLOB and BLOB

The following UPDATE statements set clob_column to some text for the two rows in clob_content :

 UPDATE clob_content SET clob_column = 'Creeps in this petty pace' WHERE id = 1; UPDATE clob_content SET clob_column = ' from day to day' WHERE id = 2; 

The following query retrieves the row from clob_content :

  SELECT *   FROM clob_content;  ID ---------- CLOB_COLUMN -------------------------  1 Creeps in this petty pace  2  from day to day 

The next UPDATE sets blob_column to a binary number:

 UPDATE blob_content SET blob_column = '100111010101011111' WHERE id = 1; 

The following query attempts to retrieve the contents of blob_content and fails:

  SELECT *   FROM blob_content;  SP2-0678: Column or attribute type can not be displayed by SQL*Plus 

This example fails because SQL*Plus cannot display the binary content in a BLOB . You ll learn how to access the content in a BLOB later in the section Using Large Objects in PL/SQL.

Using BFILEs

The BFILE LOB type enables you to store a pointer to a file that is accessible through the computer s file system. The important point to note is that these files are located outside of the database. BFILE columns can point to files located on any media: a hard disk, CD, DVD, and so on.

Note  

A BFILE contains a pointer to an external file. The actual file itself is not stored in the database, only a pointer to that file. The file must be accessible through the file system.

Creating a Directory Object

Before you can store a pointer to a file in a BFILE column, you must first create a directory object in the database that represents the directory in the file system where your files are stored. You create a directory object using the CREATE DIRECTORY statement. To perform such a statement, you must have the CREATE ANY DIRECTORY database privilege.

The following example creates a directory object named SAMPLE_FILES_DIR for the file system directory C:\sample_files :

 CREATE OR REPLACE DIRECTORY SAMPLE_FILES_DIR AS 'C:\sample_files'; 

Note  

Windows uses the backslash character ( \ ) in directories, while Linux and Unix use the forward slash character ( / ).

When you create a directory object you must ensure that

• The actual directory exists in the file system.

• The user account in the operating system that was used to install the Oracle software has read permission on the directory and on any files that are to be pointed to by a BFILE column in the database.

If you are using Windows, you shouldn t need to worry about the second point. The Oracle database software should have been installed using a user account that has administrator privileges, and such a user account has read permission on everything in the file system. If you are using Linux or Unix, you may have to grant read access to the physical directory and to the files, using the chmod command, for example.

Populating a BFILE Column with a Pointer to a File

Because a BFILE is just a pointer to an external file, populating a BFILE column is very simple. All you have to do is to use the Oracle database s BFILENAME() function to populate the BFILE column with a pointer to your external file. The BFILENAME() function accepts two parameters: the database directory object s name (which was created earlier) and the name of the file.

The following INSERT adds a row to the bfile_content table using the BFILENAME() function to populate bfile_column with a pointer to the textContent.txt file:

 INSERT INTO bfile_content (id,  bfile_column) VALUES (1,  BFILENAME('SAMPLE_FILES_DIR', 'textContent.txt')); 

A BFILE column doesn t care what format a file is stored in. This example stores a pointer to a text file. A similar statement can be used to add a row containing a pointer to a binary file. The next INSERT adds a row to the bfile_content table using the BFILENAME() function to populate bfile_column with a pointer to the binaryContent.doc file:

 INSERT INTO bfile_content (id,  bfile_column) VALUES (2,  BFILENAME('SAMPLE_FILES_DIR', 'binaryContent.doc')); 

The following query attempts to retrieve the rows from bfile_content and fails because SQL*Plus cannot display the content in a BFILE :

  SELECT *   FROM bfile_content;  SP2-0678: Column or attribute type can not be displayed by SQL*Plus 

In order to access the content in a BFILE (or a BLOB ) you may use PL/SQL, which you ll learn how to do next.