5
Introduction to LOBs

The following topics are covered in this chapter:

• Overview of LOBs
• LOB Classes and Methods
• Objects with LOB Attributes

Overview of LOBs

Oracle C++ Call Interface (OCCI) includes classes and methods for performing operations on large objects (LOBs). LOBs are either internal or external depending on their location with respect to the database.

Internal LOBs (BLOBs, CLOBs, and NCLOBs)

Internal LOBs are stored inside database tablespaces in a way that optimizes space and enables efficient access. Internal LOBs use copy semantics and participate in the transactional model of the server. You can recover internal LOBs in the event of transaction or media failure, and any changes to an internal LOB value can be committed or rolled back. In other words, all the ACID^{Foot 1} properties that pertain to using database objects also pertain to using internal LOBs.

Internal LOB Datatypes

There are three SQL datatypes for defining instances of internal LOBs:

• BLOB: A LOB whose value is composed of unstructured binary (raw) data
• CLOB: A LOB whose value is composed of character data that corresponds to the database character set defined for the Oracle database
• NCLOB: A LOB whose value is composed of character data that corresponds to the national character set defined for the Oracle database

Copy Semantics

Internal LOBs, whether persistent or temporary, use copy semantics. When you insert or update a LOB with a LOB from another row in the same table, the LOB value is copied so that each row has a copy of the LOB value.

The use of copy semantics results in both the LOB locator and the LOB value being copied, not just the LOB locator.

Internal LOBs are divided into persistent LOBs and temporary LOBs.

External LOBs (BFILEs)

External LOBs (BFILES) are large binary data objects stored in operating system files outside database tablespaces. These files use reference semantics. Apart from conventional secondary storage devices such as hard disks, BFILEs may also be located on tertiary block storage devices such as CD-ROMs, PhotoCDs and DVDs.

The BFILE datatype allows read-only byte stream access to large files on the file system of the database server.

Oracle can access BFILEs if the underlying server operating system supports stream mode access to these operating system files.

External LOB Datatypes

There is one SQL datatype for declaring instances of external LOBs, called a BFILE. A BFILE is a LOB whose value is composed of binary (raw) data and is stored outside the database tablespaces in a server-side operating system file.

Reference Semantics

External LOBs (BFILEs) use reference semantics. When a BFILE associated with a column of a row in a table is copied to another column, only the BFILE locator is copied, not the actual operating system file that houses the BFILE.

LOB Values and Locators

The size of the LOB value, among other things, dictates where it is stored. The LOB value is either stored inline with the row data or outside the row.

A LOB locator is stored inline with the row data and indicates where the LOB value is stored.

Inline Storage of the LOB Value

Data stored in a LOB is termed the LOB value. The value of an internal LOB may or may not be stored inline with the other row data. If you do not set DISABLE STORAGE IN ROW, and if the internal LOB value is less than approximately 4,000 bytes, then the value is stored inline.Otherwise, it is stored outside the row. Since LOBs are intended to be large objects, inline storage will only be relevant if your application mixes small and large LOBs.

The LOB value is automatically moved out of the row once it extends beyond approximately 4,000 bytes.

LOB Locators

Regardless of where the value of the internal LOB is stored, a LOB locator is stored in the row. You can think of a LOB locator as a pointer to the actual location of the LOB value. A LOB locator is a locator to an internal LOB while a BFILE locator is a locator to an external LOB.

• Internal LOB Locators: For internal LOBs, the LOB column stores a locator to the LOB value stored in a database tablespace. Each internal LOB column and attribute for a given row has its own unique LOB locator and a distinct copy of the LOB value stored in the database tablespace.
• External LOB Locators: For external LOBs (BFILEs), the LOB column stores a locator to the external operating system file that houses the BFILE. Each external LOB column and attribute for a given row has its own BFILE locator. However, two different rows can contain a BFILE locator that points to the same operating system file.

LOB Classes and Methods

The classes and methods in Table 5-1are available for LOB operations.

Table 5-1 OCCI LOB Classes and Methods  

