Using C (OCI) to Work With LOBs

Oracle Call Interface (OCI) can be used to make changes to an entire internal LOB, or to pieces of the beginning, middle or end of it through OCI, as follows:

• For reading from internal and external LOBs (BFILEs)

• For writing to internal LOBs

OCI also includes functions that you can use to:

• Access data stored in internal LOBs (BLOBs, CLOBs, NCLOBs) and external LOBs (BFILEs)

• Modify internal LOBs (BLOBs, CLOBs, NCLOBs)

These functions are listed in the tables below, and are discussed in greater detail later in this section.

Set CSID Parameter to OCI_UCS2ID to Read/Write in UCS2

If you want to read or write data in 2 byte unicode (UCS2) format, set the 'csid' (character set ID) parameter in OCILobRead and OCILobWrite to OCI_UCS2ID. The 'csid' parameter indicates the character set id for the buffer parameter. You can set the 'csid' parameter to any character set id. If the csid parameter is set, it will override the NLS_LANG environment variable.

See Also: Oracle Call Interface Programmer's Guide for detailed documentation, including parameters, parameter types, return values, and example code. Oracle8i National Language Support Guide for detailed information about implementing applications in different languages.

Offset and Amount Parameters: Fixed-Width Versus Varying-Width, Character or Byte With OCI

Fixed Width Character Set Rules

In OCI, for fixed-width client-side character sets, the following rules apply:

• CLOBs and NCLOBs: offset and amount parameters are always in characters

• BLOBs and BFILES: offset and amount parameters are always in bytes

Varying-Width Character Set Rules

The following rules apply only to varying-width client-side character sets:

• Offset parameter: Regardless of whether the client-side character set is varying-width, the offset parameter is always as follows:
  • CLOBs and NCLOBs: in characters

  • BLOBs and BFILEs: in bytes

• Amount parameter: The amount parameter is always as follows:
  • When refering to a server-side LOB: in characters

  • When refering to a client-side buffer: in bytes

• OCILobFileGetLength: Regardless of whether the client-side character set is varying-width, the output length is as follows:
  • CLOBs and NCLOBs: in characters

  • BLOBs and BFILEs: in bytes

• OCILobRead: With client-side character set of varying-width, CLOBs and NCLOBs:
  • Input amount is in characters. Input amount refers to the number of characters to read from the server-side CLOB or NCLOB.

  • Output amount is in bytes. Output amount indicates how many bytes were read into the buffer 'bufp'.

• OCILobWrite: With client-side character set of varying-width, CLOBs and NCLOBs :
  • Input amount is in bytes. Input amount refers to the number of bytes of data in the input buffer 'bufp'.

  • Output amount is in characters. Output amount refers to the number of characters written into the server-side CLOB or NCLOB.

Other Operations

For all other LOB operations, irrespective of the client-side character set, the amount parameter is in characters for CLOBs and NCLOBs. These include OCILobCopy, OCILobErase, OCILobLoadFromFile, and OCILobTrim. All these operations refer to the amount of LOB data on the server.

See also: Oracle8i National Language Support Guide

NCLOBs

• NCLOBs parameters are allowed in methods

• NCLOBs parameters are not allowed as attributes in object types

OCILobLoadFromFile: Specify Amount Parameter to be Less than Length of BFILE

When using OCILobLoadFromFile you cannot specify amount larger than the length of the BFILE.

OCILobRead: Specify Amount Parameter to be 4 gigabytes - 1

In OCILobRead, you can specify amount = 4 gigabytes-1, and it will read to the end of the LOB.

OCI LOB Examples

Further OCI examples are provided in:

• Chapter 9, "Internal Persistent LOBs"

• Chapter 10, "Temporary LOBs"

• Chapter 11, "External LOBs (BFILEs)".

You will be able to access all these example scripts from your Oracle8i software CD in a future release. You can refer to the following OCI scripts available on the Oracle8i software CD:

/ORACLE_HOME/rdbms/demo/demolb.c

/ORACLE_HOME/rdbms/demo/demolb2.c

/ORACLE_HOME/rdbms/demo/demolbs.c

