6 Working with Multimedia Datatypes

Overview of Cartridges and Multimedia Datatypes

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.

  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 are stored in operating system files outside the database tablespaces as BFILEs, binary data. They cannot participate in transactions.

Together, internal LOBs and in BFILEs 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.

DDL for LOBs

LOB definition can involve the CREATE TYPE and the CREATE TABLE statements. Example 6-1 specifies a CLOB within a datatype named lob_type.

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

Example 6-2 creates an object table, lob_table, in which each row is an instance of lob_type data:

CREATE TABLE lob_table OF lob_type;

Example 6-3 shows how to store LOBs in a regular table, as opposed to an object table as in Example 6-2.

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.

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.

Example 6-4 selects the LOB locator for b_lob and places 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, as in Example 6-5.

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

In the OCI, LOB locators are mapped to LOBLocatorPointers, such as OCILobLocator *.

The OCI LOB interface and the PL/SQL DBMS_LOB package are described briefly in this chapter.

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. The syntax of the functions is demonstrated in .

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

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. The functions don't have an associated pragma.

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.

Example 6-7 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);

Example 6-8 shows how to use EMPTY_CLOB() in PL/SQL programs.

DECLARE 
  lobb         CLOB; 
  read_offset  INTEGER; 
  read_amount  INTEGER; 
  rawbuf       RAW(20); 
  charbuf      VARCHAR2(20);
BEGIN
  read_amount := 10; read_offset := 1;
  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);
END

Using the OCI to Manipulate LOBs

The OCI includes functions that enable access to data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These functions are introduced in Table 6-1.

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.

DBMS_LOB.COMPARE() 

DBMS_LOB.INSTR() 

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() 

Example 6-9 shows how to select a LOB from the database into a locator. It 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 type lob_type already exists.

/*-----------------------------------------------------------------------*/ 
/* 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, 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. Table 6-3 introduces its routines.

APPEND()

COPY()

ERASE()

LOADFROMFILE()

TRIM()

WRITE()

GETLENGTH

INSTR()

READ()

SUBSTR()

FILECLOSE()

FILECLOSEALL()

FILEEXISTS()

FILEGETNAME()

FILEISOPEN()

FILEOPEN()

Example 6-10 calls the TRIM procedure to trim a CLOB value to a smaller length. It 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 type lob_type already exists. Because this example deals with CLOB data, the second argument to DBMS_LOB.TRIM, the literal 834004, specifies the number of characters. If the example dealt with BLOB data, this argument would be interpreted as a number of bytes.

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;

LOBs in External Procedures

LOB locators can be passed as arguments to an external procedure. The corresponding C function gets an argument of type OCILobLocator *. When the function defined in Table 6-3 is called, it invokes a c routine, c_findmin(), with the following signature .

int c_findmin(OCILobLocator *);

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

The routine c_findmin is in a shared library associated with DS_Lib. In order to use the pointer OCILobLocator * to get data from the LOB, you must reconnect to the database by performing a callback.

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 both 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 code block can be very valueable for the following reasons:

• If 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 all triggers. If you do 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 when the Close call is made. Likewise, extensible indexes will not be updated until the Close call. This means that any extensible indexes on the LOB are not valid between the Open/Close calls.

• You need to apply this technology carefully because state, which reflects 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 the extensible LOB indexes are always valid and may be used at any time.

• The API enables you to determine if the LOB is open. In all cases, openness is associated with the LOB, not the locator. The locator does not save any state information.

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 */