Examples of Converting from LONG to LOB Using Table Multimedia_tab

See Appendix B, "The Multimedia Schema Used for Examples in This Manual", for a detailed description of the Multimedia_tab schema. The fields used in the following examples 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.

Here are examples of all operations (binds and defines) used by LONGs and that will continue to work for LOBs with minor modifications as described in "Applications Requiring Changes When Converting From LONGs to LOBs" .

Converting LONG to LOB Example 1: 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;
}

Converting LONG to LOB Example 2: Piecewise INSERT with Polling

Continuing the above example...

text *sqlstmt  = (text *)"INSERT INTO MULTIMEDIA_TAB VALUES (:1, :2)";
ub2 rcode;
ub1 piece, i;

OCIStmtPrepare(stmthp, errhp, sqlstmt,
                  (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX,
                  (ub4) OCI_DEFAULT);

OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 1,
                  (dvoid *) &buf1, (sb4) sizeof(buf1), SQLT_INT,
                  (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);

OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 2,
                  (dvoid *) 0, (sb4) 15000, SQLT_LNG,
                  (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                  (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC);
buf1 = 101;
i = 0;
while (1)
{
    i++;
    retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                  (ub4) OCI_DEFAULT);

switch(retval)
    {
          case OCI_NEED_DATA:

        memset((void *)buf2, (int)'A'+i, (size_t)5000);
        buflen = 5000;
        if (i == 1) piece = OCI_ONE_PIECE
            else if (i == 3) piece = OCI_LAST_PIECE
            else piece = OCI_NEXT_PIECE;
    
        if (OCIStmtSetPieceInfo((dvoid *)bndhp[1],
                  (ub4)OCI_HTYPE_BIND, errhp, (dvoid *)buf2,
                  &buflen, piece, (dvoid *) 0, &rcode))
        {
          DISCARD printf("ERROR: OCIStmtSetPieceInfo: %d \n", retval);
          break;
        }

        retval = OCI_NEED_DATA;
        break;
      case OCI_SUCCESS:
        break;
      default:
        DISCARD printf( "oci exec returned %d \n", retval);
        report_error(errhp);
        retval = 0;
    }    /* end switch */
    if (!retval) break;
} /* end while(1) */

Converting LONG to LOB Example 3: Piecewise INSERT with Callback

The following example illustrates converting from LONG to LOBs when using a piecewise INSERT with callback:

void insert_data()
{
text *sqlstmt = (text *) "INSERT INTO MULTIMEDIA_TAB VALUES (:1, :2)";
OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)
/* bind input */
if (OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 1,
                      (dvoid *) 0, (sb4) sizeof(buf1), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 2,
                      (dvoid *) 0, (sb4) 3 * sizeof(buf2), SQLT_CHR,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC))
{
    DISCARD printf("FAILED: OCIBindByPos()\n");
    report_error(errhp);
    return OCI_ERROR;
}
for (i = 0; i < MAXCOLS; i++)
    pos[i] = i+1;
if (OCIBindDynamic(bndhp[0], errhp, (dvoid *) (dvoid *) &pos[0],
        cbf_in_data,(dvoid *) 0, (OCICallbackOutBind) 0)
  ||  OCIBindDynamic(bndhp[1], errhp, (dvoid *) (dvoid *) &pos[1],
        cbf_in_data, (dvoid *) 0, (OCICallbackOutBind) 0))
{
    DISCARD printf("FAILED: OCIBindDynamic()\n");
    report_error(errhp);
    return OCI_ERROR;
}
OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (const OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT)
} /* end insert_data() */

/* Inbind callback to specify input data.                            */
STATICF sb4 cbf_in_data(ctxp, bindp, iter, index, bufpp, alenpp,
                                        piecep, indpp)