or on NT:

\ORACLE_HOME\Oci\Samples\demolb.c, etc.

See also Appendix B, "OCI Demonstration Programs" in Oracle Call Interface Programmer's Guide, for further OCI demonstration script listings.

Further Information About OCI:

For further information and features of OCI, refer to the following web sites:

• http://www.oracle.com/products and select Application Server. Enter OCI on the search line to bring up articles on OCI.

• http://technet.oracle.com/products/programmer for OCI features

OCI Functions that Operate on BLObs, BLOBs, NCLOBs, and BFILEs

OCI functions that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are as follows:

• To modify internal LOBs, see Table 3-8

• To read or examine LOB values, see Table 3-9

• To create or free temporary LOB, or check if Temporary LOB exists, see Table 3-10

• For read only functions on external LOBs (BFILEs), see Table 3-11

• To operate on LOB locators, see Table 3-12

• For LOB buffering, see Table 3-13

• To open and close LOBs, see Table 3-14

OCI Functions To Modify Internal LOB (BLOB, CLOB, and NCLOB) Values

Table 3-8 OCI Functions To Modify Internal LOB (BLOB, CLOB, and NCLOB) Values

Function/Procedure	Description
OCILobAppend()	Appends LOB value to another LOB.
OCILobCopy()	Copies all or part of a LOB to another LOB.
OCILobErase()	Erases part of a LOB, starting at a specified offset.
OCILobLoadFromFile()	Loads BFILE data into an internal LOB.
OCILobTrim()	Truncates a LOB.
OCILobWrite()	Writes data from a buffer into a LOB, overwriting existing data.
OCILobWriteAppend()	Writes data from a buffer to the end of the LOB.

OCI Functions To Read or Examine Internal LOB and External LOB (BFILE) Values

Table 3-9 OCI Functions To Read or Examine internal LOB and external LOB (BFILE) Values

Function/Procedure	Description
OCILobGetChunkSize()	Gets the Chunk size used when reading and writing. This works on internal LOBs and does not apply to external LOBs (BFILEs).
OCILobGetLength()	Returns the length of a LOB or a BFILE.
OCILobRead()	Reads a specified portion of a non-null LOB or a BFILE into a buffer.

OCI Functions For Temporary LOBs

Table 3-10  OCI Functions forTemporary LOBs

Function/Procedure	Description
OCILobCreateTemporary()	Creates a temporary LOB
OCILobIsTemporary()	Sees if a temporary LOB exists
OCILobFreeTemporary()	Frees a temporary LOB

OCI Read-Only Functions For BFILEs

Table 3-11  OCI Read-Only Functions Fo BFILES

Function/Procedure	Description
OCILobFileClose()	Closes an open BFILE.
OCILobFileCloseAll()	Closes all open BFILEs.
OCILobFileExists()	Checks whether a BFILE exists.
OCILobFileGetName()	Returns the name of a BFILE.
OCILobFileIsOpen()	Checks whether a BFILE is open.
OCILobFileOpen()	Opens a BFILE.

OCI LOB Locator Functions

Table 3-12  OCI LOB-Locator Functions

Function/Procedure	Description
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.
OCILobFileSetName()	Sets the name of a BFILE in a locator.
OCILobIsEqual()	Checks whether two LOB locators refer to the same LOB.
OCILobLocatorIsInit()	Checks whether a LOB locator is initialized.

OCI LOB-Buffering Functions

Table 3-13  OCI LOB-Buffering Functions

Function/Procedure	Description
OCILobDisableBuffering()	Disables the buffering subsystem use.
OCILobEnableBuffering()	Uses the LOB buffering subsystem for subsequent reads and writes of LOB data.
OCILobFlushBuffer()	Flushes changes made to the LOB buffering subsystem to the database (server)

OCI Functions To Open and Close Internal and External LOBs

Table 3-14  OCI Functions To Open and Close Internal and External LOBs

Function/Procedure	Description
OCILobOpen()	Opens a LOB
OCILobIsOpen()	Sees if a LOB is open
OCILobClose()	Closes a LOB

Example Procedure: main() and seeIfLOBOpen

