6
Working with Multimedia Datatypes

This chapter includes the following topics:

• Overview
• DDL for LOBs
• LOB Locators
• EMPTY_BLOB and EMPTY_CLOB Functions
• Using the OCI to Manipulate LOBs
• Using DBMS_LOB to Manipulate LOBs
• LOBs in External Procedures
• LOBs and Triggers
• Using Open/Close as Bracketing Operations for Efficient Performance

Overview

Some data cartridges need to handle large amounts of raw binary data, such as graphic images or sound waveforms, or character data, such as text or streams of numbers. Oracle supports large objects (LOBs) to handle these kinds of data.

Internal LOBs are stored in the database tablespaces in way that optimizes space and provides efficient access. Internal LOBs participate in the transactional model of the server. External LOBs are stored in operating system files outside the database tablespaces. External LOBs do not participate in transactions.

Internal LOBs can store binary data (BLOBs), single-byte character data (CLOBs), or fixed-width single-byte or multibyte character data (NCLOBs). An NCLOB consists of character data that corresponds to the national character set defined for the Oracle database. Varying-width character data is not supported in Oracle. External LOBs store only binary data (BFILEs). Together, internal and external LOBs provide considerable flexibility in handling large amounts of data.

Data stored in a LOB is called the LOB's value. To the Oracle server, a LOB's value is unstructured and cannot be queried. You must unpack and interpret a LOB's value in cartridge-specific ways.

LOBs can be manipulated using the Oracle Call Interface (OCI) or the PL/SQL DBMS_LOB package. You can write functions (including methods on object types that can contain LOBs) to manipulate parts of LOBs. Details on LOBs can be found in the Oracle9i Application Developer's Guide - Large Objects (LOBs).

DDL for LOBs

LOB definition can involve the CREATE TYPE and the CREATE TABLE statements. For example, the following statement specifies a CLOB within a datatype named lob_type:

CREATE OR REPLACE TYPE lob_type AS OBJECT ( 
         id  INTEGER, 
         data CLOB );

The following statement creates an object table (lob_table) in which each row is an instance of lob_type data:

CREATE TABLE lob_table OF lob_type;

The following statement stores LOBs in a regular table, as opposed to an object table as in the preceding statement:

CREATE TABLE lob_table1  (
                id  INTEGER,
                b_lob   BLOB,
                c_lob   CLOB,
                nc_lob  NCLOB,
                b_file  BFILE );

When creating LOBs in tables, you can set the LOB storage, buffering, and caching properties. See the Oracle9i SQL Reference manual and the Oracle9i Application Developer's Guide - Large Objects (LOBs) for information about using LOBs in the following DDL statements:

• CREATE TABLE and ALTER TABLE
  • LOB columns
  • LOB storage clause
  • NOCACHE and NOLOGGING options
• CREATE TYPE and ALTER TYPE
  • BLOB, CLOB and BFILE datatypes

LOB Locators

LOBs can be stored with other row data or separate from row data. Regardless of the storage location, each LOB has a locator, which can be viewed as a handle or pointer to the actual location. Selecting a LOB returns the LOB locator instead of the LOB value.

The following PL/SQL code selects the LOB locator for b_lob and place it a PL/SQL local variable named image1:

DECLARE
       image1  BLOB;
       image_no  INTEGER := 101;
BEGIN
       SELECT b_lob  INTO image1 FROM lob_table
                  WHERE key_value = image_no;
             ...
END;

When you use an API function to manipulate the LOB value, you refer to the LOB using the locator. The PL/SQL DBMS_LOB package contains useful routines to manipulate LOBs, such as PUT_LINE and GETLENGTH:

BEGIN
     DBMS_OUTPUT.PUT_LINE('Size of the Image is: ', 
                       DBMS_LOB.GETLENGTH(image1));
END;

In the OCI, LOB locators are mapped to LOBLocatorPointers (OCILobLocator *).

The OCI LOB interface and the PL/SQL DBMS_LOB package are described briefly in this chapter. The OCI is described in more detail in the Oracle Call Interface Programmer's Guide. The DBMS_LOB API is described in the Oracle9i Application Developer's Guide - Large Objects (LOBs).

For a BFILE, the LOB column has its own distinct locator, which refers to the LOB's value that is stored in an external file in the server's file system. This implies that two rows in a table with a BFILE column may refer to the same file or two distinct files. A BFILE locator variable in a PL/SQL or OCI program behaves like any other automatic variable. With respect to file operations, it behaves like a file descriptor available as part of the standard I/O library of most conventional programming languages.

EMPTY_BLOB and EMPTY_CLOB Functions

