13
Data Interface for Persistent LOBs

This chapter covers the following topics:

• Overview of the Data Interface for Persistent LOBs
• Benefits of Using the Data Interface for Persistent LOBs
• Using the Data Interface for Persistent LOBs in PL/SQL
• Using the Data Interface for Persistent LOBs in OCI

Overview of the Data Interface for Persistent LOBs

The data interface for persistent LOBs includes a set of PL/SQL and OCI APIs that are extended to work with LOB datatypes. These APIs, originally designed for use with legacy datatypes such as LONG, LONG RAW, and VARCHAR2, can also be used with the corresponding LOB datatypes shown in Table 13-1 and Table 13-2. These tables show the legacy datatypes in the "bind or define type" column and the corresponding supported LOB datatype in the "LOB column type" column. You can use the data interface for LOBs to store and manipulate character data and binary data in a LOB column just as if it were stored in the corresponding legacy datatype.

For simplicity, this chapter focuses on character datatypes; however, the same concepts apply to the full set of character and binary datatypes listed in Table 13-1 and Table 13-2.

Table 13-1 Corresponding LONG and LOB Datatypes in SQL and PL/SQL

Bind or Define Type	LOB Column Type	Used For Storing
CHAR	CLOB	Character data
LONG	CLOB	Character data
VARCHAR2	CLOB	Character data
LONG RAW	BLOB	Binary data
RAW	BLOB	Binary data

Table 13-2 Corresponding LONG and LOB Datatypes in OCI

Bind or Define Type	LOB Column Type	Used For Storing
SQLT_AFC(n)	CLOB	Character data
SQLT_CHR	CLOB	Character data
SQLT_LNG	CLOB	Character data
SQLT_VCS	CLOB	Character data
SQLT_BIN	BLOB	Binary data
SQLT_LBI	BLOB	Binary data
SQLT_LVB	BLOB	Binary data

Benefits of Using the Data Interface for Persistent LOBs

Using the data interface for persistent LOBs has the following benefits:

• If your application already uses LONG datatypes, then you can use the same application with LOB datatypes with little or no modification of your existing application required. To do so, just convert LONG datatype columns in your tables to LOB datatype columns as discussed in Chapter 11, "Migrating Table Columns from LONGs to LOBs".
• Performance is better for OCI applications that use sequential access techniques. A piecewise INSERT or fetch using the data interface has comparable performance to using OCI functions like OCILobRead2() and OCILobWrite2(). Because the data interface allows more than 4k bytes of data to be inserted into a LOB in a single OCI call, a round-trip to the server is saved.
• You can read LOB data in one OCIStmtFetch() call, instead of fetching the LOB locator first and then calling OCILobRead2(). This improves performance when you want to read LOB data starting at the beginning.

Using the Data Interface for Persistent LOBs in PL/SQL

The data interface enables you to use LONG and LOB datatypes listed in Table 13-1 to perform the following operations in PL/SQL:

• INSERT or UPDATE character data stored in datatypes such as VARCHAR2, CHAR, or LONG into a CLOB column.
• INSERT or UPDATE binary data stored in datatypes such as RAW or LONG RAW into a BLOB column.
• Use the SELECT statement on CLOB columns to select data into a character buffer variable such as CHAR, LONG, or VARCHAR2.
• Use the SELECT statement on BLOB columns to select data into a binary buffer variable such as RAW and LONG RAW.
• Make cross-type assignments (implicit type conversions) between CLOB and VARCHAR2, CHAR, or LONG variables.
• Make cross-type assignments (implicit type conversions) between BLOB and RAW or LONG RAW variables.
• Pass LOB datatypes to functions defined to accept LONG datatypes or pass LONG datatypes to functions defined to accept LOB datatypes. For example, you can pass a CLOB instance to a function defined to accept another character type, such as VARCHAR2, CHAR, or LONG.
• Use CLOBs with other PL/SQL functions and operators that accept VARCHAR2 arguments such as INSTR and SUBSTR. See "Passing CLOBs to SQL and PL/SQL Built-In Functions" for a complete list.

  Note: When using the data interface for LOBs with the SELECT statement in PL/SQL, you cannot specify the amount you want to read. You can only specify the buffer length of your buffer. If your buffer length is smaller than the LOB data length, then the database throws an exception.

  See Also: Chapter 9, "SQL Semantics and LOBs" for details on LOB support in SQL statements "Some Implicit Conversions Are Not Supported for LOB Datatypes"

Guidelines for Accessing LOB Columns Using the Data Interface in SQL and PL/SQL

This section describes techniques you use to access LOB columns using the data interface for persistent LOBs.

Data from CLOB and BLOB columns can be referenced by regular SQL statements, such as INSERT, UPDATE, and SELECT.

