Working with Multimedia Datatypes, 6 of 10

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

Function	Description
OCILobAppend()	Appends `LOB` value to another `LOB`.
OCILobAssign()	Assigns one `LOB` locator to another.
OCILobCharSetForm()	Returns the character set form of a `LOB`.
OCILobCharSetId()	Returns the character set ID of a `LOB`.
OCILobCopy()	Copies a portion of a `LOB` into another `LOB`.
OCILobDisableBuffering()	Disables the buffering subsystem use.
OCILobEnableBuffering()	Uses the `LOB` buffering subsystem for subsequent read and write operations of `LOB` data.
OCILobErase()	Erases part of a `LOB`, starting at a specified offset.
OCILobFileClose()	Closes an open `BFILE`.
OCILobFileCloseAll()	Closes all open `BFILEs`.
OCILobFileExists()	Tests to see if a `BFILE` exists.
OCILobFileGetName()	Returns the name of a `BFILE`.
OCILobFileIsOpen()	Tests to see if a `BFILE` is open.
OCILobFileOpen()	Opens a `BFILE`.
OCILobFileSetName()	Sets the name of a `BFILE` in a locator.
OCILobFlushBuffer()	Flushes changes made to the `LOB` buffering subsystem to the database (server)
OCILobGetLength()	Returns the length of a `LOB` or a `BFILE`.
OCILobIsEqual()	Tests to see if two `LOB` locators refer to the same `LOB`.
OCILobLoadFromFile()	Loads `BFILE` data into an internal `LOB`.
OCILobLocatorIsInit()	Tests to see if a `LOB` locator is initialized.
OCILobLocatorSize()	Returns the size of a `LOB` locator.
OCILobRead()	Reads a specified portion of a non-null `LOB` or a `BFILE` into a buffer.
OCILobTrim()	Truncates a `LOB`.
OCILobWrite()	Writes data from a buffer into a `LOB`, writing over existing data.

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

OCI (ociap.h)	PL/SQL DBMS_LOB (dbmslob.sql)
N/A	DBMS_LOB.COMPARE()
N/A	DBMS_LOB.INSTR()
N/A	DBMS_LOB.SUBSTR()
OCILobAppend	DBMS_LOB.APPEND()
OCILobAssign	N/A [use PL/SQL assign operator]
OCILobCharSetForm	N/A
OCILobCharSetId	N/A
OCILobCopy	DBMS_LOB.COPY()
OCILobDisableBuffering	N/A
OCILobEnableBuffering	N/A
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	N/A (use `BFILENAME` operator)
OCILobFlushBuffer	N/A
OCILobGetLength	DBMS_LOB.GETLENGTH()
OCILobIsEqual	N/A [use PL/SQL equal operator]
OCILobLoadFromFile	DBMS_LOB.LOADFROMFILE()
OCILobLocatorIsInit	N/A [always initialize]
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.