To work with the OCI examples in the remainder of the book, you can use a main() like the following. Here, its use with seeIfLOBIsOpen is shown as an example.

int main(char *argv, int argc)
{
  /* Declare OCI Handles to be used */
  OCIEnv        *envhp;
  OCIServer     *srvhp; 
  OCISvcCtx     *svchp;
  OCIError      *errhp;
  OCISession    *authp;
  OCIStmt       *stmthp;
  OCILobLocator *Lob_loc;

  /* Create and Initialize an OCI Environment: */
  (void) OCIEnvCreate(&envhp, (ub4)OCI_DEFAULT, (dvoid *)0,
                      (dvoid * (*)(dvoid *, size_t)) 0,
                      (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                      (void (*)(dvoid *, dvoid *))0,
                      (size_t) 0, (dvoid **) 0);

  /* Allocate error handle: */
  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR, 
                        (size_t) 0, (dvoid **) 0);

  /* Allocate server contexts: */
  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
                        (size_t) 0, (dvoid **) 0);

  /* Allocate service context: */
  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                        (size_t) 0, (dvoid **) 0);

  /* Attach to the Oracle database:  */
  (void) OCIServerAttach(srvhp, errhp, (text *)"", strlen(""), 0);

  /* Set the server context attribute in the service context: */
  (void) OCIAttrSet ((dvoid *) svchp, OCI_HTYPE_SVCCTX, 
                     (dvoid *)srvhp, (ub4) 0,
                     OCI_ATTR_SERVER, (OCIError *) errhp);

  /* Allocate the session handle: */
  (void) OCIHandleAlloc((dvoid *) envhp, 
                        (dvoid **)&authp, (ub4) OCI_HTYPE_SESSION,
                        (size_t) 0, (dvoid **) 0);
 
  /* Set the username in the session handle:*/
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                    (dvoid *) "samp", (ub4)4,
                    (ub4) OCI_ATTR_USERNAME, errhp);
  /* Set the password in the session handle: */
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                    (dvoid *) "samp", (ub4) 4,
                    (ub4) OCI_ATTR_PASSWORD, errhp);

  /* Authenticate and begin the session: */
  checkerr(errhp, OCISessionBegin (svchp,  errhp, authp, OCI_CRED_RDBMS, 
                                   (ub4) OCI_DEFAULT));

  /* Set the session attribute in the service context: */
  (void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                    (dvoid *) authp, (ub4) 0,
                   (ub4) OCI_ATTR_SESSION, errhp);

  /* ------- At this point a valid session has been created -----------*/
  printf ("user session created \n");

  /* Allocate a statement handle: */
  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /* =============  Sample procedure call begins here ===================*/  

  printf ("calling seeIfLOBIsOpen...\n");
  seeIfLOBIsOpen(envhp, errhp, svchp, stmthp);

  return 0;
}

void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;

  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
  case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
}

/* Select the locator into a locator variable */

sb4 select_frame_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text      *sqlstmt = 
                 (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=1";
  OCIDefine *defnp1;

  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                  (ub4)strlen((char *)sqlstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
  
  checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                                  (dvoid *)&Lob_loc, (sb4)0, 
                                  (ub2) SQLT_BLOB,(dvoid *) 0, 
                                  (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT));

  /* execute the select and fetch one row */
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                 (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                 (ub4) OCI_DEFAULT));
  
  return (0);
}

void seeIfLOBIsOpen(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *Lob_loc;
  int isOpen;

  /* allocate locator resources */
  (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&Lob_loc,
                            (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0);

  /* Select the locator */
  (void)select_frame_locator(Lob_loc, errhp, svchp, stmthp);
  
  /* See if the LOB is Open */
  checkerr (errhp, OCILobIsOpen(svchp, errhp, Lob_loc, &isOpen));
 
  if (isOpen)
  {
    printf("  Lob is Open\n");
    /* ... Processing given that the LOB has already been Opened */
  }
  else
  {
    printf("  Lob is not Open\n");
    /* ... Processing given that the LOB has not been Opened */
  }

  /* Free resources held by the locators*/
  (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB);
    return;
}