You can use the special functions EMPTY_BLOB and EMPTY_CLOB in INSERT or UPDATE statements of SQL DML to initialize a NULL or non-NULL internal LOB to empty. These are available as special functions in Oracle SQL DML, and are not part of the DBMS_LOB package.

Before you can start writing data to an internal LOB using OCI or the DBMS_LOB package, the LOB column must be made non-null, that is, it must contain a locator that points to an empty or populated LOB value. You can initialize a BLOB column's value to empty by using the function EMPTY_BLOB in the VALUES clause of an INSERT statement. Similarly, a CLOB or NCLOB column's value can be initialized by using the function EMPTY_CLOB.

Syntax

FUNCTION EMPTY_BLOB() RETURN BLOB;
FUNCTION EMPTY_CLOB() RETURN CLOB;

Parameters

None.

Return Values

EMPTY_BLOB returns an empty locator of type BLOB and EMPTY_CLOB returns an empty locator of type CLOB, which can also be used for NCLOBs.

Pragma

None.

Exceptions

An exception is raised if you use these functions anywhere but in the VALUES clause of a SQL INSERT statement or as the source of the SET clause in a SQL UPDATE statement.

Examples

The following example shows EMPTY_BLOB used with SQL DML:

INSERT INTO lob_table VALUES (1001, EMPTY_BLOB(), 'abcde', NULL);
UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value = 1001;
INSERT INTO lob_table VALUES (1002, NULL, NULL, NULL);

The following example shows the correct and erroneous usage of EMPTY_BLOB and EMPTY_CLOB in PL/SQL programs:

DECLARE 
  loba         BLOB; 
  lobb         CLOB; 
  read_offset  INTEGER; 
  read_amount  INTEGER; 
  rawbuf       RAW(20); 
  charbuf      VARCHAR2(20);
BEGIN
  loba := EMPTY_BLOB(); 
  read_amount := 10; read_offset := 1;
  -- the following read will fail 
  dbms_lob.read(loba, read_amount, read_offset, rawbuf); 
   
  -- the following read will succeed;
  UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value = 
         1002 RETURNING c_lob INTO lobb;
dbms_lob.read(lobb, read_amount, read_offset, charbuf); 
  dbms_output.put_line('lobb value: ' || charbuf);

Using the OCI to Manipulate LOBs

The OCI includes functions that you can use to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These functions are mentioned briefly in Table 6-1. For detailed documentation, including parameters, parameter types, return values, and example code, see the Oracle Call Interface Programmer's Guide.

Table 6-1 OCI Functions for Manipulating LOBs  

OCILobAppend() 

OCILobAssign() 

OCILobCharSetForm() 

OCILobCharSetId() 

OCILobCopy() 

OCILobDisableBuffering() 

OCILobEnableBuffering() 

OCILobErase() 

OCILobFileClose() 

OCILobFileCloseAll() 

OCILobFileExists() 

OCILobFileGetName() 

OCILobFileIsOpen() 

OCILobFileOpen() 

OCILobFileSetName() 

OCILobFlushBuffer() 

OCILobGetLength() 

OCILobIsEqual() 

OCILobLoadFromFile() 

OCILobLocatorIsInit() 

OCILobLocatorSize() 

OCILobRead() 

OCILobTrim() 

OCILobWrite() 

Table 6-2 compares the OCI and PL/SQL (DBMS_LOB package) interfaces in terms of LOB access.

Table 6-2 OCI and PL/SQL (DBMS_LOB) Interfaces Compared  

N/A 

DBMS_LOB.COMPARE() 

N/A 

DBMS_LOB.INSTR() 

N/A 

DBMS_LOB.SUBSTR() 

OCILobAppend 

DBMS_LOB.APPEND() 

OCILobAssign 

OCILobCharSetForm 

OCILobCharSetId 

OCILobCopy 

DBMS_LOB.COPY() 

OCILobDisableBuffering 

OCILobEnableBuffering 

OCILobErase 

DBMS_LOB.ERASE() 

OCILobFileClose 

DBMS_LOB.FILECLOSE() 

OCILobFileCloseAll 

DBMS_LOB.FILECLOSEALL() 

OCILobFileExists 

DBMS_LOB.FILEEXISTS() 

OCILobFileGetName 

DBMS_LOB.FILEGETNAME() 

OCILobFileIsOpen 

DBMS_LOB.FILEISOPEN() 

OCILobFileOpen 

DBMS_LOB.FILEOPEN() 

OCILobFileSetName 

OCILobFlushBuffer 

OCILobGetLength 

DBMS_LOB.GETLENGTH() 

OCILobIsEqual 

OCILobLoadFromFile 

