Akadia Information Technology

Overview

This document introduces its reader into the subject of Binary Large Objects in Oracle 8i and lists the differences between internal and external Binary Large Objects (BLOBs). It should facilitate the decision on which type of BLOB to use in a specific application. Anyone interested in BLOBs and especially in the differences between internal and external BLOBs should read this article.

Introduction

Related Documents
 

When To Use BLOBs ?

Binary Large Objects or Large Objects (LOBs) in general, are used to store unstructured data. Unstructured data is data that cannot be decomposed into a relational schema. Examples are pictures in any format (like GIF, JPEG, etc.), written documents (like Microsoft Word, WordPerfect, etc.) or multimedia content as audio and video files.

LOB Datatype

LOB datatypes consist of the LOB locator (pointer to the LOB data; always stored in-line within the table row) and the LOB value (actual LOB data; stored in-line, in another tablespace or outside of the database within the file system).
  

Oracle 8i Release 2 supports

• Internal LOBs and
• External LOBs.

General Characteristics

LOB datatypes have the following characteristics:

• Up to four (4) GB of data can be stored in LOB columns.
• Multiple LOB columns of the same or different type are allowed in a 
  single database table.
• LOBs support random piece-wise access to their data.
• They can be broken into chunks to minimise network round trips.
• Selecting a LOB column returns the LOB locator, not the LOB value.

One of the programmatic environments (see below) has to get used 
to access the LOB value.

Restrictions

The following restrictions constrain the use of LOBs:

• No support for distributed LOBs.

Remote LOB locators cannot be used in a SELECT or WHERE clause, including DBMS_LOB package functions.

• No support in clustered tables.
• LOBs are not supported in the clauses GROUP BY, ORDER BY, SELECT DISTINCT.
• No support in aggregations.
• No support in JOIN conditions.
• LOBs are not supported in partitioned, index-organised tables.
• No support in VARRAYs.
• No support in ANALYZE or ESTIMATE statements.
• Restrictions apply in a trigger body (see [APPDEVLOB] for details).

Internal LOBs

Internal LOBs are stored within the database, either in-line in the table
or in a separate tablespace.

The supported datatypes are:

• Binary LOB (BLOB)
  Used to store binary (raw) data like formatted text, image, audio or video content.
• Character LOB (CLOB)
  Unformatted character data, using the predefined database character set.
• National Character LOB (NCLOB)
  Unformatted character data, using the predefined national database character set.

They are further divided into persistent and temporary LOBs. This document does only concentrate on persistent BLOBs. Although most of the information applies to any type of LOB.

Characteristics

Internal LOBs have the following characteristics:

• LOBs smaller than four (4) KB are stored in-line in the table row.

By default, any LOB that is smaller than four (4) KB is stored in-line with the table row. Once it grows bigger, it is automatically moved out of the table. This behaviour enables fast access to small LOB columns.

If there are lots of accesses to the other (non-LOB) columns of the table, particularly full table scans, this default behaviour should be turned off by specifying DISABLE STORAGE IN ROW.

• They use copy semantics.

When inserting or updating a LOB with another LOB, the LOB value is copied. Each row has its own, separate copy of the LOB value. There will be two different LOB locators and two copies of the LOB value.

• They are part of transactions.

Changes to a LOB can be committed or rolled back. In the event of a database failure, they can get recovered. (This is just like the normal behaviour of all other database objects).

External LOBs

External LOBs are stored in operating system files, outside of the database. The supported datatypes are:

• Binary File (BFILE)
  Used to store binary (raw) data like formatted text, image, audio or video content and character data.

Characteristics

External LOBs (BFILEs) have the following characteristics:

• They are stored outside of the database, as operating system files.
  The LOB locator is a pointer to the file.
  
• They use reference semantics.
  When inserting or updating a LOB with another LOB, only the LOB locator is copied, not the actual LOB data. There will be two identical LOB locators and only one copy of the LOB value (operating system file).
  
