LONG to LOB Migration Using the LONG-to-LOB API

Figure 10-19 Use Case Diagram: Migrating LONGs to LOBs Using the (new) LONG-to-LOB API

Text description of the illustration adl10p38.gif

See: "Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations.

Purpose

This procedure describes how to migrate LONGs to LOBs using the (new) LONG-to-LOB API.

Usage Notes

See Also: Chapter 8, "Migrating From LONGs to LOBs" for further details on using the LONG-to-LOB API.

Syntax

Use the following syntax reference:

• SQL: Oracle9i SQL Reference , Chapter 4, "Functions"

Scenario

The fields used in the following example are:

CREATE TABLE Multimedia_tab ( 
   Clip_ID         NUMBER NOT NULL, 
   Story           CLOB default EMPTY_CLOB(), 
   FLSub           NCLOB default EMPTY_CLOB(), 
   Photo           BFILE default NULL, 
   Frame           BLOB default EMPTY_BLOB(), 
   Sound           BLOB default EMPTY_BLOB(), 
   Voiced_ref      REF Voiced_typ,
   InSeg_ntab      InSeg_tab, 
   Music           BFILE default NULL, 
   Map_obj         Map_typ 
 ) NESTED TABLE    InSeg_ntab STORE AS InSeg_nestedtab; 

Suppose the column, STORY,of table MULTIMEDIA_TAB was of type LONG before, that is, you originally created the table MULTIMEDIA_TAB as follows:

CREATE TABLE MULTIMEDIA_TAB (CLIP_ID NUMBER,
    STORY  LONG, 
    .... );

To Convert LONG to CLOB, Use ALTER TABLE

To convert the LONG column to CLOB just use ALTER TABLE as follows:

ALTER TABLE multimedia_tab MODIFY ( story CLOB );

and you are done!

Any existing application using table MULTIMEDIA_TAB can continue to work with minor modification even after the column STORY has been modified to type CLOB. Chapter 8, "Migrating From LONGs to LOBs" provides examples of operations (binds and defines) used by LONGs and that continue to work for LOBs with minor modifications.

Examples

The following example illustrates how to use the LONG-to-LOB API with OCI:

• C (OCI): LONG to LOB Migration Using the LONG-to-LOB API: More than 4K Binds and Simple INSERTs

C (OCI): LONG to LOB Migration Using the LONG-to-LOB API: More than 4K Binds and Simple INSERTs

The following example illustrates converting from LONG to LOBs when using a >4K bind and simple INSERT:

word buflen, buf1 = 0;
text buf2[5000];
text *insstmt = (text *)
"INSERT INTO MULTIMEDIA_TAB(CLIP_ID, STORY) VALUES 
(:CLIP_ID, :STORY)";

if (OCIStmtPrepare(stmthp, errhp, insstmt, 
(ub4)strlen((char *)insstmt), (ub4) OCI_NTV_SYNTAX, 
(ub4) OCI_DEFAULT))
{
    DISCARD printf("FAILED: OCIStmtPrepare()\n");
    report_error(errhp);
    return;
}

if (OCIBindByName(stmthp, &bndhp[0], errhp,
  (text *) ":CLIP_ID", (sb4) strlen((char *) ":CLIP_ID"),
  (dvoid *) &buf1, (sb4) sizeof(buf1), SQLT_INT,
  (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  ||  OCIBindByName(stmthp, &bndhp[1], errhp,
  (text *) ":STORY", (sb4) strlen((char *) ":STORY"),
  (dvoid *) buf2, (sb4) sizeof(buf2), SQLT_CHR,
  (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
{
    DISCARD printf("FAILED: OCIBindByName()\n");
    report_error(errhp);
    return;
}

buf1 = 101;
memset((void *)buf2, (int)'A', (size_t)5000);

if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
           (const OCISnapshot*) 0, (OCISnapshot*) 0,
           (ub4) OCI_DEFAULT))
{
      DISCARD printf("FAILED: OCIStmtExecute()\n");
      report_error(errhp);
      return;
}