DBMS_LOB.LOADFROMFILE() 

OCILobLocatorIsInit 

OCILobRead 

DBMS_LOB.READ() 

OCILobTrim 

DBMS_LOB.TRIM() 

OCILobWrite 

DBMS_LOB.WRITE() 

The following example shows a LOB being selected from the database into a locator. This example assumes that the type lob_type has two attributes (id of type INTEGER and data of type CLOB) and that a table (lob_table) of this type (lob_type) has been created.

/*---------------------------------------------------------------------*/ 
/* Select lob locators from a CLOB column */ 
/* We need the 'FOR UPDATE' clause because we need to write to the LOBs. */ 
/*---------------------------------------------------------------------*/ 
static OCIEnv        *envhp; 
static OCIServer     *srvhp; 
static OCISvcCtx     *svchp; 
static OCIError      *errhp; 
static OCISession    *authp; 
static OCIStmt       *stmthp; 
static OCIDefine     *defnp1; 
static OCIBind       *bndhp;
 
sb4 select_locator(int rowind) 
{ 
  sword retval; 
  boolean flag; 
  int colc = rowind; 
  OCILobLocator *clob;
  text  *sqlstmt = (text *)"SELECT DATA FROM LOB_TABLE WHERE ID = :1 FOR 
UPDATE"; 
 
  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt), 
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) 
  { 
    (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n"); 
    return OCI_ERROR; 
  } 
 
  if (OCIStmtBindByPos(stmthp, bndhp, errhp, (ub4) 1, 
                      (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT, 
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0, 
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) 
  { 
    (void) printf("FAILED: OCIStmtBindByPos()\n"); 
    return OCI_ERROR; 
  } 
 
 
  if (OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, 
               (dvoid *) &clob, (sb4) -1, (ub2) SQLT_CLOB, 
                  (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)) 
  { 
    (void) printf("FAILED: OCIDefineByPos()\n"); 
    return OCI_ERROR; 
  } 
   
  /* Execute the select and fetch one row */ 
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,   
                    (ub4) OCI_DEFAULT)) 
  { 
    (void) printf("FAILED: OCIStmtExecute() sqlstmt\n"); 
    report_error(); 
    return OCI_ERROR; 
  } 
 
  /* Now test to see if the LOB locator is initialized */ 
  retval = OCILobLocatorIsInit(envhp, errhp, clob, &flag); 
  if ((retval != OCI_SUCCESS) && (retval != OCI_SUCCESS_WITH_INFO)) 
  { 
    (void) printf("Select_Locator --ERROR: OCILobLocatorIsInit(), retval = 
%d\n", retval); 
    report_error(); 
    checkerr(errhp, retval); 
    return OCI_ERROR; 
  } 
  
  if (!flag) 
  { 
   (void) printf("Select_Locator --ERROR: LOB Locator is not initialized.\n"); 
    return OCI_ERROR;  
  } 
   
 
  return OCI_SUCCESS; 
}

A sample program (populate.c) that uses the OCI to populate a CLOB with the contents of a file is included on the disk.

Using DBMS_LOB to Manipulate LOBs

The DBMS_LOB package can be used to manipulate LOBs from PL/SQL.

The routines that can modify BLOB, CLOB, and NCLOB values are:

• APPEND() -- append the contents of the source LOB to the destination LOB
• COPY() -- copy all or part of the source LOB to the destination LOB
• ERASE() -- erase all or part of a LOB
• LOADFROMFILE() -- load BFILE data into an internal LOB
• TRIM() -- trim the LOB value to the specified shorter length
• WRITE() -- write data to the LOB from a specified offset

The routines that read or examine LOB values are:

• GETLENGTH() -- get the length of the LOB value
• INSTR() -- return the matching position of the nth occurrence of the pattern in the LOB
• READ() -- read data from the LOB starting at the specified offset
• SUBSTR() -- return part of the LOB value starting at the specified offset

The read-only routines specific to BFILEs are:

• FILECLOSE() -- close the file
• FILECLOSEALL() -- close all previously opened files
• FILEEXISTS() -- test to see if the file exists on the server
• FILEGETNAME() -- get the directory alias and file name
• FILEISOPEN() -- test to see if the file was opened using the input BFILE locators
• FILEOPEN() -- open a file

The following example calls the TRIM procedure to trim a CLOB value to a smaller length is shown in the following example. This example assumes that the type lob_type has two attributes (id of type INTEGER and data of type CLOB) and that a table (lob_table) of this type (lob_type) has been created.

PROCEDURE Trim_Clob IS
        clob_loc  CLOB;
BEGIN
 -- get the LOB Locator
       SELECT data into clob_loc  FROM lob_table
       WHERE id  =  179 FOR UPDATE;
   -- call the TRIM Routine
       DBMS_LOB.TRIM(clob_loc, 834004);
       COMMIT;
END;

Because this example deals with CLOB data, the second argument (834004) to DBMS_LOB.TRIM specifies the number of characters. If the example dealt with BLOB data, this argument would be interpreted as the number of bytes.

LOBs in External Procedures

LOB locators can be passed as arguments to an external procedure. The corresponding C routine gets an argument of type OCILobLocator *. For example, a PL/SQL external procedure could be defined as:

FUNCTION DS_Findmin(data CLOB) RETURN PLS_INTEGER IS EXTERNAL 
                   NAME "c_findmin" LIBRARY DS_Lib LANGUAGE C;

When this function is called, it invokes a routine (c_findmin) with the signature:

int   c_findmin (OCILobLocator *)

This routine in a shared library associated with DS_Lib. In order to use the pointer OCILobLocator * to get data from the LOB (for example, using OCILobRead()), you must reconnect to the database by performing a callback. External procedures and callbacks are discussed in "Doing Callbacks".

LOBs and Triggers

You cannot write to a LOB (:old or :new value) in any kind of trigger.

In regular triggers, you can read the :old value but you cannot read the :new value. In INSTEAD OF triggers, you can read the :old and the :new values.

You cannot specify LOB type columns in an OF clause, because BFILE types can be updated without updating the underlying table on which the trigger is defined.

Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns will not fire triggers defined on the table containing the columns or the attributes.

Using Open/Close as Bracketing Operations for Efficient Performance

The Open/Close functions let you indicate the beginning and end of a series of LOB operations so that large-scale operations, such updating indexes, can be performed once the Close function is called. This means that once the Open call is made, the index would not be updated each time the LOB is modified, and that such updating would not resume until the Close call.

You do not have to wrap all LOB operations inside the Open/Close operations, but this function can be very useful for cartridge developers.

For one thing, if the you do not wrap LOB operations inside an Open/Close call, then each modification to the LOB will implicitly open and close the LOB, thereby firing any triggers. But if do you wrap the LOB operations inside a pair of Open/Close operations, then the triggers will not be fired for each LOB modification. Instead, one trigger will be fired at the time the Close call is made. LIkewise, extensible indexes will not be updated until the user calls Close. This means that any extensible indexes on the LOB are not valid between the Open/Close calls.

You need to apply this technology carefully since state, reflecting the changes to the LOB, is not saved between the Open and the Close operations. Once you have called Open, Oracle no longer keeps track of what portions of the LOB value were modified, nor of the old and new values of the LOB that result from any modifications. The LOB value is still updated directly for each OCILob* or DBMS_LOB operation, and the usual read consistency mechanism is still in place. Moreover, you may want extensible indexes on the LOB to be updated as LOB modifications are made because in that case, the extensible LOB indexes are always valid and may be used at any time.

The API enables you to find out if the LOB is "open" or not. In all cases openness is associated with the LOB, not the locator. The locator does not save any information as to whether the LOB to which it refers is open.

Errors and Restrictions Regarding Open/Close Operations

Note that it is an error to commit the transaction before closing all previously opened LOBs. At transaction rollback time, all LOBs that are still open will be discarded, which means that they will not be closed thereby firing the triggers).