• External LOBs allow read-only byte stream access to the operating system file.
  The operating system of the database server must support stream-mode access to the file. The files must be managed by the client application directly. I.e. before you insert a reference to a file, it should exist in the file system. Trying to access a BFILE that points to a non-existent operating system file will result in an error.
  
• They do not participate in transactions.
  Integrity must be supported by the underlying file system and the client application.
  
• A single external LOB must reside on a single device.
  E.g. striping across disk arrays is not supported.
  
• Only a limited number of BFILEs can be open simultaneously per session.
  The database initialisation parameter SESSION_MAX_OPEN_FILES defines the 
  upper limit (default value is 10).
  
• The size of an external LOB is constrained to four (4) GB by the database (as for internal LOBs), and further by the operating system maximum file size.

Programmatic Environments

Six (6) programmatic environments are supported to operate on LOBs. They can only work on LOBs that have been initialised to either actual data or empty, not on NULL values.

The programmatic environments are:

• PL/SQL (package DBMS_LOB),
• C using OCI,
• C/C++ using Pro*C,
• COBOL using Pro*COBOL,
• Visual Basic using Oracle Objects for OLE and
• Java using JDBC.

Not every environment supports the full set of features. For a complete
list please see [APPDEVLOB].

In Java, for example, the JDBC API is used to work with LOBs. oracle.sql.BLOB for internal binary LOBs and oracle.sql.BFILE for external LOBs. For a complete reference please see [APPDEVLOB] and [JDBC].

It is highly recommended to create an abstraction layer on top of the APIs for internal and external LOB manipulation. This will allow for switching between the two types quite easily.

Internal Versus External LOBs

This chapter compares internal LOBs to external LOBs. However, it does not try to judge them, as the decision on whether to use internal or external LOBs heavily depends on the application and its use of the LOB data.

JDBC Code Examples

This chapter shows code snippets of the most important methods on internal and external BLOBs in Java. For more examples and a complete description of the API, please see [APPDEVLOB].

The examples are part of the provided sample application (see below).

Internal BLOBs

As discussed above, internal BLOBs are stored entirely within the database. For storing or retrieving them, it does not matter whether their data is stored in-line with the table row or in another tablespace, this is transparent to the application programmer.

Insert

The following code snippet reads a file and stores it into the database:

// variables
String strStatement;
Statement stmt = null;
ResultSet resSet = null;
InputStream sampleFileStream = null;
OutputStream blobOutputStream = null;
try {
  // create a statement (connection is already existent)
  stmt = dbConn.createStatement();
  // get new id
  strStatement = " SELECT lobsamp_seq.nextval"
               + " FROM   dual";
  resSet = stmt.executeQuery(strStatement);
  if (resSet.next()) {
    lngId = new Long(resSet.getLong(1));
  }
  // insert new row
  // The LOB column value is initialized to empty in this step,
  // and will be loaded in the steps below.
  stmt.execute(" INSERT INTO lobsamp "
             + " (id"
             + " ,blob_col"
             + " ) VALUES "
             + " (" + lngId
             + " ,empty_blob()"
             + " )");
  // Retrieve BLOB locator
  strStatement = " SELECT blob_col"
               + " FROM   lobsamp"
               + " WHERE  id = " + lngId
               + " FOR UPDATE";
  resSet = stmt.executeQuery(strStatement);
  if (resSet.next()) {
    // Get the BLOB locator and open output stream for the BLOB
    BLOB bCol = ((OracleResultSet)resSet).getBLOB(1);
    blobOutputStream = bCol.getBinaryOutputStream();
    // Open the sample file as a stream for insertion
    // into the BLOB column
    File file2Load = new File(strDirectory + strFile);
    sampleFileStream = new FileInputStream(file2Load);
    // Buffer to hold chunks of data to being written to the BLOB.
    byte[] bBuffer = new byte[bCol.getBufferSize()*NUMCHUNKS];
    // Read a chunk of data from the sample file input stream,
    // and write the chunk to the BLOB column output stream.
    // Repeat till file has been fully read.
    int intBytesRead = 0;
    // read from file until done
    while ((intBytesRead = sampleFileStream.read(bBuffer)) != -1) {
      // write to BLOB
      blobOutputStream.write(bBuffer,0,intBytesRead);
    }
    // closing the streams and committing
    sampleFileStream.close();
    blobOutputStream.close();
    dbConn.commit();
  }
} catch (Exception ex) {
  try { dbConn.rollback(); } catch (Exception e) {}
} finally {
  try { sampleFileStream.close(); } catch (Exception e) {}
  try { blobOutputStream.close(); } catch (Exception e) {}
  try { stmt.close(); } catch (Exception e) {}
}