There is no piecewise INSERT, UPDATE, or fetch routine in PL/SQL. Therefore, the amount of data that can be accessed from a LOB column is limited by the maximum character buffer size. PL/SQL supports character buffer sizes up to 32K (32767) bytes. For this reason, LOB data of up to only 32K bytes in size can be accessed by PL/SQL applications using the data interface for persistent LOBs.

If you need to access more than 32K bytes using the data interface, then you must make OCI calls from the PL/SQL code to use the APIs for piecewise insert and fetch.

Use he following are guidelines for using the data interface to access LOB columns:

• INSERT operations

  You can INSERT into tables containing LOB columns using regular INSERT statements in the VALUES clause. The field of the LOB column can be a literal, a character datatype, a binary datatype, or a LOB locator.

• UPDATE operations

  LOB columns can be updated as a whole by UPDATE... SET statements. In the SET clause, the new value can be a literal, a character datatype, a binary datatype, or a LOB locator.

• 4000 byte limit on hex to raw and raw to hex conversions

  The database does not do implicit hex to raw or raw to hex conversions on data that is more than 4000 bytes in size. You cannot bind a buffer of character data to a binary datatype column, and you cannot bind a buffer of binary data to a character datatype column if the buffer is over 4000 bytes in size. Attempting to do so will result in your column data being truncated at 4000 bytes.

  For example, you cannot bind a VARCHAR2 buffer to a LONG RAW or a BLOB column if the buffer is more than 4000 bytes in size. Similarly, you cannot bind a RAW buffer to a LONG or a CLOB column if the buffer is more than 4000 bytes in size.

• SELECT operations

  LOB columns can be selected into character or binary buffers in PL/SQL. If the LOB column is longer than the buffer size, then an exception is raised without filling the buffer with any data. LOB columns can also be selected into LOB locators.

Implicit Assignment and Parameter Passing

Implicit assignment and parameter passing are supported for LOB columns. For the datatypes listed in Table 13-1 and Table 13-2, you can pass or assign: any character type to any other character type, or any binary type to any other binary type using the data interface for persistent LOBs.

Implicit assignment works for variables declared explicitly and for variables declared by referencing an existing column type using the %TYPE attribute as show in the following example. This example assumes that column long_col in table t has been migrated from a LONG to a CLOB column.

CREATE TABLE t (long_col LONG); -- Alter this table to change LONG column to LOB
DECLARE
   a VARCHAR2(100);
   b t.long_col%type; -- This variable changes from LONG to CLOB
BEGIN
   SELECT * INTO b FROM t;
   a := b;  -- This changes from "VARCHAR2 := LONG to VARCHAR2 := CLOB
   b := a;  -- This changes from "LONG := VARCHAR2 to CLOB := VARCHAR2 
END;

Implicit parameter passing is allowed between functions and procedures. For example, you can pass a CLOB to a function or procedure where the formal parameter is defined as a VARCHAR2.

Passing CLOBs to SQL and PL/SQL Built-In Functions

Implicit parameter passing is also supported for built-in PL/SQL functions that accept character data. For example, INSTR can accept a CLOB as well as other character data.

Any SQL or PL/SQL built-in function that accepts a VARCHAR2 can accept a CLOB as an argument. Similarly, a VARCHAR2 variable can be passed to any DBMS_LOB API for any parameter that takes a LOB locator.

Explicit Conversion Functions

In PL/SQL, the following explicit conversion functions convert other data types to CLOB and BLOB datatypes as follows:

• TO_CLOB() converts LONG, VARCHAR2, and CHAR to CLOB
• TO_BLOB() converts LONG RAW and RAW to BLOB

Also note that the conversion function TO_CHAR() can convert a CLOB to a CHAR type.

Calling PL/SQL and C Procedures from SQL

When a PL/SQL or C procedure is called from SQL, buffers with more than 4000 bytes of data are not allowed.

Calling PL/SQL and C Procedures from PL/SQL

You can call a PL/SQL or C procedure from PL/SQL. You can pass a CLOB as an actual parameter where CHR is the formal parameter, or vice versa. The same holds for BLOBs and RAWs.

One example of when these cases can arise is when either the formal or the actual parameter is an anchored type, that is, the variable is declared using the
table_name.column_name%type syntax.

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 a CLOB:

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

• When the formal parameter is a 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 CLOB; 
  begin
    get_lob('table_name', c);
  end;
  
  

Binds of All Sizes in INSERT and UPDATE Operations

Binds of all sizes are supported for INSERT and UPDATE operations on LOB columns. Multiple binds of any size are allowed in a single INSERT or UPDATE statement.

4,000 Byte Limit on Results of SQL Operator