Class	Method	Purpose
Bfile Class	close() closeStream() fileExists() getDirAlias() getFileName() getStream() isInitialized() isNull() isOpen() length() open() operator=() operator==() operator!=() read() setName() setNull()	To access data in external LOBs (BFILEs)
Blob Class	append() close() closeStream() copy() getChunkSize() getStream() isInitialized() isNull() isOpen() length() open() operator=() operator==() operator!= () read() setEmpty() setNull() trim() write() writeChunk()	To manipulate internal LOB (BLOB) values and locators
Clob Class	append() close() closeStream() copy() getCharSetForm() getCharSetId() getChunkSize() getStream() isInitialized() isNull() isOpen() length() open() operator=() operator==() operator!=() read() setCharSetForm() setNull() trim() write() writeChunk()	To manipulate internal LOB (CLOB and NCLOB) values and locators

Creating LOBs

To create an internal or external LOB, initialize a new LOB locator in the database. Based on the type of LOB you want to create, use one of the following classes:

• Bfile
• Blob
• Clob

You can then use the related methods, as appropriate, to access the LOB value.

For any LOB write command to be successful, a transaction must be open. This means that if you commit a transaction before writing the data, then you must lock the row again (by reissuing the SELECT ... FOR UPDATE statement, for example), because the COMMIT closes the transaction.

Opening and Closing LOBs

OCCI provides methods to explicitly open and close internal and external LOBs:

• Bfile::open() and Bfile::close()
• Blob::open() and Blob::close()
• Clob::open() and Clob::close()

Additional methods are available to check whether a particular LOB is already open:

• Bfile::isOpen()
• Blob::isOpen()
• Clob::isOpen()

These methods allow an OCCI application to mark the beginning and end of a series of LOB operations so that specific processing (for example, updating indexes, and so on) can be performed when a LOB is closed.

If an application does not wrap LOB operations between a set of open() and close() method calls, then each modification to the LOB implicitly opens and closes the LOB, thereby firing any triggers associated with changes to the LOB.

Restrictions for Opening and Closing LOBs

The LOB opening and closing mechanism has the following restrictions:

• An application must close all previously opened LOBs before committing a transaction. Failing to do so results in an error. If a transaction is rolled back, then all open LOBs are discarded along with the changes made (the LOBs are not closed), so associated triggers are not fired.
• While there is no limit to the number of open internal LOBs, there is a limit on the number of open files. Note that assigning an already opened locator to another locator does not count as opening a new LOB.
• It is an error to open or close the same internal LOB twice within the same transaction, either with different locators or with the same locator.
• It is an error to close a LOB that has not been opened.

  Note: The definition of a transaction within which an open LOB value must be closed is one of the following: Between SET TRANSACTION and COMMIT Between DATA MODIFYING DML or SELECT ... FOR UPDATE and COMMIT Within an autonomous transaction block

  A LOB opened when there is no transaction must be closed before the end of session. If there are LOBs open at the end of session, then the openness is discarded and no triggers of extensible indexes are fired.

Reading and Writing LOBs

OCCI provides methods for reading and writing LOBS. For nonstreamed reads and writes, the following methods are used:

• Bfile::read()
• Blob::read() and Blob::write()
• Clob::read() and Clob::write()

For streamed reads and writes, the following methods are used:

• Bfile::getStream()
• Blob::getChunkSize(), Blob::getStream(), and Blob::writeChunk()
• Clob::getChunkSize(), Clob::getStream(), and Clob::writeChunk()

The remainder of this section provides code examples for streamed and unstreamed reads and writes.

Nonstreamed Read

The following code example demonstrates how to obtain data from an internal LOB (in this example, a BLOB) that is not null by using a nonstreamed read:

ResultSet *rset=stmt->executeQuery("SELECT ad_composite FROM print_media
                                    WHERE product_id=6666");
while(rset->next())
{
   Blob blob=rset->getBlob(1);
   if(blob.isNull())
     cerr <<"Null Blob"<<endl;
   else
   {
      blob.open(OCCI_LOB_READONLY);

      const unsigned int BUFSIZE=100;
      char buffer[BUFSIZE];
      unsigned int readAmt=BUFSIZE;
      unsigned int offset=1;

      //reading readAmt bytes from offset 1
      blob.read(readAmt,buffer,BUFSIZE,offset);

      //process information in buffer
      .
      .
      .
      blob.close();
   }
}
stmt->closeResultSet(rset);

Reading all information from a BLOB without using streams, as in the preceding code example, requires that you keep track of the read offset and the amount remaining to be read, and pass these values to the read() method.

The following code example demonstrates how to read data from a BFILE, where the BFILE locator is not null, by using a nonstreamed read:

ResultSet *rset=stmt->executeQuery("SELECT ad_graphic FROM print_media
                                    WHERE product_id=6666");
while(rset->next())
{
   Bfile file=rset->getBfile(1);
   if(bfile.isNull())
      cerr <<"Null Bfile"<<endl;
   else
   {
      //display the directory alias and the file name of the BFILE
      cout <<"File Name:"<<bfile.getFileName()<<endl;
      cout <<"Directory Alias:"<<bfile.getDirAlias()<<endl;

      if(bfile.fileExists())
      {
         unsigned int length=bfile.length();
         char *buffer=new char[length];
         bfile.read(length, buffer, length, 1);
         //read all the contents of the BFILE into buffer, then process
         .
         .
         .
         delete[] buffer;
      }
      else
         cerr <<"File does not exist"<<endl;
   }
}
stmt->closeResultSet(rset);

Nonstreamed Write

The following code example demonstrates how to write data to an internal LOB (in this example, a BLOB) that is not null by using a nonstreamed write:

ResultSet *rset=stmt->executeQuery("SELECT ad_composite FROM print_media
                                    WHERE product_id=6666 FOR UPDATE");
while(rset->next())
{
   Blob blob=rset->getBlob(1);
   if(blob.isNull())
      cerr <<"Null Blob"<<endl;
   else
   {
      blob.open(OCCI_LOB_READWRITE);

      const unsigned int BUFSIZE=100;
      char buffer[BUFSIZE];
      unsigned int writeAmt=BUFSIZE;
      unsigned int offset=1;

      //writing writeAmt bytes from offset 1
      //contents of buffer are replaced after each writeChunk(),
      //typically with an fread()
      while(<fread "BUFSIZE" bytes into buffer succeeds>)
      {
         blob.writeChunk(writeAmt, buffer, BUFSIZE, offset);
         offset += writeAmt;
      }
      blob.writeChunk(<remaining amt>, buffer, BUFSIZE, offset);

      blob.close();
   }
}
stmt->closeResultSet(rset);
conn->commit();

In the preceding code example, the writeChunk() method is enclosed by the open() and close() methods. The writeChunk() method operates on a LOB that is currently open and ensures that triggers do not fire for every chunk read. The write() method can be used in place of the writeChunk() method in the preceding example; however, the write() method implicitly opens and closes the LOB.

Streamed Read

The following code example demonstrates how to obtain data from an internal LOB (in this example, a BLOB) that is already populated by using a streamed read:

ResultSet *rset=stmt->executeQuery("SELECT ad_composite FROM print_media
                                    WHERE product_id=6666");
while(rset->next())
{
   Blob blob=rset->getBlob(1);
   if(blob.isNull())
      cerr <<"Null Blob"<<endl;
   else
   {
      Stream *instream=blob.getStream(1,0);
      //reading from offset 1 to the end of the BLOB

      unsigned int size=blob.getChunkSize();
      char *buffer=new char[size];

      while((unsigned int length=instream->readBuffer(buffer,size))!=-1)
      {
         //process "length" bytes read into buffer
         .
         .
         .
      }
      delete[] buffer;
      blob.closeStream(instream);
   }
}
stmt->closeResultSet(rset);

Streamed Write

The following code example demonstrates how to write data to an internal LOB (in this example, a BLOB) that is already populated by using a streamed write:

ResultSet *rset=stmt->executeQuery("SELECT ad_composite FROM print_media
                                    WHERE product_id=6666 FOR UPDATE");
while(rset->next())
{
   Blob blob=rset->getBlob(1);
   if(blob.isNull())
      cerr <<"Null Blob"<<endl;
   else
   {
      char buffer[BUFSIZE];
      Stream *outstream=blob.getStream(1,0);

      //writing from buffer beginning at offset 1 until 
      //a writeLastBuffer() method is issued.
      //contents of buffer are replaced after each writeBuffer(),
      //typically with an fread()
      while(<fread "BUFSIZE" bytes into buffer succeeds>)
         ostream->writeBuffer(buffer,BUFSIZE);
      ostream->writeLastBuffer(buffer,<remaining amt>);
      blob.closeStream(outstream);
   }
}
stmt->closeResultSet(rset);
conn->commit();

Improving Read and Write Performance

Read and write performance of internal LOBs can be improved by using either of the following methods:

• getChunkSize() method
• writeChunk() method

When to Use the getChunkSize() Method

Take advantage of the getChunkSize() methods of the Blob and Clob classes to improve the performance of internal LOB read and write operations. The getChunkSize() method returns the usable chunk size in bytes for BLOBs and in characters for CLOBs and NCLOBs. When a read or write is done by using data whose size is a multiple of the usable chunk size and starts on a chunk boundary, performance improves. You can specify the chunk size for a LOB column when you create a table that contains the LOB.

Calling the getChunkSize() method returns the usable chunk size of the LOB. An application can batch a series of write operations until an entire chunk can be written, rather than issuing multiple LOB write calls that operate on the same chunk.

To read through the end of a LOB, use the read() method with an amount of 4 GB. This avoids the round-trip involved with first calling the getLength() method because the read() method with an amount of 4 GB reads until the end of the LOB is reached.

When to Use the writeChunk() Method

OCCI provides a shortcut to make it more efficient to write data to the end of a LOB. The writeAppend() methods of the Blob and Clob classes enables an application to append data to the end of a LOB without first requiring a call to the getLength() method to determine the starting point for a call to the write() method.

Updating LOBs

The following code example demonstrates how to update an internal LOB (in this example, a CLOB) to empty:

Clob clob(conn);
clob.setEmpty();
stmt->setSQL("UPDATE print_media SET ad_composite = :1
              WHERE product_id=6666");
stmt->setClob(1, clob);
stmt->executeUpdate();
conn->commit();

The following code example demonstrates how to update a BFILE:

Bfile bfile(conn);
bfile.setName("MEDIA_DIR", "img1.jpg");
stmt->setSQL("UPDATE print_media SET ad_graphic = :1 
              WHERE product_id=6666");
stmt->setBfile(1, bfile);
stmt->executeUpdate();
conn->commit();

Objects with LOB Attributes

An OCCI application can use the overloaded operator new() to create a persistent or transient object with a LOB attribute. By default, all LOB attributes are constructed by using the default constructor and initialized to null.

Persistent Objects with LOB Attributes

It is possible to use OCCI to create a new persistent object with a LOB attribute. To do so, follow these steps:

1. Create a persistent object with a LOB attribute.

       Person *p=new(conn,"PERSON_TAB")Person();
   
   

2. Initialize the Blob object to empty.

       p->imgBlob = Blob(conn);
       p->imgBlob.setEmpty();
   
   

   If appropriate, then use the corresponding methods (setxxx methods and getxxx methods) on the Person object to accomplish the same thing.

3. Mark the Blob object as dirty.

       p->markModified();
   
   

4. Flush the object.

       p->flush();
   
   

5. Repin the object after obtaining a REF to it, thereby retrieving a refreshed version of the object from the database and acquiring an initialized LOB.

       Ref<Person> r = p->getRef();
       delete p;
       p = r.ptr();
   
   

6. Write the data.

       p->imgBlob.write( ... );
   

To create a persistent object with BFILE attributes, follow these steps:

1. Create a persistent object with a LOB attribute.

       Person *p=new(conn,"PERSON_TAB")Person();
   
   

2. Initialize the Bfile object to empty.

       p->imgBfile = Bfile(conn);
       p->setName(<Directory Alias>,<File Name>);
   
   

3. Mark the object as dirty.

       p->markModified();
   
   

4. Flush the object.

       p->flush();
   
   

5. Read the data.

       p->imgBfile.read( ... );
   

Transient Objects with LOB Attributes

An application can call the overloaded new() method and create a transient object with an internal LOB (BLOB, CLOB, NCLOB) attribute. However, you cannot perform any operations (for example, read or write) on the LOB attribute because transient LOBs are not currently supported. Calling the overloaded new() method to create a transient internal LOB type does not fail, but the application cannot use any LOB operations with the transient LOB.

An application can, however, create a transient object with a FILE attribute and use the FILE attribute to read data from the file stored in the server's file system. The application can also call the overloaded new() method to create a transient FILE and use that FILE to read from the server's file.

---

¹ ACID = Access Control Information Directory. This is the attribute that determines who has what type of access and to what directory data. It contains a set of rules for structural and content access items. For more information, refer to the Oracle Internet Directory Administrator's Guide.