Read

The code below reads an internal BLOB from the database and stores it into
an operating system file:

// variables
String strStatement = null;
Statement stmt = null;
ResultSet resSet = null;
BLOB bCol = null;
String strFileName = null;
String strDirectory = null;
InputStream blobInputStream = null;
OutputStream sampleFileStream = null;
try {
  // select the blob locator from the database
  strStatement = " SELECT name"
               + " ,      blob_col"
               + " FROM   lobsamp"
               + " WHERE  id = " + lngId;
  stmt = dbConn.createStatement();
  resSet = stmt.executeQuery(strStatement);
  if (resSet.next()) {
    strFileName = resSet.getString("name");
    // get the locator
    bCol = ((OracleResultSet)resSet).getBLOB("blob_col");
  }
  // Save as dialog box to get directory and file name to save as
  FileDialog selFile = new FileDialog(mainFrame, 
  "Save as", FileDialog.SAVE);
  selFile.setFile(strFileName);
  selFile.show();
  strDirectory = selFile.getDirectory();
  strFileName = selFile.getFile();
  // get the input stream
  blobInputStream = bLocator.getBinaryStream();
  // Open the file to write to
  File file2Save = new File(strDirectory + strFile);
  sampleFileStream = new FileOutputStream(file2Save);
  // get the buffer size to use
  int intBufferSize = bLocator.getBufferSize()*NUMCHUNKS;
  // Buffer to hold chunks of data to read from the BLOB.
  byte[] bBuffer = new byte[intBufferSize];
  // Read a chunk of data from the BLOB, and 
  // write the chunk to the file.
  int intBytesRead = 0;
  while ((intBytesRead = blobInputStream.read(bBuffer)) != -1) {
    sampleFileStream.write(bBuffer,0,intBytesRead);
  }
} catch (Exception ex) {
  System.err.println("Saving LOB data: " + ex.toString());
} finally {
  // close input stream, output stream and statement
  if (sampleFileStream != null) {
    try { sampleFileStream.close(); } catch (Exception e) {
      System.err.println("Could not close file output stream!"); }
  }
  if (blobInputStream != null) {
    try { blobInputStream.close(); } catch (Exception e) {
      System.err.println("Could not close blob input stream!"); }
  }
  try { stmt.close(); } catch (Exception e) {
    System.err.println("Could not close the statement!"); }
}

External BLOBs (BFILEs)

External BLOBs do only store a reference to the operating system file within the database. The reference is done using a DIRECTORY database object and a file name.

The application programmer must ensure that the file exists and that Oracle processes have operating system read permissions on the file (although this is only checked when trying to access the file, not on insertion of a BFILE object).

The Directory Database Object

A directory object specifies an alias for a directory on the server’s file system where external binary file LOBs (BFILEs) are located.

All directories are created in a single namespace and are not owned by an individual schema. You can secure access to the BFILEs stored within the directory structure by granting object privileges on the directories to specific users. When you create a directory, you are automatically granted the READ object privilege and can grant READ privileges to other users and roles.

You must have CREATE ANY DIRECTORY system privileges to create directories.