If you bind more than 4,000 bytes of data to a BLOB or a CLOB, and the data consists of an SQL operator, then Oracle limits the size of the result to at most 4,000 bytes.

The following statement inserts only 4,000 bytes because the result of LPAD is limited to 4,000 bytes:

INSERT INTO print_media (ad_sourcetext) VALUES (lpad('a', 5000, 'a'));  

The following statement inserts only 2,000 bytes because the result of LPAD is limited to 4,000 bytes, and the implicit hex to raw conversion converts it to 2,000 bytes of RAW data:

INSERT INTO print_media (ad_photo) VALUES (lpad('a', 5000, 'a'));  

Restrictions on Binds of More Than 4,000 Bytes

The following lists the restrictions for binds of more than 4,000 bytes:

• If a table has both LONG and LOB columns, then you can bind more than 4,000 bytes of data to either the LONG or LOB columns, but not both in the same statement.
• You cannot bind data of any size to LOB attributes in Abstract Data Types (ADTs). This restriction in prior releases still exists. For LOB attributes, first insert an empty LOB locator and then modify the contents of the LOB using OCILob* functions.
• In an INSERT AS SELECT operation, binding of any length data to LOB columns is not allowed.

Example: PL/SQL - Using Binds of More Than 4,000 Bytes in INSERT and UPDATE

DECLARE
  bigtext VARCHAR2(32767);
  smalltext VARCHAR2(2000);
  bigraw RAW (32767);
BEGIN
  bigtext := LPAD('a', 32767, 'a');
  smalltext := LPAD('a', 2000, 'a');
  bigraw := utl_raw.cast_to_raw (bigtext);

  /* Multiple long binds for LOB columns are allowed for INSERT: */
  INSERT INTO print_media(product_id, ad_id, ad_sourcetext, ad_composite)
    VALUES (2004, 1, bigtext, bigraw);

  /* Single long bind for LOB columns is allowed for INSERT: */
  INSERT INTO print_media (product_id, ad_id, ad_sourcetext)
    VALUES (2005, 2, smalltext);  

  bigtext := LPAD('b', 32767, 'b');
  smalltext := LPAD('b', 20, 'a');
  bigraw := utl_raw.cast_to_raw (bigtext);

  /* Multiple long binds for LOB columns are allowed for UPDATE: */
  UPDATE print_media SET ad_sourcetext = bigtext, ad_composite = bigraw,
    ad_finaltext = smalltext;

  /* Single long bind for LOB columns is allowed for UPDATE: */
  UPDATE print_media set ad_sourcetext = smalltext, ad_finaltext = bigtext;

  /* The following is NOT allowed because we are trying to insert more than
     4000 bytes of data in a LONG and a LOB column: */
  INSERT INTO print_media(product_id, ad_id, ad_sourcetext, press_release)
    VALUES (2030, 3, bigtext, bigtext);
  
  /* The following is NOT allowed because we are trying to insert
     data into LOB attribute */
  INSERT into print_media(product_id, ad_id, ad_header)
     VALUES (2049, 4, adheader_typ(null, null, null, bigraw));

  /* The following is not allowed because we try to perform INSERT AS
     SELECT data INTO LOB */
  INSERT INTO print_media(product_id, ad_id, ad_sourcetext)
    SELECT 2056, 5, bigtext FROM dual;

END;
/

4,000 Byte Result Limit for SQL Operators

The following example illustrates how the result for SQL operators is limited to 4,000 bytes.

/* The following command inserts only 4,000 bytes because the result of
 * LPAD is limited to 4,000 bytes */
INSERT INTO print_media(product_id, ad_id, ad_sourcetext)
  VALUES (2004, 5, lpad('a', 5000, 'a'));
select length(ad_sourcetext) from print_media 
  where product_id=2004 and ad_id=5;
rollback;

/* The following command inserts only 2,000 bytes because the result of
 * LPAD is limited to 4,000 bytes, and the implicit hex to raw conversion
 * converts it to 2,000 bytes of RAW data. */
INSERT INTO print_media(product_id, ad_id, ad_composite)
  VALUES (2004, 5, lpad('a', 5000, 'a'));
select length(ad_composite) from print_media 
  where product_id=2004 and ad_id=5;
rollback;

Using the Data Interface for LOBs with INSERT, UPDATE, and SELECT Operations

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

DECLARE
  ad_buffer VARCHAR2(100);
BEGIN
  INSERT INTO print_media(product_id, ad_id, ad_sourcetext)
    VALUES(2004, 5, 'Source for advertisement 1');
  UPDATE print_media SET ad_sourcetext= 'Source for advertisement 2'
    WHERE product_id=2004 and ad_id=5;
  /* This will get the LOB column if it is up to 100 bytes, otherwise it will
   * raise an exception */
  SELECT ad_sourcetext INTO ad_buffer FROM print_media 
    WHERE product_id=2004 and ad_id=5;