Only 32 LOBs may be open at any one time. An error will be returned when the 33rd LOB is opened. Assigning an already opened locator to another locator does not incur a round trip to the server and does not count as opening a new LOB (both locators refer to the same LOB).

It is an error to Open/Close the same LOB twice either with different locators or with the same locator. It is an error to close a LOB that has not been opened.

Example

Assume loc1 is refers to an opened LOB and is assigned to loc2. If loc2 is subsequently used to modify the LOB value, the modification is grouped together with loc1's modifications (that is, there is only one entry in the LOB manager's state, not one for each locator). Once the LOB is closed (through loc1 or loc2), the triggers are fired and all updates made to the LOB through any locator are committed. After the close of the LOB, if the user tries to use either locator to modify the LOB, the operation will be performed as Open/operation/Close. Note that consistent read is still maintained for each locator. This discussion is merely showing that the LOB, not the locator, is opened and closed. No matter how many copies of the locator are made, the triggers for the LOB are fired only once on the first Close call.

For example:

open (loc1);
loc2 := loc1;
write (loc1);
write (loc2); 
open (loc2);  /* error because the LOB is already open */
close (loc1); /* triggers are fired and all LOB updates made prior to this 
            statement by any locator are incorporated in the extensible
            index */
write (loc2); /* implicit open, write, implicit close */