Example:

CREATE OR REPLACE DIRECTORY bfile_dir AS ’/data1/LOB/files’;

Insert

The following code snippet stores a reference to an operating system file that is located within the directory BFILE_DIR into the database:

// variables
Statement stmt = null;
ResultSet resSet = null;
try {
  // create a statement
  stmt = dbConn.createStatement();
  // insert new row
  stmt.execute(" INSERT INTO lobsamp "
             + " (id"
             + " ,name"
             + " ,bfile_col"
             + " )"
             + " SELECT lobsamp_seq.nextval"
             + " ,      '" + strFile + "'"
             + " ,      bfilename('BFILE_DIR','" + strFile + "')"
             + " FROM   dual");
} catch (Exception ex) {
  System.err.println("Error loading BFILE data: " + ex.toString());
  try { dbConn.rollback(); } catch (Exception e) {}
} finally {
  // close the statement
  try { stmt.close(); } catch (Exception e) {}
}

Read

The code below reads an external BLOB (BFILE) via the database and stores it into another operating system file:

// variables
String strStatement = null;
Statement stmt = null;
ResultSet resSet = null;
BFILE bFile = null;
String strFileName = null;
String strDirectory = null;
InputStream bfileInputStream = null;
OutputStream sampleFileStream = null;
try {
  // select the bfile locator from the database
  strStatement = " SELECT name"
               + " ,      bfile_col"
               + " FROM   lobsamp"
               + " WHERE  id = " + lngId;
  stmt = dbConn.createStatement();
  resSet = stmt.executeQuery(strStatement);
  if (resSet.next()) {
    strFileName = resSet.getString("name");
    // get the locator
    bFile = ((OracleResultSet)resSet).getBFILE("bfile_col");
  }
  // Save as dialog box to get directory and file name to save as
  FileDialog selFile = new FileDialog(mainFrame,
  "Save as", FileDialog.SAVE);
  selFile.setFile(strFileName);
  selFile.show();
  strDirectory = selFile.getDirectory();
  strFileName = selFile.getFile();
  // show directory alias
  System.out.println("... directory alias: "
                    + bLocator.getDirAlias() + " ...");
  // show file name
  System.out.println("... file name: "
                    + bLocator.getName() + " ...");
  // check if file exists
  System.out.println("... does the file exist: "
                    + bLocator.fileExists() + " ...");
  // show file length
  System.out.println("... file length = "
                    + bLocator.length() + " ...");
  // open the file and get the stream
  bLocator.openFile();
  bfileInputStream = bLocator.getBinaryStream();
  // open the file to write to
  File file2Save = new File(strDirectory + strFile);
  sampleFileStream = new FileOutputStream(file2Save);
  // define the buffer size
  int intBufferSize = BFILEBUFSIZE*NUMCHUNKS;
  // buffer to hold chunks of data to read from the BFILE
  byte[] bBuffer = new byte[intBufferSize];
  // read a chunk of data from the BFILE, and write
  // the chunk to the file.
  int intBytesRead = 0;
  while ((intBytesRead = bfileInputStream.read(bBuffer)) != -1) {
    sampleFileStream.write(bBuffer,0,intBytesRead);
  }
} catch (Exception ex) {
  System.err.println("Saving BFILE data: " + ex.toString());
} finally {
  // close input stream, output stream, bfile and statement
  if (sampleFileStream != null) {
    try { sampleFileStream.close(); } catch (Exception e) {
      System.err.println("Could not close file output stream!"); }
  }
  if (bfileInputStream != null) {
    try { bfileInputStream.close(); } catch (Exception e) {
      System.err.println("Could not close bfile input stream!"); }
  }
  try { bLocator.closeFile(); } catch (Exception e) {
    System.err.println("Could not close bfile file!"); }
  try { stmt.close(); } catch (Exception e) {
    System.err.println("Could not close the statement!"); }
}

List of References