LOB Locator and LOB Value

There are two methods that could be used to access and modify LOB values:

• Using the Data Interface for LOBs
• Using the LOB Locator to Access/Modify LOB Values

Using the Data Interface for LOBs

You can perform bind and define operations on BLOB and CLOB columns in C applications using the data interface for LOBs.It enables you to insert or select out data in a LOB column without using a LOB locator are as follows:

• With the help of bind variable associated with a LOB column to insert character data into a CLOB, or RAW data into a BLOB.
• Define operation is used to define an output buffer in your application that holds RAW data selected from a BLOB, or character data selected from a CLOB.

Using the LOB Locator to Access/Modify LOB Values

The value of a LOB instance which is stored in the database can be accessed through a LOB locator, a reference to the location of the LOB value. Database tables store only locators in CLOB, BLOB, NCLOB and BFILE columns. Below are the important points with respect to LOB locators and values:

• To manipulate or access a LOB value, you can pass the LOB locator to the various LOB APIs.
• A LOB locator can be easily assigned or reassigned to any LOB instance of the same type.
• The characteristics of a LOB as being temporary or persistent have nothing to do with the locator. The characteristics of temporary or persistent is only applicable to the LOB instance.

LOB Locators and BFILE Locators

There are few differences between the semantics of locators for LOB types CLOB, NCLOB and BLOB on one hand, and the semantics of locators for the BFILE type on the other hand:

• For LOB types CLOB, NCLOB and BLOB, the LOB column stores a locator to the LOB value. Each LOB instance has its own distinct copy of the LOB value and also a distinct LOB locator.
• For initialized BFILE columns, the row stores a locator to the external operating system file that holds the value of the BFILE. Each BFILE instance in a given row has its own distinct locator; however, two different rows can contain a BFILE locator that points to the same operating system file.

Regardless of where the value of a LOB is stored, a locator is stored in the table row of any initialized LOB column. Whenever the term locator is used without an identifying prefix term, it refers to both LOB locators and BFILE locators. Also, whenever you select a LOB from a table, the LOB returned is always a temporary LOB. For more information on working with locators for temporary LOBs, see “LOBs Returned from SQL Functions”.

Table print_media The table print_media of the Oracle Database Sample Schema PM, is used as many examples and is defined as:

CREATE TABLE print_media
    ( product_id NUMBER(6), 
      ad_id NUMBER(6), 
      ad_composite BLOB, 
      ad_sourcetext CLOB, 
      ad_finaltext CLOB, 
      ad_fltextn NCLOB, 
      ad_textdocs_ntab textdoc_tab, 
      ad_photo BLOB, 
      ad_graphic BFILE, 
      ad_header adheader_typ
    ) 
NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;