dvoid *ctxp;
OCIBind *bindp;
ub4 iter;
ub4 index;
dvoid **bufpp;
ub4 *alenpp;
ub1 *piecep;
dvoid **indpp;
{
  static int a = 0;
  word j;
  ub4  inpos = *((ub4 *)ctxp);
    switch(inpos)
    {
    case 1:
    buf1 = 175;
    *bufpp = (dvoid *) &buf1;
    *alenpp = sizeof(buf1);
    break;
    case 2:
    memset((void *)buf2, (int) `A'+a, (size_t) 5000);
    *bufpp = (dvoid *) buf2;
    *alenpp = 5000 ;
     a++;
    break;
    default: printf("ERROR: invalid position number: %d\n", pos);
    }
    *indpp = (dvoid *) 0;
      *piecep = OCI_ONE_PIECE;
      if (inpos == 2)
    {
    if (a<=1)
    {
      *piecep = OCI_FIRST_PIECE;
      printf("Insert callback: 1st piece\n");
    }
    else if (a<3)
    {
      *piecep = OCI_NEXT_PIECE;
      printf("Insert callback: %d'th piece\n", a);
    }
    else {
      *piecep = OCI_LAST_PIECE;
      printf("Insert callback: %d'th piece\n", a);
      a = 0;
    }
    }
    return OCI_CONTINUE;
}

Converting LONG to LOB Example 4: Array insert

The following example illustrates converting from LONG to LOBs when using an array INSERT:

word buflen;
word arrbuf1[5];
text arrbuf2[5][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 *) &arrbuf1[0], (sb4) sizeof(arrbuf1[0]), 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 *) arrbuf2[0], (sb4) sizeof(arrbuf2[0]), SQLT_CHR,
  (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
{
    DISCARD printf("FAILED: OCIBindByName()\n");
    report_error(errhp);
    return;
}
OCIBindArrayOfStruct(bndhp[0], ERRH, sizeof(arrbuf1[0]), 
                indsk, rlsk, rcsk); 
OCIBindArrayOfStruct(bndhp[1], ERRH, sizeof(arrbuf2[0]), 
                indsk, rlsk, rcsk);
for (i=0; i<5; i++)
{
    arrbuf1[i] = 101+i;
    memset((void *)arrbuf2[i], (int)'A'+i, (size_t)5000);
}

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

Converting LONG to LOB Example 5: Simple Fetch

The following example illustrates converting from LONG to LOBs when using a simple fetch:

word   i, buf1 = 0;
text   buf2[5000];

text *selstmt = (text *) "SELECT CLIP_ID, STORY FROM MULTIMEDIA_TAB 
                      ORDER BY CLIP_ID";
OCIStmtPrepare(stmthp, errhp, selstmt, (ub4)strlen((char *)selstmt),
                      (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0,
                      (const OCISnapshot*) 0, (OCISnapshot*) 0,
                      (ub4) OCI_DEFAULT);

while (retval == OCI_SUCCESS || retval == OCI_SUCCESS_WITH_INFO)
{
    OCIDefineByPos(stmthp, &defhp1, errhp, (ub4) 1, (dvoid *) &buf1,
                      (sb4) sizeof(buf1), (ub2) SQLT_INT, (dvoid *) 0,
                      (ub2 *) 0,(ub2 *) 0, (ub4) OCI_DEFAULT);
    OCIDefineByPos(stmthp, &defhp2, errhp, (ub4) 2, (dvoid *) buf2,
                      (sb4) sizeof(buf2), (ub2) SQLT_CHR, (dvoid *) 0,
                      (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT);
    retval = OCIStmtFetch(stmthp, errhp, (ub4) 1, 
                      (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
    if (retval == OCI_SUCCESS || retval == OCI_SUCCESS_WITH_INFO)
       DISCARD printf("buf1 = %d,  buf2 = %.*s\n", buf1, 30, buf2);
}

Converting LONG to LOB Example 6: Piecewise Fetch with Polling

The following example illustrates converting from LONG to LOBs when using a 
piecewise fetch with polling:
text *selstmt = (text *) "SELECT STORY FROM MULTIMEDIA_TAB 
                            ORDER BY CLIP_ID";
OCIStmtPrepare(stmthp, errhp, sqlstmt,
                  (ub4) strlen((char *)sqlstmt),
                  (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
OCIDefineByPos(stmthp, &dfnhp[1], errhp, (ub4) 1,
                  (dvoid *) NULL, (sb4) 100000, SQLT_LNG,
                  (dvoid *) 0, (ub2 *) 0,
                  (ub2 *) 0, (ub4) OCI_DYNAMIC_FETCH);
retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0,
                                (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                                    (ub4) OCI_DEFAULT);
retval = OCIStmtFetch(stmthp, errhp, (ub4) 1 ,
                                (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);

while (retval != OCI_NO_DATA && retval != OCI_SUCCESS)
{
    ub1    piece;
    ub4    iter, buflen;
    ub4    idx;
     genclr((void *)buf2, 5000);

    switch(retval)
    {
     case OCI_NEED_DATA:
        OCIStmtGetPieceInfo(stmthp, errhp, &hdlptr, &hdltype,
                              &in_out, &iter, &idx, &piece);
        OCIStmtSetPieceInfo(hdlptr, hdltype, errhp,
                                (dvoid *) buf2, &buflen, piece,
                                            (CONST dvoid *) &indp1, (ub2 *) 0));
        retval = OCI_NEED_DATA;
            break;
    default:
      DISCARD printf("ERROR: piece-wise fetching\n");
        return;
    } /* end switch */

    retval = OCIStmtFetch(stmthp, errhp, (ub4) 1 ,
                                (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
    printf("Data : %s\n"; buf2);

} /* end while */

Converting LONG to LOB Example 7: Piecewise Fetch with Callback

The following example illustrates converting from LONG to LOBs when using a piecewise fetch with callback:

select()
{
text *sqlstmt = (text *) "SELECT CLIP_ID, STORY FROM MULTIMEDIA_TAB";

OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
                        (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

OCIDefineByPos(stmthp, &dfnhp[0], errhp, (ub4) 1,
                    (dvoid *) 0, (sb4) sizeof(buf1), SQLT_INT,
                    (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                    (ub4) OCI_DYNAMIC_FETCH);
OCIDefineByPos(stmthp, &dfnhp[1], errhp, (ub4) 2,
                    (dvoid *) 0, (sb4)3 * sizeof(buf2), SQLT_CHR,
                    (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                    (ub4) OCI_DYNAMIC_FETCH);
OCIDefineDynamic(dfnhp[0], errhp, (dvoid *) &outpos,
                      (OCICallbackDefine) cbf_get_data);
OCIDefineDynamic(dfnhp[1], errhp, (dvoid *) &outpos2,
                      (OCICallbackDefine) cbf_get_data);
OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (const OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT);
buf2[ 4999 ] = `\0';
printf("Select callback: Last piece: %s\n", buf2);
}

/* -------------------------------------------------------------- */
/* Fetch callback to specify buffers.                          */
/* -------------------------------------------------------------- */
STATICF sb4 cbf_get_data(ctxp, dfnhp, iter, bufpp, alenpp, piecep,
                                indpp, rcpp)
dvoid *ctxp;
OCIDefine *dfnhp;
ub4 iter;
dvoid **bufpp;
ub4 **alenpp;
ub1 *piecep;
dvoid **indpp;
ub2 **rcpp;
{
  static int a = 0;
  ub4  outpos = *((ub4 *)ctxp);
  len = sizeof(buf1);
  len2 = 5000;

      switch(outpos)
    {
      case 1:
    *bufpp = (dvoid *) &buf1;
    *alenpp = &len;
    break;
      case 2:
    a ++;
    *bufpp = (dvoid *) buf2;
    *alenpp = &len2;
    break;
      default:
    *bufpp = (dvoid *) 0;
    *alenpp = (ub4 *) 0;
    DISCARD printf("ERROR: invalid position number: %d\n", pos);
  }

  *indpp = (dvoid *) 0;
  *rcpp = (ub2 *) 0;

  if (outpos == 1)
    *piecep = (ub1)OCI_ONE_PIECE;
  if (outpos == 2)
  {
    out2[len2] = `\0';
    if (a<=1)
    {
      *piecep = OCI_FIRST_PIECE;
        printf("Select callback: 0th piece\n");
    }
    else if (a<3)
    {
      *piecep = OCI_NEXT_PIECE;
      printf("Select callback: %d'th piece: %s\n", a-1, out2);
    }
    else {
      *piecep = OCI_LAST_PIECE;
      printf("Select callback: %d'th piece: %s\n", a-1, out2);
      a = 0;
    }
  }

  return OCI_CONTINUE;
}

Converting LONG to LOB Example 8: Array Fetch

The following example illustrates converting from LONG to LOBs when using an array fetch:

word   i;
word arrbuf1[5] = 0;
text   arrbuf2[5][5000];

text *selstmt = (text *) "SELECT CLIP_ID, STORY FROM MULTIMEDIA_TAB 
                    ORDER BY CLIP_ID";
OCIStmtPrepare(stmthp, errhp, selstmt, (ub4)strlen((char *)selstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0,
                    (const OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT);

while (retval == OCI_SUCCESS || retval == OCI_SUCCESS_WITH_INFO)
{
    OCIDefineByPos(stmthp, &defhp1, errhp, (ub4) 1, 
                    (dvoid *) &arrbuf1[0], (sb4) sizeof(arrbuf1[0]), 
                    (ub2) SQLT_INT, (dvoid *) 0,
                    (ub2 *) 0,(ub2 *) 0, (ub4) OCI_DEFAULT);
    OCIDefineByPos(stmthp, &defhp2, errhp, (ub4) 2,
                    (dvoid *) arrbuf2[0], (sb4) sizeof(arrbuf2[0]), 
                    (ub2) SQLT_CHR, (dvoid *) 0,
                    (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT);
    OCIDefineArrayOfStruct(dfnhp[0], ERRH, sizeof(arrbuf1[0]), indsk,
                     rlsk, rcsk);
    OCIDefineArrayOfStruct(dfnhp[1], ERRH, sizeof(arrbuf2[0]), indsk,
                     rlsk, rcsk);

    retval = OCIStmtFetch(stmthp, errhp, (ub4) 5, 
                     (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
    if (retval == OCI_SUCCESS || retval == OCI_SUCCESS_WITH_INFO)
    {
        DISCARD printf("%d, %s\n", arrbuf1[0], arrbuf2[0]);
        DISCARD printf("%d, %s\n", arrbuf1[1], arrbuf2[1]);
        DISCARD printf("%d, %s\n", arrbuf1[2], arrbuf2[2]);
        DISCARD printf("%d, %s\n", arrbuf1[3], arrbuf2[3]);
        DISCARD printf("%d, %s\n", arrbuf1[4], arrbuf2[4]);

    }
}

Converting LONG to LOB Example 9: Using PL/SQL in INSERT, UPDATE and SELECT

INSERT/UPDATE statements on LOBs are used in the same way as on LONGs. For example:

BEGIN
  INSERT INTO Multimedia_tab VALUES (1, `A wonderful story', NULL, EMPTY_BLOB,
      EMPTY_BLOB(), NULL, NULL, NULL, NULL, NULL);
  UPDATE Multimedia_tab SET Story = `A wonderful story';
END;

LONG-to-LOB API enables SELECT statements to bind character variables to LOB columns.

BEGIN
story_buffer VARCHAR2(100);
/* This will get the LOB column if it is upto 100 bytes, otherwise it will 
raise an exception */
SELECT Story INTO story_buffer FROM Multimedia_tab WHERE Clip_ID = 1;
END;

Converting LONG to LOB Example 10: Assignments and Parameter Passing in PL/SQL

The LONG-to-LOB API enables implicit assignments of LOBs to VARCHAR2s, RAWs,..., including parameter passing. For example:

CREATE TABLE t (clob_col CLOB, blob_col BLOB);
INSERT INTO t VALUES('abcdefg', 'aaaaaa');
DECLARE
    var_buf VARCHAR2(100);
    clob_buf CLOB;
    raw_buf RAW(100);
    blob_buf BLOB;
BEGIN
    SELECT * INTO clob_buf, blob_buf FROM t;
    var_buf := clob_buf;
    clob_buf:= var_buf;
    raw_buf := blob_buf;
    blob_buf := raw_buf;
  END;

CREATE PROCEDURE FOO ( a IN OUT CLOB) IS......

CREATE PROCEDURE BAR (b IN OUT VARCHAR2) IS .....
DECLARE
    a VARCHAR2(100) := '1234567';
    b CLOB;
BEGIN
    FOO(a);
    SELECT clob_col INTO b FROM t;
    BAR(b);
END;

Converting LONG to LOB Example 11: CLOBs in PL/SQL Built-In Functions

This example illustrates the use of CLOBs in PL/SQL built-in functions, when converting LONGs to LOBs:

DECLARE
   myStory CLOB;
   revisedStory CLOB;
   myGist VARCHAR2(100):= 'This is my gist.';
   revisedGist VARCHAR2(100);
BEGIN
   -- select a CLOB column into a CLOB variable
   SELECT Story INTO myStory FROM Multimedia_tab WHERE clip_id=10;

   -- perform VARCHAR2 operations on a CLOB variable
   revisedStory := UPPER(SUBSTR(myStory, 100, 1)); 

   -- revisedStory is a temporary LOB
   -- Concat a VARCHAR2 at the end of a CLOB
   revisedStory := revisedStory || myGist;
   -- The following statement will raise an error since myStory is 
   -- longer than 100 bytes
   myGist := myStory;
END;

Converting LONG to LOB Example 12: Using PL/SQL Binds from OCI on LOBs

The LONG-to-LOB API allows LOB PL/SQL binds from OCI to work as follows:

When you call a PL/SQL procedure from OCI, and have an in or out or inout bind, you should be able to bind a variable as SQLT_CHR, where the formal parameter of the PL/SQL procedure is SQLT_CLOB.

Note: C procedures are wrapped inside a PL/SQL stub, so the OCI application always invokes the PL/SQL stub.

For the OCI calling program, the following are likely cases:

Calling PL/SQL Outbinds in the "begin foo(:1); end;" Manner

For example:

text *sqlstmt = (text *)"BEGIN PKG1.P5 (:c); END; " ;

Calling PL/SQL Outbinds in the "call foo(:1);" Manner

For example:

text *sqlstmt = (text *)"CALL PKG1.P5( :c );" ;

In both these cases, the rest of the program is as follows:

   OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
            (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   curlen = 0;

   OCIBindByName(stmthp, &bndhp[3], errhp,
            (text *) ":c4", (sb4) strlen((char *) ":c"),
            (dvoid *) buf5, (sb4) LONGLEN, SQLT_CHR,
            (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
            (ub4) 1, (ub4 *) &curlen, (ub4) OCI_DATA_AT_EXEC);

    OCIStmtExecute(svchp, stmthp, errhp,(ub4) 0,(ub4) 0, (const OCISnapshot*) 0,
    (OCISnapshot*) 0,(ub4) OCI_DEFAULT);

The PL/SQL procedure PKG1.P5 is as follows:

   CREATE OR REPLACE PACKAGE BODY pkg1 AS
     ...
   procedure p5 (c OUT CLOB) is
     -- This might have been table%rowtype (so it   is CLOB now)
   BEGIN
     ...
   END p5;

END pkg1;

Converting LONG to LOB Example 13: Calling PL/SQL and C Procedures from PL/SQL

PL/SQL procedures or functions can accept a CLOB or a VARCHAR2 as a formal parameter. For example the PL/SQL procedure could be one of the following:

• When the formal parameter is CLOB:

  CREATE OR REPLACE PROCEDURE get_lob(table_name IN VARCHAR2, lob INOUT
  CLOB) AS
     ...
  BEGIN
    ...
  END;
  /
  
  

• When the formal parameter is VARCHAR2:

  CREATE OR REPLACE PROCEDURE get_lob(table_name IN VARCHAR2, lob INOUT
  VARCHAR2) AS
     ...
  BEGIN
    ...
  END;
  /
  

The calling function could be of any of the following types:

• When the actual parameter is a CHR:

  create procedure ...
  declare 
  c VARCHAR2[200];
  begin
    get_lob('table_name', c);
  end;
   
  

• When the actual parameter is a CLOB:

  create procedure ...
  declare 
  c table_name.column_name%type   -- This is a CLOB now
  begin
    get_lob('table_name', c);
  end;
  
  

Both the PL/SQL case stubs works with both cases of the actual parameters.