END;
/

Using the Data Interface for LOBs in Assignments and Parameter Passing

The data interface for LOBs enables implicit assignment and parameter passing as shown in the following 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 OR REPLACE PROCEDURE FOO ( a IN OUT CLOB) IS
BEGIN
  -- Any procedure body
  a := 'abc';
END;
/

CREATE OR REPLACE PROCEDURE BAR (b IN OUT VARCHAR2) IS
BEGIN
  -- Any procedure body
  b := 'xyz';
END;
/

DECLARE
  a VARCHAR2(100) := '1234567';
  b CLOB;
BEGIN
  FOO(a);
  SELECT clob_col INTO b FROM t;
  BAR(b);
END;
/

Using the Data Interface for LOBs with PL/SQL Built-In Functions

This example illustrates the use of CLOBs in PL/SQL built-in functions, using the data interface for LOBs:

DECLARE
  my_ad CLOB;
  revised_ad CLOB;
  myGist VARCHAR2(100):= 'This is my gist.';
  revisedGist VARCHAR2(100);
BEGIN
  INSERT INTO print_media (product_id, ad_id, ad_sourcetext)
    VALUES (2004, 5, 'Source for advertisement 1');  

  -- select a CLOB column into a CLOB variable
  SELECT ad_sourcetext INTO my_ad FROM print_media 
    WHERE product_id=2004 and ad_id=5;

  -- perform VARCHAR2 operations on a CLOB variable
  revised_ad := UPPER(SUBSTR(my_ad, 100, 1));
 
  -- revised_ad is a temporary LOB
  -- Concat a VARCHAR2 at the end of a CLOB
  revised_ad := revised_ad || myGist;

  -- The following statement will raise an error if my_ad is
  -- longer than 100 bytes
  myGist := my_ad;
END;
/

Using the Data Interface for Persistent LOBs in OCI

This section discusses OCI functions included in the data interface for persistent LOBs. These OCI functions work for LOB datatypes exactly the same way as they do for LONG datatypes. Using these functions, you can perform INSERT, UPDATE, fetch, bind, and define operations in OCI on LOBs using the same techniques you would use on other datatypes that store character or binary data.

Binding LOB Datatypes in OCI

You can bind LOB datatypes in the following operations:

• Regular, piecewise, and callback binds for INSERT and UPDATE operations
• Array binds for INSERT and UPDATE operations
• Parameter passing across PL/SQL and OCI boundaries

Piecewise operations can be performed by polling or by providing a callback. To support these operations, the following OCI functions accept the LONG and LOB datatypes listed in Table 13-2.

• OCIBindByName() and OCIBindByPos()
  These functions create an association between a program variable and a placeholder in the SQL statement or a PL/SQL block for INSERT and UPDATE operations.
• OCIBindDynamic()
You use this call to register callbacks for dynamic data allocation for INSERT and UPDATE operations
• OCIStmtGetPieceInfo() and OCIStmtSetPieceInfo()
  These calls are used to get or set piece information for piecewise operations.

Defining LOB Datatypes in OCI

The data interface for persistent LOBs allows the following OCI functions to accept the LONG and LOB datatypes listed in Table 13-2.

• OCIDefineByPos()
  This call associates an item in a SELECT list with the type and output data buffer.
• OCIDefineDynamic()
This call registers user callbacks for SELECT operations if the
  OCI_DYNAMIC_FETCH mode was selected in OCIDefineByPos() function call.

When you use these functions with LOB types, the LOB data, and not the locator, is selected into your buffer. Note that in OCI, you cannot specify the amount you want to read using the data interface for LOBs. You can only specify the buffer length of your buffer. The database only reads whatever amount fits into your buffer and the data is truncated.

Using Multibyte Charactersets in OCI with the Data Interface for LOBs

When the client characterset is in a multibyte format, functions included in the data interface operate the same way with LOB datatypes as they do for LONG datatypes as follows:

• For a piecewise fetch in a multibyte characterset, a multibyte character could be cut in the middle, with some bytes at the end of one buffer and remaining bytes in the next buffer.
• For a regular fetch, if the buffer cannot hold all bytes of the last character, then Oracle returns as many bytes as fit into the buffer, hence returning partial characters.

Using OCI Functions to Perform INSERT or UPDATE on LOB Columns

This section discusses the various techniques you can use to perform INSERT or UPDATE operations on LOB columns using the data interface. The operations described in this section assume that you have initialized the OCI environment and allocated all necessary handles.

Simple INSERTs or UPDATEs in One Piece

To perform simple INSERT or UPDATE operations in one piece using the data interface for persistent LOBs, perform the following steps:

1. Call OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.
2. Call OCIBindByName() or OCIBindbyPos() to bind a placeholder in OCI_DEFAULT mode to bind a LOB as character data or binary data.
3. Call OCIStmtExecute() to do the actual INSERT or UPDATE operation.

Using Piecewise INSERTs and UPDATEs with Polling

To perform piecewise INSERT or UPDATE operations with polling using the data interface for persistent LOBs, do the following steps:

1. Call OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.
2. Call OCIBindByName() or OCIBindbyPos() to bind a placeholder in OCI_DATA_AT_EXEC mode to bind a LOB as character data or binary data.
3. Call OCIStmtExecute() in default mode. Do each of the following in a loop while the value returned from OCIStmtExecute() is OCI_NEED_DATA. Terminate your loop when the value returned from OCIStmtExecute() is OCI_SUCCESS.
   • Call OCIStmtGetPieceInfo() to retrieve information about the piece to be inserted.
   • Call OCIStmtSetPieceInfo() to set information about piece to be inserted.

Piecewise INSERTs and UPDATEs with Callback

To perform piecewise INSERT or UPDATE operations with callback using the data interface for persistent LOBs, do the following steps:

1. Call OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.
2. Call OCIBindByName() or OCIBindbyPos() to bind a placeholder in OCI_DATA_AT_EXEC mode to bind the LOB column as character data or binary data.
3. Call OCIBindDynamic() to specify the callback.
4. Call OCIStmtExecute() in default mode.

Array INSERT and UPDATE Operations

To perform array INSERT or UPDATE operations using the data interface for persistent LOBs, use any of the techniques discussed in this section in conjunction with OCIBindArrayOfStruct(), or by specifying the number of iterations (iter), with iter value greater than 1, in the OCIStmtExecute() call.

Using the Data Interface to Fetch LOB Data in OCI

This section discusses techniques you can use to fetch data from LOB columns in OCI using the data interface for persistent LOBs.

Simple Fetch in One Piece

To perform a simple fetch operation on LOBs in one piece using the data interface for persistent LOBs, do the following:

1. Call OCIStmtPrepare() to prepare the SELECT statement in OCI_DEFAULT mode.
2. Call OCIDefineByPos() to define a select list position in OCI_DEFAULT mode to define a LOB as character data or binary data.
3. Call OCIStmtExecute() to run the SELECT statement.
4. Call OCIStmtFetch() to do the actual fetch.

Piecewise Fetch with Polling

To perform a piecewise fetch operation on a LOB column with polling using the data interface for LOBs, do the following steps:

1. Call OCIStmtPrepare() to prepare the SELECT statement in OCI_DEFAULT mode.
2. Call OCIDefinebyPos() to define a select list position in OCI_DYNAMIC_FETCH mode to define the LOB column as character data or binary data.
3. Call OCIStmtExecute() to run the SELECT statement.
4. Call OCIStmtFetch() in default mode. Do each of the following in a loop while the value returned from OCIStmtFetch() is OCI_NEED_DATA. Terminate your loop when the value returned from OCIStmtFetch() is OCI_SUCCESS.
   • Call OCIStmtGetPieceInfo() to retrieve information about the piece to be fetched.
   • Call OCIStmtSetPieceInfo() to set information about piece to be fetched.

Piecewise with Callback

To perform a piecewise fetch operation on a LOB column with callback using the data interface for persistent LOBs, do the following:

1. Call OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.
2. Call OCIDefinebyPos() to define a select list position in OCI_DYNAMIC_FETCH mode to define the LOB column as character data or binary data.
3. Call OCIStmtExecute() to run the SELECT statement.
4. Call OCIDefineDynamic() to specify the callback.
5. Call OCIStmtFetch() in default mode.

Array Fetch

To perform an array fetch in OCI using the data interface for persistent LOBs, use any of the techniques discussed in this section in conjunction with OCIDefineArrayOfStruct(), or by specifying the number of iterations (iter), with the value of iter greater than 1, in the OCIStmtExecute() call.

PL/SQL and C Binds from OCI

When you call a PL/SQL procedure from OCI, and have an in or out or in/out bind, you should be able to:

• Bind a variable as SQLT_CHR or SQLT_LNG where the formal parameter of the PL/SQL procedure is SQLT_CLOB, or
• Bind a variable as SQLT_BIN or SQLT_LBI where the formal parameter is SQLT_BLOB

The following two cases work:

Calling PL/SQL Out-binds in the "begin foo(:1); end;" Manner.

Here is an example of calling PL/SQL out-binds in the "begin foo(:1);end;" manner:

text *sqlstmt = (text *)"BEGIN get_lob(:c); END; " ;

Calling PL/SQL Out-binds in the "call foo(:1);" Manner.

Here is an example of calling PL/SQL out-binds in the "call foo(:1);" manner:

text *sqlstmt = (text *)"CALL get_lob( :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 *) ":c", (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);

The PL/SQL procedure, get_lob(), is as follows:

procedure get_lob(c INOUT CLOB) is  -- This might have been column%type 
  begin
  ... /* The procedure body could be in PL/SQL or C*/
  end;

Example: C (OCI) - Binds of More than 4,000 Bytes for INSERT and UPDATE

void insert()         /* A function in an OCI program */ 
{ 
/* The following is allowed */ 
   ub1 buffer[8000]; 
   text *insert_sql = "INSERT INTO Print_media(ad_sourcetext, ad_composite, 
press_release)  
              VALUES (:1, :2, :3)"; 
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
              (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,  
              SQLT_LBI, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 2000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
 
void insert() 
{ 
/* The following is allowed */ 
   ub1 buffer[8000]; 
   text *insert_sql = "INSERT INTO Print_media (ad_sourcetext,press_release)  
              VALUES (:1, :2)"; 
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
              (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
 
void update() 
{ 
/* The following is allowed, no matter how many rows it updates */ 
ub1 buffer[8000]; 
   text *insert_sql = (text *)"UPDATE Print_media SET  
              ad_sourcetext = :1, ad_photo=:2, press_release=:3"; 
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
              (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,  
              SQLT_LBI, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 2000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT);  
} 
 
void update() 
{ 
/* The following is allowed, no matter how many rows it updates */ 
   ub1 buffer[8000]; 
   text *insert_sql = (text *)"UPDATE Print_media SET  
              ad_sourcetext = :1, ad_photo=:2, press_release=:3"; 
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
             (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000,  
             SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 2000,  
             SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 8000,  
             SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
 
void insert() 
{ 
/* Piecewise, callback and array insert/update operations similar to  
the allowed regular insert/update operations are also allowed */ 
} 
 
void insert() 
{ 
/* The following is NOT allowed because we try to insert >4000 bytes 
to both LOB and LONG columns */ 
ub1 buffer[8000]; 
text *insert_sql = (text *)"INSERT INTO Print_media (ad_composite, press_
release)  
          VALUES (:1, :2)"; 
OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
         (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,  
          SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,  
          SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
void insert() 
{ 
/* The following is NOT allowed because we try to insert data into 
LOB attributes */ 
ub1 buffer[8000]; 
text *insert_sql = (text *)"INSERT INTO Print_media (adheader_typ)  
          VALUES (adheader_typ(NULL, NULL, NULL, NULL, NULL,:1, NULL))"; 
OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
         (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000,  
          SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
 
void insert() 
{ 
/* The following is NOT allowed because we try to do insert as  
select character data into LOB column */ 
   ub1 buffer[8000]; 
   text *insert_sql = (text *)"INSERT INTO Print_media (ad_sourcetext) 
            SELECT :1 from FOO"; 
   OCIStmtPrepare(stmthp, errhp, insert_sql,strlen((char*)insert_sql),  
            (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,  
            SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
 
void insert() 
{ 
/* Other update operations similar to the disallowed insert operations are also  
not allowed. Piecewise and callback insert/update operations similar to the 
disallowed regular insert/update operations are also not allowed  */ 
} 

Using the Data Interface for LOBs in PL/SQL Binds from OCI on LOBs

The data interface for LOBs 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 in out bind, you should be able to bind a variable as SQLT_CHR, where the formal parameter of the PL/SQL procedure is SQLT_CLOB.

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

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

For example:

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

Calling PL/SQL Out-binds 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;

Binding LONG Data for LOB Columns in Binds Greater Than 4,000 Bytes

The following example illustrates binding character data for a LOB column:

void simple_insert()
{
  word buflen;
  text buf[5000];
  text *insstmt = (text *) "INSERT INTO print_media(product_id, ad_id, ad_
sourcetext) VALUES (2004, 1, :SRCTXT)";

  OCIStmtPrepare(stmthp, errhp, insstmt, (ub4)strlen((char *)insstmt), 
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
  
  OCIBindByName(stmthp, &bndhp[0], errhp,
                (text *) ":SRCTXT", (sb4) strlen((char *) ":SRCTXT"T),
                (dvoid *) buf, (sb4) sizeof(buf), SQLT_CHR,
                (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);

  memset((void *)buf, (int)'A', (size_t)5000);
  OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                 (const OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT);
}

Binding LONG Data to LOB Columns Using Piecewise INSERT with Polling

The following example illustrates using piecewise INSERT with polling using the data interface for LOBs.

void piecewise_insert()
{
  text *sqlstmt = (text *)"INSERT INTO print_media(product_id, ad_id, ad_
sourcetext) VALUES (:1, :2, :3)";
  ub2 rcode;
  ub1 piece, i;
  word product_id = 2004;
  word ad_id = 2;
  word buflen;
  char buf[5000];

  OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), 
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
  OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 1,
               (dvoid *) &product_id, (sb4) sizeof(product_id), SQLT_INT,
               (dvoid *) 0, (ub2 *)0, (ub2 *)0,
               (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);
  OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 2,
               (dvoid *) &ad_id, (sb4) sizeof(ad_id), SQLT_INT,
               (dvoid *) 0, (ub2 *)0, (ub2 *)0,
               (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);
  OCIBindByPos(stmthp, &bndhp[2], errhp, (ub4) 3,
               (dvoid *) 0, (sb4) 15000, SQLT_LNG,
               (dvoid *) 0, (ub2 *)0, (ub2 *)0,
               (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC);

  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 *)buf, (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 *)buf,
                              &buflen, piece, (dvoid *) 0, &rcode))
        {
          DISCARD printf("ERROR: OCIStmtSetPieceInfo: %d \n", retval);
          break;
        }
      
      break;
    case OCI_SUCCESS:
      break;
    default:
      DISCARD printf( "oci exec returned %d \n", retval);
      report_error(errhp);
      retval = OCI_SUCCESS;
    } /* end switch */
    if (retval == OCI_SUCCESS) 
      break;
  } /* end while(1) */
}

Binding LONG Data to LOB Columns Using Piecewise INSERT with Callback

The following example illustrates binding LONG data to LOB columns using a piecewise INSERT with callback:

void callback_insert()
{
  word buflen = 15000;
  word product_id = 2004;
  word ad_id = 3;
  text *sqlstmt = (text *) "INSERT INTO print_media(product_id, ad_id, ad_
sourcetext) VALUES (:1, :2, :3)";
  word pos = 3;

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

  OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 1,
               (dvoid *) &product_id, (sb4) sizeof(product_id), SQLT_INT,
               (dvoid *) 0, (ub2 *)0, (ub2 *)0,
               (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);
  OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 2,
               (dvoid *) &ad_id, (sb4) sizeof(ad_id), SQLT_INT,
               (dvoid *) 0, (ub2 *)0, (ub2 *)0,
               (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);
  OCIBindByPos(stmthp, &bndhp[2], errhp, (ub4) 3,
               (dvoid *) 0, (sb4) buflen, SQLT_CHR,
               (dvoid *) 0, (ub2 *)0, (ub2 *)0,
               (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC);

  OCIBindDynamic(bndhp[2], errhp, (dvoid *) (dvoid *) &pos,
                 insert_cbk, (dvoid *) 0, (OCICallbackOutBind) 0);

  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 insert_cbk(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);
  char   buf[5000];

  switch(inpos)
  {
  case 3:
    memset((void *)buf, (int) 'A'+a, (size_t) 5000);
    *bufpp = (dvoid *) buf;
    *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;
}

Binding LONG Data to LOB Columns Using an Array INSERT

The following example illustrates binding character data for LOB columns using an array INSERT operation:

void array_insert()
{
  word buflen;
  word arrbuf1[5];
  word arrbuf2[5];
  text arrbuf3[5][5000];
  text *insstmt = (text *)"INSERT INTO print_media(product_id, ad_id, ad_
sourcetext) VALUES (:PID, :AID, :SRCTXT)"

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

  OCIBindByName(stmthp, &bndhp[0], errhp,
                (text *) ":PID", (sb4) strlen((char *) ":PID"),
                (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 *) ":AID", (sb4) strlen((char *) ":AID"),
                (dvoid *) &arrbuf2[0], (sb4) sizeof(arrbuf2[0]), SQLT_INT,
                (dvoid *) 0, (ub2 *)0, (ub2 *) 0,
                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);

  OCIBindByName(stmthp, &bndhp[2], errhp,
                (text *) ":SRCTXT", (sb4) strlen((char *) ":SRCTXT"),
                (dvoid *) arrbuf3[0], (sb4) sizeof(arrbuf3[0]), SQLT_CHR,
                (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);

  OCIBindArrayOfStruct(bndhp[0], ERRH, sizeof(arrbuf1[0]),
                       indsk, rlsk, rcsk);
  OCIBindArrayOfStruct(bndhp[1], ERRH, sizeof(arrbuf2[0]),
                       indsk, rlsk, rcsk);
  OCIBindArrayOfStruct(bndhp[1], ERRH, sizeof(arrbuf3[0]),
                       indsk, rlsk, rcsk);

  for (i=0; i<5; i++)
  {
    arrbuf1[i] = 2004;
    arrbuf2[i] = i+4;
    memset((void *)arrbuf3[i], (int)'A'+i, (size_t)5000);
  }
  OCIStmtExecute(svchp, stmthp, errhp, (ub4) 5, (ub4) 0,
                 (const OCISnapshot*) 0, (OCISnapshot*) 0,
                 (ub4) OCI_DEFAULT);

}

Selecting a LOB Column into a LONG Buffer Using a Simple Fetch

The following example illustrates selecting a LOB column using a simple fetch:

void simple_fetch()
{
  word i, buf1 = 0;
  word retval;
  text buf[15000];
  text *selstmt = (text *) "SELECT AD_SOURCETEXT FROM PRINT_MEDIA WHERE PRODUCT_
ID = 2004";

  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, &defhp[1], errhp, (ub4) 2, (dvoid *) buf,
                   (sb4) sizeof(buf1), (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("buf = %.*s\n", buf2);
  }
}

Selecting a LOB Column into a LONG Buffer Using Piecewise Fetch with Polling

The following example illustrates selecting a LOB column into a LONG buffer using a piecewise fetch with polling:

void piecewise_fetch()
{
  text buf[15000];
  word buflen=5000;
  word retval;
  text *selstmt = (text *) "SELECT AD_SOURCETEXT FROM PRINT_MEDIA 
                  WHERE PRODUCT_ID = 2004 AND AD_ID=2";

  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 *)buf, 5000);
    switch(retval)
    {
    case OCI_NEED_DATA:
      OCIStmtGetPieceInfo(stmthp, errhp, &hdlptr, &hdltype,
                          &in_out, &iter, &idx, &piece);
      OCIStmtSetPieceInfo(hdlptr, hdltype, errhp,
                          (dvoid *) buf, &buflen, piece,
                          (CONST dvoid *) &indp1, (ub2 *) 0);
      retval = OCI_NEED_DATA;
      break;
    default:
      DISCARD printf("ERROR: piece-wise fetching, %d\n", retval);
      return;
    } /* end switch */
    retval = OCIStmtFetch(stmthp, errhp, (ub4) 1 ,
                          (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
    printf("Data : %s\n"; buf);
  } /* end while */
}

Selecting a LOB Column into a LONG Buffer Using Piecewise Fetch with Callback

The following example illustrates selecting a LONG column into a LOB buffer when using a piecewise fetch with callback:

char buf[5000];
void callback_fetch()
{
  text *sqlstmt = (text *) "SELECT AD_SOURCETEXT FROM PRINT_MEDIA WHERE PRODUCT_
ID = 2004 AND AD_ID=3";
  
  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)3 * sizeof(buf), SQLT_CHR,
                 (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                 (ub4) OCI_DYNAMIC_FETCH);
  
  OCIDefineDynamic(dfnhp[0], errhp, (dvoid *) &outpos,
                   (OCICallbackDefine) fetch_cbk);

  OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                 (const OCISnapshot*) 0, (OCISnapshot*) 0,
                 (ub4) OCI_DEFAULT);
  buf[ 4999 ] = '\0';
  printf("Select callback: Last piece: %s\n", buf);
}

/* -------------------------------------------------------------- */
/* Fetch callback to specify buffers. */
/* -------------------------------------------------------------- */
STATICF sb4 fetch_cbk(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 = 5000;
  switch(outpos)
  {
  case 1:
    a ++;
    *bufpp = (dvoid *) buf;
    *alenpp = &len;
    break;
  default:
    *bufpp = (dvoid *) 0;
    *alenpp = (ub4 *) 0;
    DISCARD printf("ERROR: invalid position number: %d\n", pos);
  }
  *indpp = (dvoid *) 0;
  *rcpp = (ub2 *) 0;

  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;
}

Selecting a LOB Column into a LONG Buffer Using an Array Fetch

The following example illustrates selecting a LOB column into a LONG buffer using an array fetch:

void array_fetch()
{
  word i;
  text arrbuf[5][5000];
  text *selstmt = (text *) "SELECT AD_SOURCETEXT FROM PRINT_MEDIA WHERE PRODUCT_
ID = 2004 AND AD_ID >=4";

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

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

  OCIDefineByPos(stmthp, &defhp1, errhp, (ub4) 2,
                   (dvoid *) arrbuf[0], (sb4) sizeof(arrbuf[0]),
                   (ub2) SQLT_CHR, (dvoid *) 0,
                   (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT);

  OCIDefineArrayOfStruct(dfnhp[0], ERRH, sizeof(arrbuf[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", arrbuf[0]);
    DISCARD printf("%d, %s\n", arrbuf[1]);
    DISCARD printf("%d, %s\n", arrbuf[2]);
    DISCARD printf("%d, %s\n", arrbuf[3]);
    DISCARD printf("%d, %s\n", arrbuf[4]);
  }
}