10
Internal Persistent LOBs

Use Case Model

This chapter describes each operation on LOBs (such as "Write Data to a LOB") in terms of a use case. Table 10-1, "Internal Persistent LOB Basic Operations", alphabetically lists all these use cases.

Individual Use Cases

Each detailed internal persistent LOB use case operation description is laid out as follows:

• Use case figure. This UML drawing depicts the use case. Appendix A, "Unified Modeling Language Diagrams" explains how to interpret these diagrams.
• Purpose. The purpose of this LOB use case.
• Usage Notes. Guidelines to assist your implementation of the LOB operation.
• Syntax. The main syntax used to perform the LOBs related activity.
• Scenario. Describes the implementation of the use case in terms of the sample schema used in the examples. For further details on sample schemas, refer to Oracle9i Sample Schemas.
• Examples. How to apply each use case based on the sample schema used.

Use Case Model: Internal Persistent LOBs Operations

Table 10-1, indicates with a + where examples are provided for specific use cases in each programmatic environment. An "S" indicates that SQL is used for that use case and applicable programmatic environment(s).

We refer to programmatic environments by means of the following abbreviations:

• P -- PL/SQL, using the DBMS_LOB Package
• O -- C, using OCI (Oracle Call Interface)
• CP -- C++, using OCCI (Oracle C++ Call Interface)
• B -- COBOL, using Pro*COBOL precompiler
• C -- C/C++, using Pro*C/C++ precompiler
• V -- Visual Basic, using OO4O (Oracle Objects for OLE)
• J -- Java, using JDBC (Java Database Connectivity)
• S -- SQL

Table 10-1 Internal Persistent LOB Basic Operations  

Creating a Table Containing One or More LOB Columns

Figure 10-1 Use Case Diagram: Creating a Table Containing one or More LOB Columns

Purpose

This procedure describes how to create a table containing one or more LOB columns.

Usage Notes

When you use functions, EMPTY_BLOB () and EMPTY_CLOB(), the resulting LOB is initialized, but not populated with data. LOBs that are empty are not null, and vice versa. This is discussed in more detail in "Inserting a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()".

• For information about creating nested tables that have one or more columns of LOB datatype, see "Creating a Nested Table Containing a LOB".
• Creating an object column containing one or more LOBs is discussed under the heading, "Creating a Table Containing an Object Type with a LOB Attribute".

When creating tables that contain LOBs use the guidelines and examples described in the following:

• Chapter 2, "Basic LOB Components", "Initializing Internal LOBs to NULL or Empty"
• Chapter 4, "Managing LOBs"
• Chapter 7, "Modeling and Design"

  See Also: Oracle9i SQL Reference for a complete specification of syntax for using LOBs in CREATE TABLE and ALTER TABLE with: BLOB, CLOB, NCLOB and BFILE columns EMPTY_BLOB and EMPTY_CLOB functions LOB storage clause for internal LOB columns, and LOB attributes of embedded objects

Syntax

Use the following syntax reference:

• SQL: Oracle9i SQL Reference, "Chapter 7, SQL Statements" -- CREATE TABLE

Scenario

These examples use the following Oracle9i Sample Schemas:

• Human Resources (HR)
• Order Entry (OE)
• Product Media (PM)

You must create the HR and OE schemas before your create the PM schema. For details on these schemas, you must refer to Oracle9i Sample Schemas.

Examples

How to create a table containing a LOB column is illustrated with the following example, in SQL:

• SQL: Create a Table Containing One or More LOB Columns

SQL: Create a Table Containing One or More LOB Columns

You may need to set up the following data structures for certain examples in this manual to work.

/*  Setup script for creating Print_media, 
    Online_media and associated structures 
*/

DROP USER pm CASCADE;
DROP DIRECTORY ADPHOTO_DIR;
DROP DIRECTORY ADCOMPOSITE_DIR;
DROP DIRECTORY ADGRAPHIC_DIR;
DROP INDEX onlinemedia CASCADE CONSTRAINTS;
DROP INDEX printmedia CASCADE CONSTRAINTS;
DROP TABLE online_media CASCADE CONSTRAINTS;
DROP TABLE print_media CASCADE CONSTRAINTS;
DROP TYPE textdoc_typ;
DROP TYPE textdoc_tab;
DROP TYPE adheader_typ;
DROP TABLE adheader_typ;
CREATE USER pm;
GRANT CONNECT, RESOURCE to pm;

CREATE DIRECTORY ADPHOTO_DIR AS '/tmp/';
CREATE DIRECTORY ADCOMPOSITE_DIR AS '/tmp/';
CREATE DIRECTORY ADGRAPHIC_DIR AS '/tmp/';
CREATE DIRECTORY media_dir AS '/tmp/';
GRANT READ ON DIRECTORY ADPHOTO_DIR to pm;
GRANT READ ON DIRECTORY ADCOMPOSITE_DIR to pm;
GRANT READ ON DIRECTORY ADGRAPHIC_DIR to pm;
GRANT READ ON DIRECTORY media_dir to pm;

CONNECT pm/pm  (or &pass);
COMMIT;

CREATE TABLE a_table (blob_col BLOB); 

CREATE TYPE adheader_typ AS OBJECT ( 
   header_name    VARCHAR2(256),  
   creation_date  DATE,  
   header_text    VARCHAR(1024), 
   logo           BLOB );

CREATE TYPE textdoc_typ AS OBJECT ( 
   document_typ   VARCHAR2(32), 
   formatted_doc  BLOB);

CREATE TYPE Textdoc_ntab AS TABLE of textdoc_typ;

CREATE TABLE adheader_tab of adheader_typ (
Ad_finaltext DEFAULT EMPTY_CLOB(), CONSTRAINT 
Take CHECK (Take IS NOT NULL),  DEFAULT NULL);

CREATE TABLE online_media
( product_id  NUMBER(6),
product_photo ORDSYS.ORDImage,
product_photo_signature ORDSYS.ORDImageSignature,
product_thumbnail ORDSYS.ORDImage, 
product_video ORDSYS.ORDVideo,
product_audio ORDSYS.ORDAudio, 
product_text CLOB,
product_testimonials ORDSYS.ORDDoc);

CREATE UNIQUE INDEX onlinemedia_pk
  ON online_media (product_id);
  
ALTER TABLE online_media
ADD (CONSTRAINT onlinemedia_pk
PRIMARY KEY (product_id), CONSTRAINT loc_c_id_fk
FOREIGN KEY (product_id)  REFERENCES oe.product_information(product_id)
);

CREATE TABLE print_media
(product_id NUMBER(6), 
ad_id NUMBER(6),
ad_composite BLOB,
ad_sourcetext CLOB, 
ad_finaltext CLOB,
ad_fktextn NCLOB,
ad_testdocs_ntab textdoc_tab,
ad_photo BLOB, 
ad_graphic BFILE,
ad_header adheader_typ,
press_release LONG) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;

CREATE UNIQUE INDEX printmedia_pk
  ON print_media (product_id, ad_id);

ALTER TABLE print_media
ADD (CONSTRAINT printmedia_pk
PRIMARY KEY (product_id, ad_id), 
CONSTRAINT printmedia_fk FOREIGN KEY (product_id) 
REFERENCES oe.product_information(product_id)
);

Creating a Table Containing an Object Type with a LOB Attribute

Figure 10-2 Use Case Diagram: Creating a Table Containing an Object Type with a LOB Attribute

Purpose

This procedure describes how to create a table containing an object type with a LOB attribute.

Usage Notes

When creating tables that contain LOBs use the guidelines and examples described in the following:

• Chapter 2, "Basic LOB Components", "Initializing Internal LOBs to NULL or Empty"
• Chapter 4, "Managing LOBs"
• Chapter 7, "Modeling and Design"

Syntax

See the following specific reference for a detailed syntax description:

• SQL: Oracle9i SQL Reference, "Chapter 7, SQL Statements" -- CREATE TABLE.

Scenario

You must create the object type that contains LOB attributes before you can proceed to create a table that makes use of that object type.

This example uses the Product Media schema included with Oracle9i sample schemas. For details on this schema, you must refer to the Oracle9i Sample Schemas manual.

Examples

This example is provided in SQL and applies to all programmatic environments:

• SQL: Creating a Table Containing an Object Type with a LOB Attribute

SQL: Creating a Table Containing an Object Type with a LOB Attribute

Create adheader_typ as a basis for tables containing ad headings or titles and logos used in these examples:

CREATE TYPE adheader_typ AS OBJECT ( 
   header_name    VARCHAR2(256),  
   creation_date  DATE,  
   header_text    VARCHAR(1024), 
   logo           BLOB );

/* Create table adheader_tab Using SQL DDL: */
CREATE TABLE adheader_tab of adheader_typ (
   logo DEFAULT EMPTY_BLOB() CONSTRAINT Take CHECK (Take IS NOT NULL),
   creation_date DATE );

Create Type adheader_typ using SQL DDL as a basis for a table that will contain the column object:

DROP TYPE adheader;
DROP TABLE adheader_tab;
CREATE TYPE adheader_typ AS OBJECT ( 
   header_name    VARCHAR2(256),  
   creation_date  DATE,  
   header_text    VARCHAR(1024), 
   logo           BLOB );

/*  Create support table adheader_tab as an archive of 
    ad headers using SQL DDL: */
CREATE TABLE adheader_tab of adheader_typ;

Creating a Nested Table Containing a LOB

Figure 10-3 Use Case Diagram: Creating a Nested Table Containing a LOB

Purpose

This procedure creates a nested table containing a LOB.

Usage Notes

When creating tables that contain LOBs use the guidelines and examples described in the following sections and these chapters:

• Chapter 2, "Basic LOB Components", "Initializing Internal LOBs to NULL or Empty"
• Chapter 4, "Managing LOBs"
• Chapter 7, "Modeling and Design"

Syntax

Use the following syntax reference:

• SQL: Oracle9i SQL Reference , "Chapter 7, SQL Statements" -- CREATE TABLE.

Scenario

Create the object type that contains the LOB attributes before you create a nested table based on that object type. In our example, table Print_media contains nested table ad_textdoc_ntab that has type textdoc_tab. This type uses two LOB datatypes:

• BFILE - an advertisement graphic
• CLOB - an advertisement transcript

We have already described how to create a table with LOB columns in the previous section (see "Creating a Table Containing One or More LOB Columns"), so here we only describe the syntax for creating the underlying object type.

Examples

The example "SQL: Creating a Nested Table Containing a LOB" is provided in the SQL programmatic environment.

SQL: Creating a Nested Table Containing a LOB

/* Create type textdoc_typ as the base type 
   for the nested table textdoc_ntab, 
   where textdoc_ntab contains a LOB: 
*/
DROP TYPE textdoc_typ force; 
DROP TYPE textdoc_ntab; 
DROP TABLE textdoc_ntable; 
CREATE TYPE textdoc_typ AS OBJECT
( 
   document_typ    VARCHAR2(32),  
   formatted_doc   BLOB 
);

/* The type has been created. Now you need a */
/* nested table of that type to embed in */
/* table Print_media, so: */
CREATE TYPE textdoc_ntab AS TABLE of textdoc_typ; 
CREATE TABLE textdoc_ntable ( 
   id number, 
   textdoc_ntab textdoc_typ) 
NESTED TABLE textdoc_ntab STORE AS textdoc_nestedtab;

The actual embedding of the nested table is accomplished when the structure of the containing table is defined. In our example, this is effected by the NESTED TABLE statement when the Print_media table is created.

Inserting a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()

Figure 10-4 Use Case Diagram: Inserting a Row Using EMPTY_CLOB() or EMPTY_BLOB()

Purpose

This procedure describes how to insert a LOB value using EMPTY_CLOB() or EMPTY_BLOB().

Usage Notes

Here are guidelines for inserting LOBs:

Before inserting, Make the LOB Column Non-Null

Before you write data to an internal LOB, make the LOB column non-null; that is, the LOB column must contain a locator that points to an empty or populated LOB value. You can initialize a BLOB column's value by using the function EMPTY_BLOB() as a default predicate. Similarly, a CLOB or NCLOB column's value can be initialized by using the function EMPTY_CLOB().

You can also initialize a LOB column with a character or raw string less than 4,000 bytes in size. For example:

INSERT INTO Print_media (product_id, ad_id, ad_sourcetext)
     VALUES (1, 1, 'This is a One Line Advertisement');

You can perform this initialization during CREATE TABLE (see "Creating a Table Containing One or More LOB Columns") or, as in this case, by means of an INSERT.

Inserting LOBs For Binds of More Than 4,000 Bytes

For guidelines on how to INSERT into a LOB when binds of more than 4,000 bytes are involved, see the following sections in Chapter 7, "Modeling and Design":

• Binds Greater than 4,000 Bytes are Now Allowed For LOB INSERTs and UPDATEs
• Binds of More Than 4,000 Bytes ... No HEX to RAW or RAW to HEX Conversion
• Example: PL/SQL - Using Binds of More Than 4,000 Bytes in INSERT and UPDATE
• Example: PL/SQL - Binds of More Than 4,000 Bytes -- Inserts Not Supported
• Example: PL/SQL - 4,000 Byte Result Limit in Binds of More than 4,000 Bytes

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• SQL: Oracle9i SQL Reference , "Chapter 7, SQL Statements" -- INSERT.
• C (OCI): There is no applicable syntax reference for this use case.
• C++ (OCCI): There is no applicable syntax reference for this use case.
• COBOL (Pro*COBOL): There is no applicable syntax reference for this use case.
• C/C++ (Pro*C/C++): There is no applicable syntax reference for this use case.
• Visual Basic (OO4O): There is no applicable syntax reference for this use case.
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

Examples

Examples are provided in the following programmatic environments:

• SQL: Inserting a Value Using EMPTY_CLOB() / EMPTY_BLOB()
• C (OCI): No example is provided with this release.
• C++ (OCCI): No example is provided with this release.
• C/C++ (ProC/C++): No example is provided with this release.
• COBOL (Pro*COBOL): No example is provided with this release.
• Visual Basic (OO4O): No example is provided with this release.
• Java (JDBC): No example is provided with this release.

SQL: Inserting a Value Using EMPTY_CLOB() / EMPTY_BLOB()

These functions are special functions in Oracle SQL, and are not part of the DBMS_LOB package.

/* In the new row of table Print_media, 
   the columns ad_sourcetext and ad_fltextn are initialized using EMPTY_CLOB(), 
   the columns ad_composite and ad_photo are initialized using EMPTY_BLOB(),
   the column formatted-doc in the nested table is initialized using EMPTY_
BLOB(),
   the column logo in the column object is initialized using EMPTY_BLOB(): */   
INSERT INTO Print_media 
   VALUES (3060,11001, EMPTY_BLOB(), EMPTY_CLOB(),EMPTY_CLOB(),EMPTY_CLOB(), 
   textdoc_tab(textdoc_typ ('HTML', EMPTY_BLOB())), EMPTY_BLOB(), NULL,
   adheader_typ('any header name', <any date>, 'ad header text goes here', 
EMPTY_BLOB()),
   'Press release goes here');

Inserting a Row by Selecting a LOB From Another Table

Figure 10-5 Use Case Diagram: Inserting a Row by Selecting a LOB From Another Table

Purpose

This procedure describes how to insert a row containing a LOB as SELECT.

Usage Notes

For example, assuming Print_media and Online_media have identical schemas. The statement creates a new LOB locator in table Print_media. It also copies the LOB data from Online_media to the location pointed to by the new LOB locator inserted in table Print_media.

Inserting LOBs For Binds of More Than 4,000 Bytes

For guidelines on how to INSERT into a LOB when binds of more than 4,000 bytes are involved, see the following sections in Chapter 7, "Modeling and Design":

• Binds Greater than 4,000 Bytes are Now Allowed For LOB INSERTs and UPDATEs
• Binds of More Than 4,000 Bytes ... No HEX to RAW or RAW to HEX Conversion
• Example: PL/SQL - Using Binds of More Than 4,000 Bytes in INSERT and UPDATE
• Example: PL/SQL - Binds of More Than 4,000 Bytes -- Inserts Not Supported
• Example: PL/SQL - 4,000 Byte Result Limit in Binds of More than 4,000 Bytes

Syntax

Use the following syntax reference:

• SQL: Oracle9i SQL Reference , "Chapter 7, SQL Statements" -- INSERT.

Scenario

For LOBs, one of the advantages of using an object-relational approach is that you can define a type as a common template for related tables. For instance, it makes sense that both the tables that store archival material and working tables that use those libraries, share a common structure.

The following code fragment is based on the fact that the table Online_media is of the same type as Print_media referenced by the ad_textdocs_ntab column of table Print_media. It inserts values into the library table, and then inserts this same data into Print_media by means of a SELECT.

Examples

The following example is provided in SQL and applies to all the programmatic environments:

• SQL: Inserting a Row by Selecting a LOB from Another Table

SQL: Inserting a Row by Selecting a LOB from Another Table

/* Store records in the archive table Online_media: */
INSERT INTO Online_media  
   VALUES (3060, NULL, NULL, NULL, NULL, 
           'some text about this CRT Monitor', NULL);

/* Insert values into Print_media by selecting from Online_media: */
INSERT INTO Print_media (product_id, ad_id, ad_sourcetext)
   (SELECT product_id, 11001, product_text
          FROM Online_media where product_id = 3060);

Inserting a Row by Initializing a LOB Locator Bind Variable

Figure 10-6 Use Case Diagram: Inserting a Row by Initializing a LOB Locator Bind Variable

Purpose

This procedure inserts a row by initializing a LOB locator bind variable.

Usage Notes

For guidelines on how to INSERT and UPDATE a LOB when binds of more than 4,000 bytes are involved, see the following sections in Chapter 7, "Modeling and Design":

• Binds Greater than 4,000 Bytes are Now Allowed For LOB INSERTs and UPDATEs
• Binds of More Than 4,000 Bytes ... No HEX to RAW or RAW to HEX Conversion
• Example: PL/SQL - Using Binds of More Than 4,000 Bytes in INSERT and UPDATE
• Example: PL/SQL - Binds of More Than 4,000 Bytes -- Inserts Not Supported
• Example: PL/SQL - 4,000 Byte Result Limit in Binds of More than 4,000 Bytes

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• SQL:Oracle9i SQL Reference , "Chapter 7, SQL Statements" -- INSERT
• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- INSERT.
• C/C++ (ProC/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- INSERT
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > Objects > Oradynaset
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

In the following examples you use a LOB locator bind variable to take ad_photo data in one row of Print_media and insert it into another row.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Inserting a Row by Initializing a LOB Locator Bind Variable
• C (OCI): Inserting a Row by Initializing a LOB Locator Bind Variable
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Inserting a Row by Initializing a LOB Locator Bind Variable
• C/C++ (ProC/C++): Inserting a Row by Initializing a LOB Locator Bind Variable
• Visual Basic (OO4O): Inserting a Row by Initializing a LOB Locator Bind Variable
• Java (JDBC): Inserting a Row by Initializing a LOB Locator Bind Variable

PL/SQL (DBMS_LOB Package): Inserting a Row by Initializing a LOB Locator Bind Variable

/* Note that the example procedure insertUseBindVariable_proc is not part of the 
   DBMS_LOB package.   */
CREATE OR REPLACE PROCEDURE insertUseBindVariable_proc 
   (productnum IN NUMBER, adnum IN NUMBER, Blob_loc IN BLOB) IS
BEGIN
   INSERT INTO Print_media (product_id, ad_id, ad_photo) 
      VALUES (productnum, adnum, Blob_loc);
END;

DECLARE
   Blob_loc  BLOB;
BEGIN
   /* Select the LOB from the row where product_id = 3106 and ad_id=13001. 
      Initialize the LOB locator bind variable: */
   SELECT ad_photo INTO Blob_loc
      FROM Print_media
      WHERE product_id = 3106 AND ad_id=13001;
  /* Insert into the row where product_id = 2056 AND ad_id=12001 */
  insertUseBindVariable_proc (2056, 12001, Blob_loc);
  COMMIT;
END;

C (OCI): Inserting a Row by Initializing a LOB Locator Bind Variable

/* Select the locator into a locator variable */
sb4 select_Printmedia_Locator (Lob_loc, errhp, stmthp, svchp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCIStmt       *stmthp; 
OCISvcCtx     *svchp;
{
  OCIDefine *defnp1, *defnp2;

  text  *sqlstmt = 
    (text *)"SELECT ad_photo FROM Print_media WHERE product_id=2268 AND ad_
id=21001";

  /* Prepare the SQL statement */     
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                  (ub4)strlen((char *)sqlstmt),
                                  (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

  /* Define the column being selected */ 
  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));
  checkerr (errhp, OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 2,
                                  (dvoid *)&Lob_loc, (sb4)0, 
                                  (ub2)SQLT_BLOB,(dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                  (ub4)OCI_DEFAULT));
 
  /* Execute and fetch one row */
  checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                  (ub4) OCI_DEFAULT));
  return (0);

}
/* Insert the selected Locator into table using Bind Variables.
   This function selects a locator from the Print_media table and inserts
   it into the same table in another row.
 */
void insertUseBindVariable (envhp, errhp, svchp, stmthp)
OCIEnv        *envhp; 
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;
{
  int            clipid;
  OCILobLocator *Lob_loc;
  OCIBind       *bndhp2;
  OCIBind       *bndhp1;

  text          *insstmt = 
   (text *) "INSERT INTO Print_media (product_id, ad_photo) VALUES (:2268, 
:3060)";
   

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

  /* Select a LOB locator from the Print_media table */
  select_Printmedia_Locator(Lob_loc, errhp, stmthp, svchp); 


  /* Insert the locator into the Print_media table with product_id=3060 */
  product_id = 3060;
     
  /* Prepare the SQL statement */
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) 
                                  strlen((char *) insstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

  /* Binds the bind positions */
  checkerr (errhp, OCIBindByPos(stmthp, &bndhp1, errhp, (ub4) 1,
                                (dvoid *) &clipid, (sb4) sizeof(clipid),
                                SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));
  checkerr (errhp, OCIBindByPos(stmthp, &bndhp2, errhp, (ub4) 2,
                                (dvoid *) &Lob_loc, (sb4) 0,  SQLT_BLOB,
                                (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));

  /* Execute the SQL statement */
  checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                  (ub4) OCI_DEFAULT));

  /* Free LOB resources*/
  OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB);

COBOL (Pro*COBOL): Inserting a Row by Initializing a LOB Locator Bind Variable

       IDENTIFICATION DIVISION.
       PROGRAM-ID. INSERT-LOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01 BLOB1 SQL-BLOB.
       01  USERID   PIC X(11) VALUES "PM/PM".
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       INSERT-LOB.
    
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Initialize the BLOB locator
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.

      * Populate the LOB
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL 
              SELECT AD_PHOTO INTO :BLOB1
                 FROM PRINT_MEDIA WHERE PRODUCT_ID = 2268 AND AD_ID = 21001 
END-EXEC.
  
      * Insert the value with PRODUCT_ID of 3060
           EXEC SQL 
              INSERT INTO PRINT_MEDIA (PRODUCT_ID, AD_PHOTO)
                 VALUES (3060, 11001, :BLOB1)END-EXEC.

      * Free resources held by locator
       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BLOB1 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (ProC/C++): Inserting a Row by Initializing a LOB Locator Bind Variable

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
   EXEC SQL ROLLBACK WORK RELEASE;
   exit(1);
}

void insertUseBindVariable_proc(Rownum, Lob_loc)
   int Rownum, Rownum2;
   OCIBlobLocator *Lob_loc;
{
   EXEC SQL WHENEVER SQLERROR DO Sample_Error();
   EXEC SQL INSERT INTO Print_media (product_id, ad_id, ad_photo)
      VALUES (:Rownum, :Rownum2, :Lob_loc);
}
void insertBLOB_proc()
{
   OCIBlobLocator *Lob_loc;

   /* Initialize the BLOB Locator: */
   EXEC SQL ALLOCATE :Lob_loc;

   /* Select the LOB from the row where product_id = 2268 and ad_id=21001: */
   EXEC SQL SELECT ad_photo INTO :Lob_loc
      FROM Print_media WHERE product_id = 2268 AND ad_id = 21001;

   /* Insert into the row where product_id = 3106 and ad_id = 13001: */
   insertUseBindVariable_proc(3106, 13001, Lob_loc);

   /* Release resources held by the locator: */
  EXEC SQL FREE :Lob_loc;
}

void main()
{
   char *samp = "pm/pm";
   EXEC SQL CONNECT :pm;
   insertBLOB_proc();
   EXEC SQL ROLLBACK WORK RELEASE;
}

Visual Basic (OO4O): Inserting a Row by Initializing a LOB Locator Bind Variable

Dim OraDyn as OraDynaset, OraPhoto1 as OraBLOB, OraPhotoClone as OraBLOB
Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Print_media ORDER BY product_id", ORADYN_DEFAULT)
Set OraPhoto1 = OraDyn.Fields("ad_photo").Value
'Clone it for future reference
Set OraPhotoClone = OraPhoto1  

'Go to Next row
OraDyn.MoveNext

'Lets update the current row and set the LOB to OraPhotoClone
OraDyn.Edit
Set OraPhoto1 = OraPhotoClone
OraDyn.Update

Java (JDBC): Inserting a Row by Initializing a LOB Locator Bind Variable

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class Ex2_31
{
  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ());
    // Connect to the database: 
    Connection conn =
       DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
       ResultSet rset = stmt.executeQuery (
          "SELECT ad_photo FROM Print_media WHERE product_id = 3106 AND ad_id = 
13001");
       if (rset.next())
       {
          // retrieve the LOB locator from the ResultSet
          BLOB adphoto_blob = ((OracleResultSet)rset).getBLOB (1);
          OraclePreparedStatement ops = 
          (OraclePreparedStatement) conn.prepareStatement(
             "INSERT INTO Print_media (product_id, ad_id, ad_photo) VALUES 
(2268, 21001, ?)");
          ops.setBlob(1, adphoto_blob);
          ops.execute();
          conn.commit();
          conn.close();
       }
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Loading a LOB with Data From a BFILE

Figure 10-7 Use Case Diagram: Loading a LOB with Data From a BFILE

Purpose

This procedure describes how to load a LOB with data from a BFILE.

Usage Notes

Since LOBs can be quite large in size, it makes sense that SQL*Loader can load LOB data from either the main datafile (that is, in-line with the rest of the data) or from one or more secondary datafiles.

To load LOB data from the main datafile, the usual SQL*Loader formats can be used. LOB data instances can be in predetermined size fields, delimited fields, or length-value pair fields.

For detailed information and tips on using SQL Loader for loading data into an internal LOB see "Loading Inline LOB Data" and "Loading Out-Of-Line LOB Data" in Chapter 4 of this guide.

Binary Data to Character Set Conversion is Needed on BFILE Data

In using OCI, or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another. When you use the DBMS_LOB.LOADFROMFILE procedure to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. No implicit translation is performed from binary data to a character set. For this reason, you should use the LOADCLOBFROMFILE procedure when loading text (see Loading an Internal Persistent CLOB with BFILE Data on).

Specify Amount to be Less than the Size of BFILE

Unless you specify maxlobsize to load the entire BFILE, the amount (the number of bytes) you specify to load from a BFILE must be less than or equal to the size of BFILE as follows:

• DBMS_LOB.LOADFROMFILE: You cannot specify an amount larger than the size of the BFILE.
• OCILobLoadFromFile: You cannot specify an amount larger than the length of the BFILE.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- LOADFROMFILE.
• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OciLobLoadFromFile.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB LOAD, LOB OPEN, LOB CLOSE.
• C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB LOAD
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > CopyFromBFILE and select OO4O Automation Server > OBJECTS > OraDynaset, OraDatabase, OraConnection
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and ReferenceChapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

The examples assume that there is an operating system source file keyboard_3106_13001 that contains LOB data to be loaded into the target LOB ad_composite. The examples also assume that directory object ADVERT_DIR already exists and is mapped to the location of the source file.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Loading a LOB with Data from a BFILE
• C (OCI): Loading a LOB with Data from a BFILE
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Loading a LOB with Data from a BFILE
• C/C++ (ProC/C++): No example is provided with this release.
• Visual Basic (OO4O): Loading a LOB with Data from a BFILE
• Java (JDBC): Loading a LOB with Data from a BFILE

PL/SQL (DBMS_LOB Package): Loading a LOB with Data from a BFILE

/* Loading a LOB with Data from a BFILE. Note that the example procedure 
loadLOBFromBFILE_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc IS
   Dest_loc       BLOB;
   Src_loc        BFILE := BFILENAME('ADPHOTO_DIR', 'keyboard_3106_13001');
   Amount         INTEGER := 4000;
BEGIN
   SELECT ad_photo INTO Dest_loc FROM print_media
      WHERE product_id = 3106 and ad_id=13001 FOR UPDATE;

   /* Opening the source BFILE is mandatory: */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);

   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);

   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE(Dest_loc);
   DBMS_LOB.CLOSE(Src_loc);
   COMMIT;
END;

C (OCI): Loading a LOB with Data from a BFILE

/* Selecting a BLOB from Print_media and loading it with data from a BFILE */
sb4 select_lock_adphoto_locator_3(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text  *sqlstmt = 
     (text *)"SELECT ad_photo FROM Print_media WHERE product_id=2056 
             AND ad_id = 12001 FOR UPDATE";
  OCIDefine *defnp1, *defnp2;
  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)
                     OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 2,
                                  (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 LoadLobDataFromBFile(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *bfile;
  OCILobLocator *blob;
  ub4            amount= 4000;

  /* Allocate the Source (bfile) & destination (blob) locators desriptors*/
  OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&bfile, 
                     (ub4)OCI_DTYPE_FILE, (size_t)0, (dvoid **)0);
  OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&blob,
                     (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0);

  /* Select a ad_photo locator for update */
  printf (" select the ad_photo locator...\n");
  select_lock_adphoto_locator_2056(blob, errhp, svchp, stmthp);
  
  /* Set the Directory Alias and File Name of the ad_photo file */
  printf ("  set the file name in bfile\n");
  checkerr (errhp, OCILobFileSetName(envhp, errhp, &bfile, (text*)"ADPHOTO_DIR",
                                     (ub2)strlen("ADPHOTO_DIR"),
                                     (text*)"mousepad_2056_12001",
                                     (ub2)strlen("mousepad_2056_12001")));
    printf (" open the bfile\n");
  /* Opening the BFILE locator is Mandatory */
  checkerr (errhp, (OCILobOpen(svchp, errhp, bfile, OCI_LOB_READONLY)));

  printf("  open the lob\n");
  /* Opening the BLOB locator is optional */
  checkerr (errhp, (OCILobOpen(svchp, errhp, blob, OCI_LOB_READWRITE)));

  /* Load the data from the graphic file (bfile) into the blob */
  printf (" load the LOB from File\n");
  checkerr (errhp, OCILobLoadFromFile(svchp, errhp, blob, bfile, (ub4)amount,
                                      (ub4)1, (ub4)1));

  /* Closing the LOBs is Mandatory if they have been Opened */
  checkerr (errhp, OCILobClose(svchp, errhp, bfile));
  checkerr (errhp, OCILobClose(svchp, errhp, blob));

  /* Free resources held by the locators*/
  (void) OCIDescriptorFree((dvoid *) bfile, (ub4) OCI_DTYPE_FILE);
  (void) OCIDescriptorFree((dvoid *) blob, (ub4) OCI_DTYPE_LOB);

  return;
}

COBOL (Pro*COBOL): Loading a LOB with Data from a BFILE

       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOB-LOAD.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  DEST           SQL-BLOB.
       01  BFILE1         SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
      * Declare the amount to load.  The value here
      * was chosen arbitrarily
       01  LOB-AMT        PIC S9(9) COMP VALUE 10.
       01  USERID   PIC X(11) VALUES "PM/PM".
           EXEC SQL INCLUDE SQLCA END-EXEC.
       PROCEDURE DIVISION.
       LOB-LOAD.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Allocate and initialize the BFILE locator
           EXEC SQL ALLOCATE :BFILE1 END-EXEC.

      * Set up the directory and file information
           MOVE "ADGRAPHIC_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_3106_13001" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.

           EXEC SQL 
              LOB FILE SET :BFILE1 DIRECTORY = :DIR-ALIAS,FILENAME = :FNAME 
           END-EXEC.

      * Allocate and initialize the destination BLOB
           EXEC SQL ALLOCATE :DEST END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL SELECT AD_GRAPHIC INTO :DEST
           FROM PRINT_MEDIA WHERE PRODUCT_ID = 2268 AND AD_ID = 21001 FOR UPDATE 
END-EXEC.
  
      * Open the source BFILE for READ
           EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.

      * Open the destination BLOB for READ/WRITE 
           EXEC SQL LOB OPEN :DEST READ WRITE END-EXEC.

      * Load the destination BLOB from the source BFILE
           EXEC SQL LOB LOAD :LOB-AMT FROM FILE :BFILE1 INTO :DEST END-EXEC.

      * Close the source and destination LOBs
           EXEC SQL LOB CLOSE :BFILE1 END-EXEC.
           EXEC SQL LOB CLOSE :DEST END-EXEC.
       END-OF-BLOB.
           EXEC SQL FREE :DEST END-EXEC.
           EXEC SQL FREE :BFILE1 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.
       SQL-ERROR.
           EXEC SQL
              WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

Visual Basic (OO4O): Loading a LOB with Data from a BFILE

Dim OraDyn as OraDynaset, OraPhoto1 as OraBLOB, OraMyBfile as OraBFile

OraConnection.BeginTrans
Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT)
Set OraPhoto1 = OraDyn.Fields("ad_photo").Value

OraDb.Parameters.Add "id", 3060,ORAPARAM_INPUT
OraDb.Parameters.Add "mybfile", Null,ORAPARAM_OUTPUT
OraDb.Parameters("mybfile").serverType = ORATYPE_BFILE

OraDb.ExecuteSQL ("begin  GetBFile(:id, :mybfile); end;")

Set OraMyBFile = OraDb.Parameters("mybfile").Value
'Go to Next row
OraDyn.MoveNext

OraDyn.Edit
'Lets update OraPhoto1 data with that from the BFILE
OraPhoto1.CopyFromBFile  OraMyBFile
OraDyn.Update

OraConnection.CommitTrans

Java (JDBC): Loading a LOB with Data from a BFILE

// Java IO classes:
import java.io.InputStream;
import java.io.OutputStream;

// Core JDBC classes:
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes:
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class Ex2_45
{
  public static void main (String args [])
     throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ());
    // Connect to the database: 
    Connection conn =
       DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm");
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();

    try
    {
     BFILE src_lob = null;
     BLOB dest_lob = null;
     InputStream in = null;
     OutputStream out = null;
     byte buf[] = new byte[1000];
     ResultSet rset = null;

       rset = stmt.executeQuery (
          "SELECT BFILENAME('ADPHOTO_DIR', 'keyboard_3106_13001') FROM DUAL");
   if (rset.next())
   {
      src_lob = ((OracleResultSet)rset).getBFILE (1);
      src_lob.openFile();
      in = src_lob.getBinaryStream();
   }

       rset = stmt.executeQuery (
          "SELECT ad_photo FROM Print_media WHERE product_id = 3106 
          AND AD_ID = 13001 FOR UPDATE");
   if (rset.next())
   {
      dest_lob = ((OracleResultSet)rset).getBLOB (1);

      // Fetch the output stream for dest_lob: 
        out = dest_lob.getBinaryOutputStream();
   }

   int length = 0;
   int pos = 0;
   while ((in != null) && (out != null) && ((length = in.read(buf)) != -1)) 
   {
      System.out.println(
         "Pos = " + Integer.toString(pos) + ".  Length = " +
         Integer.toString(length));
      pos += length;
      out.write(buf, pos, length);
   }

   // Close all streams and file handles: 
   in.close();
   out.flush();
   out.close();
   src_lob.closeFile();

   // Commit the transaction: 
   conn.commit();
   conn.close();
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }
}

Loading an Internal Persistent BLOB with Binary Data from a BFILE

Figure 10-8 Use Case Diagram: Loading a BLOB with Data from a BFILE

Purpose

This procedure describes how to load an internal persistent BLOB with binary data from a BFILE.

Usage Notes

Use LOADBLOBFROMFILE for loading of binary data and LOADCLOBFROMFILE for text loading. This achieves the same outcome as LOADFROMFILE as well as returning the new offsets to the user. The LOADCLOBFROMFILE API allows you to specify the character set id of the BFILE ensuring that the character set is properly converted from the BFILE data character set to the destination CLOB/NCLOB character set.

Since LOBs can be quite large in size, it makes sense that SQL*Loader can load LOB data from either the main datafile (that is, in-line with the rest of the data) or from one or more secondary datafiles.

To load LOB data from the main datafile, the usual SQL*Loader formats can be used. LOB data instances can be in predetermined size fields, delimited fields, or length-value pair fields.

For detailed information and tips on using SQL Loader for loading data into an internal LOB see "Loading Inline LOB Data" and "Loading Out-Of-Line LOB Data" in Chapter 4 of this guide.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- LOADBLOBFROMFILE

Scenario

The example procedures in this section use the Print_media table of the Product Media sample schema and assume that there is an operating system source directory that contains the binary LOB data to be loaded into the target BLOB.

Example

The example, "PL/SQL (DBMS_LOB Package): Loading an Internal Persistent BLOB with BFILE Data", illustrates LOADBLOBFROMFILE usage in the PL/SQL programmatic environment. (Other programmatic environments are not supported.)

PL/SQL (DBMS_LOB Package): Loading an Internal Persistent BLOB
with BFILE Data

The following example illustrates:

• How to use LOADBLOBFROMFILE to load the entire file without getting its length first.
• How to use the return value of the offsets to calculate the actual amount loaded.

  DECLARE
    src_loc     BFILE := bfilename('ADVERT_DIR','display_ad_frame') ;
    dst_loc     BLOB;
    src_offset  NUMBER := 1;
    dst_offset  NUMBER := 1;
    src_osin    NUMBER;
    dst_osin    NUMBER;
    bytes_rd    NUMBER;
    bytes_wt    NUMBER; 
  BEGIN
    SELECT ad_composite INTO dst_loc FROM Print_media 
        WHERE product_id=3106 and ad_id=13001 FOR UPDATE;
  
    /* Opening the source BFILE is mandatory */
    dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);
  
    /* Opening the LOB is optional */
    dbms_lob.OPEN(dst_loc, dbms_lob.lob_readwrite);
    /* Save the input source/destination offsets */
    src_osin := src_offset;
    dst_osin := dst_offset;
    /* Use LOBMAXSIZE to indicate loading the entire BFILE */
    dbms_lob.LOADBLOBFROMFILE(dst_loc,src_loc,dbms_lob.lobmaxsize,src_offset,dst_
  offset) ;
  
    /* Closing the LOB is mandatory if you have opened it */
    dbms_lob.close(dst_loc);
    dbms_lob.filecloseall();
    COMMIT;
  
    /* Use the src_offset returned to calculate the actual amount read from the 
  BFILE */
    bytes_rd := src_offset - src_osin;
    dbms_output.put_line(' Number of bytes read from the BFILE ' || bytes_rd ) ;
    /* Use the dst_offset returned to calculate the actual amount written to the 
  BLOB */
    bytes_wt := dst_offset - dst_osin;
    dbms_output.put_line(' Number of bytes written to the BLOB ' || bytes_wt ) ;
    /* If there is no exception the number of bytes read should equal to the 
  number of bytes written */
  
  END ;
  
  

Loading an Internal Persistent CLOB with BFILE Data

Figure 10-9 Use Case Diagram: Loading a CLOB with BFILE Data

Purpose

This procedure describes how to load an internal persistent CLOB or NCLOB with character data from a BFILE.

Usage Notes

Use LOADBLOBFROMFILE for loading of binary data and LOADCLOBFROMFILE for loading of text as the latter method lets you specify the character set id of the BFILE. The LOADCLOBFROMFILE API allows you to specify the character set id of the BFILE ensuring that the character set is properly converted from the BFILE data character set to the destination CLOB/NCLOB character set.

Since LOBs can be quite large in size, it makes sense that SQL*Loader can load LOB data from either the main datafile (that is, in-line with the rest of the data) or from one or more secondary datafiles.

To load LOB data from the main datafile, the usual SQL*Loader formats can be used. LOB data instances can be in predetermined size fields, delimited fields, or length-value pair fields.

For detailed information and tips on using SQL Loader for loading data into an internal LOB see "Loading Inline LOB Data" and "Loading Out-Of-Line LOB Data" in Chapter 4 of this guide.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- LOADCLOBFROMFILE

Scenario

The example procedures in this section use the Print_media table of the Product Media sample schema and assume that there is an operating system source directory that contains the character LOB data to be loaded into the target CLOB or NCLOB.

Examples

The examples given in, PL/SQL (DBMS_LOB Package): Loading an Internal Persistent CLOB with BFILE Data, illustrate LOADCLOBFROMFILE usage in the PL/SQL programmatic environment. (Other programmatic environments are not supported.)

PL/SQL (DBMS_LOB Package): Loading an Internal Persistent CLOB with BFILE Data

Example

The following example illustrates:

• How to use default csid (0).
• How to load the entire file without calling getlength for the BFILE.
• How to find out the actual amount loaded using return offsets.

This example assumes that ad_source is a BFILE in UTF8 character set format and the database character set is UTF8.

DECLARE
  src_loc     bfile := bfilename('ADVERT_DIR','ad_source_1000') ;
  dst_loc     clob  ;
  amt         number := dbms_lob.lobmaxsize;
  src_offset  number := 1 ;
  dst_offset  number := 1 ;
  lang_ctx    number := dbms_lob.default_lang_ctx;
  warning     number;
BEGIN
  select ad_sourcetext into dst_loc from Print_media
     where product_id = 3000 and ad_id = 1000 for update ;
  dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);

  /* The default_csid can be used when the BFILE encoding is in the same charset
   * as the destination CLOB/NCLOB charset
   */
   dbms_lob.LOADCLOBFROMFILE(dst_loc,src_loc, amt, dst_offset, src_offset,                               
       dbms_lob.default_csid, lang_ctx,warning) ;

  commit;

  dbms_output.put_line(' Amount specified ' || amt ) ;
  dbms_output.put_line(' Number of bytes read from source: ' || 
      (src_offset-1));
  dbms_output.put_line(' Number of characters written to destination: ' || 
      (dst_offset-1) );
  if (warning = dbms_lob.warn_inconvertible_char) 
  then
    dbms_output.put_line('Warning: Inconvertible character');
  end if;

  dbms_lob.filecloseall() ;
END ;

Example

The following example illustrates:

• How to get the character set ID from the character set name using the NLS_CHARSET_ID function.
• How to load a stream of data from a single BFILE into different LOBs using the returned offset value and the language context lang_ctx.
• How to read a warning message.

This example assumes that ad_file_ext_01 is a BFILE in JA16TSTSET format and the database national character set is AL16UTF16.

DECLARE
  src_loc     bfile := bfilename('ADVERT_DIR','ad_file_ext_01') ;
  dst_loc1    nclob;
  dst_loc2    nclob;
  amt         number := 1000;
  src_offset  number := 1;
  dst_offset  number := 1;
  src_osin    number;
  cs_id       number := NLS_CHARSET_ID('JA16TSTSET'); /* 998 */
  lang_ctx    number := dbms_lob.default_lang_ctx;
  warning     number;
BEGIN
  dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);
  dbms_output.put_line(' BFILE csid is ' || cs_id) ;

  /* Load the first 1KB of the BFILE into dst_loc1 */
  dbms_output.put_line(' ----------------------------' ) ;
  dbms_output.put_line('   First load  ' ) ;
  dbms_output.put_line(' ----------------------------' ) ;

  SELECT ad_fltextn INTO dst_loc1 FROM Print_media
      WHERE product_id=3106 and ad_id=13000 FOR UPDATE;

  dbms_lob.LOADCLOBFROMFILE(dst_loc1, src_loc, amt, dst_offset, src_offset,
      cs_id, lang_ctx, warning);
  commit;

  /* the number bytes read may or may not be 1k */
  dbms_output.put_line(' Amount specified ' || amt ) ;
  dbms_output.put_line(' Number of bytes read from source: ' || 
      (src_offset-1));
  dbms_output.put_line(' Number of characters written to destination: ' ||
      (dst_offset-1) );
  if (warning = dbms_lob.warn_inconvertible_char) 
  then
    dbms_output.put_line('Warning: Inconvertible character');
  end if;

  /* load the next 1KB of the BFILE into the dst_loc2 */
  dbms_output.put_line(' ----------------------------' ) ;
  dbms_output.put_line('   Second load  ' ) ;
  dbms_output.put_line(' ----------------------------' ) ;

  SELECT ad_fltextn INTO dst_loc2 FROM Print_media
      WHERE product_id=3106 and ad_id=13001 FOR UPDATE;

  /* Notice we are using the src_offset and lang_ctx returned from the previous
   * load. We do not use value 1001 as the src_offset here because sometimes the
   * actual amount read may not be the same as the amount specified.
   */

  src_osin := src_offset;
  dst_offset := 1;
  dbms_lob.LOADCLOBFROMFILE(dst_loc2, src_loc, amt, dst_offset, src_offset,
      cs_id, lang_ctx, warning);
  commit ;
  dbms_output.put_line(' Number of bytes read from source: ' || 
      (src_offset-src_osin) );
  dbms_output.put_line(' Number of characters written to destination: ' || 
      (dst_offset-1) );
  if (warning = dbms_lob.warn_inconvertible_char)
  then
    dbms_output.put_line('Warning: Inconvertible character');
  end if;

  dbms_lob.filecloseall() ;

END ;

Open: Checking If a LOB Is Open

Figure 10-10 Use Case Diagram: Checking If a LOB Is Open

Purpose

This procedure describes how to check if LOB is open.

Usage Notes

Not applicable.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- OPEN, ISOPEN.
• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions -- OciSeeIfLob IsOpen.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB DESCRIBE.
• C/C++ (ProC/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB DESCRIBE ... ISOPEN ...
• Visual Basic (OO4O): There is no applicable syntax reference for this use case.
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

The following "Checking if a LOB is Open" examples open a graphic image (ad_composite), and then evaluate it to see if the LOB is open.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Checking if a LOB is Open
• C (OCI): Checking if a LOB is Open
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Checking if a LOB is Open
• C/C++ (ProC/C++): Checking if a LOB is Open
• Visual Basic (OO4O): No example is provided with this release.
• Java (JDBC): Checking if a LOB is Open

PL/SQL (DBMS_LOB Package): Checking if a LOB is Open

/* Checking if a LOB is Open. Note that the example procedure lobIsOpen_proc 
is not part of the DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE lobIsOpen_proc IS
   Lob_loc     BLOB;
   Retval      INTEGER;
BEGIN
   SELECT ad_composite INTO Lob_loc FROM Print_media 
       WHERE product_id = 3106 AND ad_id = 13001;

   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc , DBMS_LOB.LOB_READONLY);

   /* See if the LOB is open: */
   Retval := DBMS_LOB.ISOPEN(Lob_loc);
  /* The value of Retval will be 1 meaning that the LOB is open. */
END;

C (OCI): Checking if a LOB is Open

/* Checking if LOB is Open. */
/* Select the locator into a locator variable */
sb4 select_adcomp_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text      *sqlstmt = 
    (text *)"SELECT ad_composite FROM Print_media 
               WHERE product_id=2268 AND ad_id = 21001";
  OCIDefine *defnp1 *defnp2;
  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)
                   OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 2,
                                  (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_adcomp_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;
  }

COBOL (Pro*COBOL): Checking if a LOB is Open

     * Checking if LOB is Open
       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOB-OPEN.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  BLOB1          SQL-BLOB.
       01  LOB-ATTR-GRP.
           05 ISOPN       PIC S9(9) COMP.
       01  SRC            SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  USERID   PIC X(11) VALUES "PM/PM".
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       LOB-OPEN.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Allocate and initialize the target BLOB
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL SELECT AD_COMPOSITE INTO :BLOB1
              FROM PRINT_MEDIA WHERE PRODUCT_ID = 3060 AND AD_ID = 11001 
END-EXEC.
  
      * See if the LOB is OPEN
           EXEC SQL 
              LOB DESCRIBE :BLOB1 GET ISOPEN INTO :ISOPN END-EXEC.

           IF ISOPN = 1 
      *       <Processing for the LOB OPEN case>
              DISPLAY "The LOB is open"
           ELSE
      *         <Processing for the LOB NOT OPEN case>
              DISPLAY "The LOB is not open"
           END-IF.

      * Free the resources used by the BLOB
       END-OF-BLOB.
           EXEC SQL FREE :BLOB1 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
              ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (ProC/C++): Checking if a LOB is Open

/* Checking if LOB is open */ 
#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void seeIfLOBIsOpen()
{
  OCIBlobLocator *Lob_loc;
  int isOpen = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT ad_composite INTO :Lob_loc
           FROM Print_media WHERE product_id = 3106 and ad_id = 13001;
  /* See if the LOB is Open: */
  EXEC SQL LOB DESCRIBE :Lob_loc GET ISOPEN INTO :isOpen;
  if (isOpen)
    printf("LOB is open\n");
  else
    printf("LOB is not open\n");
  /* Note that in this example, the LOB is not open */
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp = "pm/pm";
  EXEC SQL CONNECT :pm;
  seeIfLOBIsOpen();  
  EXEC SQL ROLLBACK WORK RELEASE;
}

Java (JDBC): Checking if a LOB is Open

Checking if a CLOB is Open

To see if a CLOB is open, your JDBC application can now use the isOpen method defined in oracle.sql.CLOB. The return boolean value indicates whether the CLOB has been previously opened or not. The isOpen method is defined as follows:

/** 
  * Check whether the CLOB is opened. 
  * @return true if the LOB is opened. 
  */ 
public boolean isOpen () throws SQLException

The usage example is:

CLOB clob = ... 
 // See if the CLOB is opened 
 boolean isOpen = clob.isOpen ();

Checking If a BLOB is Open

To see if a BLOB is open, your JDBC application can now use the isOpen method defined in oracle.sql.BLOB. The return boolean value indicates whether the BLOB has been previously opened or not. The isOpen method is defined as follows:

/** 
 * Check whether the BLOB is opened. 
 * @return true if the LOB is opened. 
 */ 
 public boolean isOpen () throws SQLException

The usage example is:

BLOB blob = ... 
// See if the BLOB is opened 
boolean isOpen = blob.isOpen ();

Example

// Checking if LOB is open
// Core JDBC classes: 
import java.io.OutputStream;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.Types;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class Ex2_48
{
  public Ex2_48 ()
  {
  }

  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    // Connect to the database: 
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm");
    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);
    // Create a Statement: 
    Statement stmt = conn.createStatement ();

    try
    {
      BLOB blob = null;
      ResultSet rset = stmt.executeQuery (
          "SELECT ad_composite FROM Print_media product_id = 3060 AND ad_id = 
11001");
   if (rset.next())
   {
     blob = ((OracleResultSet)rset).getBLOB (1);
   }
       OracleCallableStatement cstmt =
            (OracleCallableStatement) conn.prepareCall (
            "BEGIN ? := DBMS_LOB.ISOPEN(?); END;");
   cstmt.registerOutParameter (1, Types.NUMERIC);
   cstmt.setBLOB(2, blob);
   cstmt.execute();
   int result = cstmt.getInt(1);
   System.out.println("The result is: " + Integer.toString(result));

   OracleCallableStatement cstmt2 = (OracleCallableStatement) 
       conn.prepareCall (
       "BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READONLY); END;");
   cstmt2.setBLOB(1, blob);
   cstmt2.execute();

   System.out.println("The LOB has been opened with a call to DBMS_LOB.OPEN()");

   // Use the existing cstmt handle to re-query the status of the locator: 
   cstmt.setBLOB(2, blob);
   cstmt.execute();
   result = cstmt.getInt(1);
   System.out.println("This result is: " + Integer.toString(result));

   stmt.close();
   cstmt.close();
   cstmt2.close();
   conn.commit();
   conn.close();
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }
}

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

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

Purpose

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

Usage Notes

Syntax

Use the following syntax reference:

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

Scenario

The fields used in the following example are:

CREATE TABLE Print_media ( 
   product_id      NUMBER NOT NULL, 
   ad_id           NUMBER NOT NULL, 
   ad_sourcetext   CLOB default EMPTY_CLOB(), 
   ad_fltextn      NCLOB default EMPTY_CLOB(), 
   ad_graphic      BFILE default NULL, 
   ad_composite    BLOB default EMPTY_BLOB(), 
   ad_photo        BLOB default EMPTY_BLOB(), 
   ad_graphic      BFILE default NULL, 
 ); 

The following example assumes that the column, ad_sourcetext, of table PRINT_MEDIA has been created as follows:

CREATE TABLE Print_media (
    ...
    ad_sourcetext  LONG, 
    ... 
);

To Convert LONG to CLOB, Use ALTER TABLE

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

ALTER TABLE Print_media MODIFY ( ad_sourcetext CLOB );

Any existing application using table Print_media can continue to work with minor modification even after the column ad_sourcetext has been converted 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.

Example

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

• C (OCI): LONG to LOB Migration

C (OCI): LONG to LOB Migration

/* Migrating from LONG to LOB Using LONG-to-LOB API */ 

word buflen, pid, aid;
text buf2[5000];
text *insstmt = (text *)
"INSERT INTO Print_media(product_id, ad_id, ad_sourcetext) VALUES 
(:PID, :AID, :ADSOURCE)";

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

LONG to LOB Copying, Using the TO_LOB Operator

Figure 10-12 Use Case Diagram: Copying LONGs to LOBs Using TO_LOB Operator

Purpose

This procedure describes how to copy a LONG to a LOB using the TO_LOB operator.

Usage Notes

Use of TO_LOB is subject to the following limitations:

• You can use TO_LOB to copy data to a LOB column, but not to a LOB attribute.
• You cannot use TO_LOB with any remote table. Consequently, all the following statements will fail:

  INSERT INTO tb1@dblink (lob_col) SELECT TO_LOB(long_col) FROM tb2; 
  INSERT INTO tb1 (lob_col) SELECT TO_LOB(long_col) FROM tb2@dblink; 
  CREATE table tb1 AS SELECT TO_LOB(long_col) FROM tb2@dblink; 
   
  

• The TO_LOB operator cannot be used in the CREATE TABLE AS SELECT statement to convert a LONG or LONG RAW column to a LOB column when creating an Index Organized Table.

  The workaround is to create the Index Organized Table, and then do an INSERT AS SELECT of the LONG or LONG RAW column using the TO_LOB operator.

• If the target table (the table with the lob column) has a trigger -- such as BEFORE INSERT or INSTEAD OF INSERT -- the :NEW.lob_col variable can't be referenced in the trigger body.
• You cannot deploy TO_LOB inside any PL/SQL block.
• The TO_LOB function can be used to copy data to a CLOB but not a NCLOB. This is because LONG datatypes have the database CHAR character set and can only be converted to a CLOB which also uses the database CHAR character set. NCLOB on the other hand, use the database NCHAR character set.

Syntax

Use the following syntax reference:

• SQL: Oracle9i SQL Reference , Chapter 4, "Functions" -- TO_LOB.

Scenario

Assume that the following archival source table adlibrary_tab was defined and contains data:

Rem  This script is not needed if you use the (newer) 
Rem  ALTER TABLE when migrating LONGs to LOBs
CREATE TABLE adlibrary_tab 
(
   product_id    NUMBER(6), 
   ad_id         NUMBER(6), 
   photos        LONG RAW
);

This example assumes that you want to copy the data from the LONG RAW column (photos) into the BLOB column (ad_photo) of the Print_media table, and uses the SQL function TO_LOB to accomplish this.

Examples

The example is provided in SQL and applies to all programmatic environments:

• "SQL: Copying LONGs to LOBs Using TO_LOB Operator"

SQL: Copying LONGs to LOBs Using TO_LOB Operator

 INSERT INTO Print_media (product_id, ad_id, ad_photo)
  SELECT product_id, TO_LOB(photos)
   FROM adlibrary_tab WHERE product_id =3106;

CREATE TABLE adlibrary_tab (
 product_id     NUMBER,
 ad_audience  LONG RAW);

This functionality is based on using an operator on LONGs called TO_LOB that converts the LONG to a LOB. The TO_LOB operator copies the data in all the rows of the LONG column to the corresponding LOB column, and then lets you apply the LOB functionality to what was previously LONG data. Note that the type of data that is stored in the LONG column must match the type of data stored in the LOB. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data.

Once you have completed this one-time only operation and are satisfied that the data has been copied correctly, you could then drop the LONG column. However, this will not reclaim all the storage originally required to store LONGs in the table. In order to avoid unnecessary, excessive storage, you are better advised to copy the LONG data to a LOB in a new or different table. Once you have made sure that the data has been accurately copied, you should then drop the original table.

One simple way to effect this transposing of LONGs to LOBs is to use the CREATE TABLE... SELECT statement, using the TO_LOB operator on the LONG column as part of the SELECT statement. You can also use INSERT... SELECT.

In the examples in the following procedure, the LONG column named long_col in table Long_tab is copied to a LOB column named lob_col in table Lob_tab. These tables include an id column that contains identification numbers for each row in the table.

Complete the following steps to copy data from a LONG column to a LOB column:

1. Create a new table with the same definition as the table that contains the LONG column, but use a LOB datatype in place of the LONG datatype.

   For example, if you have a table with the following definition:

   
   CREATE TABLE Long_tab (
      id       NUMBER,
      long_col LONG);
   
   

   Create a new table using the following SQL statement:

   
   CREATE TABLE Lob_tab (
      id       NUMBER,
      blob_col BLOB);
   
   

   Note: When you create the new table, make sure you preserve the table's schema, including integrity constraints, triggers, grants, and indexes. The TO_LOB operator only copies data; it does not preserve the table's schema.

2. Issue an INSERT command using the TO_LOB operator to insert the data from the table with the LONG datatype into the table with the LOB datatype.

   For example, issue the following SQL statement:

   
   INSERT INTO Lob_tab
     SELECT id,
     TO_LOB(long_col)
     FROM long_tab;
   
   

3. When you are certain that the copy was successful, drop the table with the LONG column.

   For example, issue the following SQL command to drop the LONG_TAB table:

   DROP TABLE Long_tab;
   
   

4. Create a synonym for the new table using the name of the table with LONG data. The synonym ensures that your database and applications continue to function properly.

   For example, issue the following SQL statement:

   CREATE SYNONYM Long_tab FOR Lob_tab;
   
   

Once the copy is complete, any applications that use the table must be modified to use the LOB data.

You can use the TO_LOB operator to copy the data from the LONG to the LOB in statements that employ CREATE TABLE...AS SELECT or INSERT...SELECT. In the latter case, you must have already ALTERed the table and ADDed the LOB column prior to the UPDATE. If the UPDATE returns an error (because of lack of undo space), you can incrementally migrate LONG data to the LOB using the WHERE clause. The WHERE clause cannot contain functions on the LOB but can test the LOB's nullness.

Checking Out a LOB

Figure 10-13 Use Case Diagram: Checking Out a LOB

Purpose

This procedure describes how to checkout a LOB.

Usage Notes

Streaming Mechanism

The most efficient way to read large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled using polling or callback. Use OCI , OCCI, or PRO*C interfaces with streaming for the underlying read operation. Using DBMS_LOB.READ will result in non-optimal performance.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- OPEN, READ, CLOSE.
• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions -- OCILobOpen, OCILobRead, OCILobClose.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB READ.
• C/C++ (ProC/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB OPEN, LOB READ
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > read, copytofile
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

In the typical use of the checkout-checkin operation, the user wants to checkout a version of the LOB from the database to the client, modify the data on the client without accessing the database, and then checkin all the modifications that were made to the document on the client side. The checkin portion of the scenario is described in "Checking In a LOB".

Examples

The following examples are similar to examples provided in "Displaying LOB Data". Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Checking Out a LOB
• C (OCI): Checking Out a LOB
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Checking Out a LOB
• C/C++ (ProC/C++): Checking Out a LOB
• Visual Basic (OO4O): Checking Out a LOB
• Java (JDBC): Checking Out a LOB

PL/SQL (DBMS_LOB Package): Checking Out a LOB

/* Checking out a LOB. The procedure checkOutLOB_proc used here is not part of 
the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE checkOutLOB_proc IS
    Lob_loc     BLOB;
    Buffer      VARCHAR2(32767);
    Amount      BINARY_INTEGER := 32767;
    Position    INTEGER := 2147483647;
BEGIN
   /* Select the LOB: */
   SELECT Nesttab.formatted_doc INTO Lob_loc
       FROM TABLE(SELECT PMtab.Textdoc_ntab FROM Print_media PMtab
          WHERE PMtab.product_id = 3106 AND PMtab.ad_id = 13001) Nesttab
             WHERE Nesttab.document_typ = 'PDF';
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
   LOOP
      DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
      /* Process the buffer: */
      Position := Position + Amount;
   END LOOP;
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('End of data');
END;

C (OCI): Checking Out a LOB

/* Checking out a lOB -- This example reads the entire contents of a BLOB 
   piecewise into a buffer using a standard polling method, processing each 
   buffer piece after every READ operation until the entire BLOB has been read: 
*/

#define MAXBUFLEN 32767
/* Select the locator into a locator variable: */
sb4 select_formatteddoc_locator(Lob_loc, errhp, stmthp, svchp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text  *sqlstmt = 
     (text *) "SELECT Intab.Transcript \
           FROM TABLE(SELECT pm.ad_textdoc_ntab FROM Print_media pm  \
              WHERE pm.product_id = 3060 AND pm.ad_id = 11001) ntab \
                  WHERE ntab.document_typ = 'PDF'";
 OCIDefine *defnp1, *defnp2;
 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_CLOB,(dvoid *) 0, 
                                 (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)
                  || OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 2,
                                 (dvoid *)&Lob_loc, (sb4)0, 
                                 (ub2) SQLT_CLOB,(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 checkoutLob(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *Lob_loc;
  ub4 amt;
  ub4 offset;
  sword retval;
  boolean done;
  ub1 bufp[MAXBUFLEN];
  ub4 buflen;

  /* Allocate locators desriptors: */
  (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &Lob_loc, 
                            (ub4)OCI_DTYPE_LOB,(size_t) 0, (dvoid **) 0);
  /* Select the BLOB: */
  printf(" select the formatted_doc locator...\n");
  select_formatteddoc_locator(Lob_loc, errhp, stmthp, svchp);


  /* Open the CLOB: */
  printf (" open lob in checkOutLOB_proc\n");
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY)));

  /* Setting amt = 0 will read till the end of LOB: */
  amt = 0;
  buflen = sizeof(bufp);

 /* Process the data in pieces: */
  printf (" read lob in pieces\n");
 offset = 1;
 memset(bufp, '\0', MAXBUFLEN);
 done = FALSE;
 while (!done)
   {
     retval = OCILobRead(svchp, errhp, Lob_loc, &amt, offset, (dvoid *)bufp,
                         buflen, (dvoid *)0,(sb4 (*)(dvoid *, dvoid *, ub4, 
                         ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT);
     switch (retval)
       {
       case OCI_SUCCESS:             /* Only one piece or last piece */
    /* Process the data in bufp. amt will give the amount of data just read in 
       bufp. This is in bytes for BLOBs and in characters for fixed 
       width CLOBS and in bytes for variable width CLOBs */
    done = TRUE;          
    break;
       case OCI_ERROR:
         checkerr (errhp, OCI_ERROR);
    done = TRUE;
    break;
       case OCI_NEED_DATA:           /* There are 2 or more pieces */
    /* Process the data in bufp. amt will give the amount of data just read in 
       bufp. This is in bytes for BLOBs and in characters for fixed 
       width CLOBS and in bytes for variable width CLOBs. */
    break;
       default:
         checkerr (errhp, retval);
    done = TRUE;
       break;
       } /* while */
   }
  /* Closing the CLOB is mandatory if you have opened it: */
  printf (" close lob in checkOutLOB_proc\n");
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));

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

  return;
}

COBOL (Pro*COBOL): Checking Out a LOB

    *  Checking out a LOB
       IDENTIFICATION DIVISION.
       PROGRAM-ID. CHECKOUT.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "PM/PM".
       01  CLOB1          SQL-CLOB.
       01  BUFFER         PIC X(5) VARYING.
       01  AMT            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
       01  D-BUFFER-LEN   PIC 9.
       01  D-AMT          PIC 9.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       READ-CLOB.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
             CONNECT :USERID
           END-EXEC.
      * Allocate and initialize the CLOB locator: 
           EXEC SQL ALLOCATE :CLOB1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC.
           EXEC SQL 
             SELECT AD_SOURCETEXT INTO :CLOB1 FROM PRINT_MEDIA
                WHERE PRODUCT_ID = 3060 AND AD_ID = 11001
           END-EXEC.
 
      * Initiate polling read: 
           MOVE 0 TO AMT.
      * Read first piece of the CLOB into the buffer: 
           EXEC SQL 
             LOB READ :AMT FROM :CLOB1 AT :OFFSET INTO :BUFFER 
           END-EXEC.
           DISPLAY "Reading a CLOB ...".
           DISPLAY " ".
           MOVE BUFFER-LEN TO D-BUFFER-LEN.
           DISPLAY "first read (", D-BUFFER-LEN, "): "
              BUFFER-ARR(1:BUFFER-LEN).
 
      * Read subsequent pieces of the CLOB: 
       READ-LOOP.
           MOVE "     " TO BUFFER-ARR.
           EXEC SQL 
             LOB READ :AMT FROM :CLOB1 INTO :BUFFER 
           END-EXEC.
           MOVE BUFFER-LEN TO D-BUFFER-LEN.
           DISPLAY "next read (", D-BUFFER-LEN, "): "
             BUFFER-ARR(1:BUFFER-LEN).
 
           GO TO READ-LOOP.
 
      * Read the last piece of the CLOB: 
       END-OF-CLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :CLOB1 END-EXEC.
           MOVE BUFFER-LEN TO D-BUFFER-LEN.
           DISPLAY "last read (", D-BUFFER-LEN, "): "
             BUFFER-ARR(1:BUFFER-LEN).
           EXEC SQL
             ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.
        
       SQL-ERROR.
           EXEC SQL
             WHENEVER SQLERROR CONTINUE
           END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
             ROLLBACK WORK RELEASE
          END-EXEC.
          STOP RUN.

C/C++ (ProC/C++): Checking Out a LOB

/* Checking out a LOB - 
   This example reads the entire contents of a CLOB piecewise into a
   buffer using a standard polling method, processing each buffer piece
   after every READ operation until the entire CLOB has been read: */
#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 256

void checkOutLOB_proc()
{
  OCIClobLocator *Lob_loc;
  int Amount;
  int Clip_ID, Segment;
  VARCHAR Buffer[BufferLength];

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
 
  /* Use Dynamic SQL to retrieve the LOB: */
  EXEC SQL PREPARE S FROM
    'SELECT Intab. \
       FROM TABLE(SELECT PMtab.textdoc_ntab FROM Print_media PMtab \
          WHERE PMtab.product_id = :pid AND PMtab.ad_id = :aid) ntab \
             WHERE ntab.document_typ = :seg';
  EXEC SQL DECLARE C CURSOR FOR S;
  Clip_ID = Segment = 1;
  EXEC SQL OPEN C USING :PID, :AID, :Segment;
  EXEC SQL FETCH C INTO :Lob_loc;
  EXEC SQL CLOSE C;
  
  /* Open the LOB: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* Setting Amount = 0 will initiate the polling method: */
  Amount = 0;
  /* Set the maximum size of the Buffer: */
  Buffer.len = BufferLength;
  EXEC SQL WHENEVER NOT FOUND DO break;
  while (TRUE)
    {
      /* Read a piece of the LOB into the Buffer: */
      EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer;
      printf("Checkout %d characters\n", Buffer.len);
    }
  printf("Checkout %d characters\n", Amount);

  /* Closing the LOB is mandatory if you have opened it: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *pm = "pm/pm";
  EXEC SQL CONNECT :pm;
  checkOutLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Visual Basic (OO4O): Checking Out a LOB

'Checking out a lOB
'There are two ways of reading a lob using orablob.read or orablob.copytofile

'Using OraBlob.Read mechanism
Dim OraDyn as OraDynaset, OraPhoto as OraBlob, amount_read%, chunksize%, chunk

chunksize = 32767
set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT)
set OraPhoto = OraDyn.Fields("ad_photo").Value
OraPhoto.PollingAmount = OraPhoto.Size 'Read entire BLOB contents
Do
    amount_read = OraPhoto.Read(chunk,chunksize) 'chunk returned is a variant of 
type byte array
Loop Until OraPhoto.Status <> ORALOB_NEED_DATA

'Using OraBlob.CopyToFile mechanism
Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT)
Set OraPhoto = OraDyn.Fields("ad_photo").Value

'Read entire BLOB contents
OraPhoto.CopyToFile "c:\myphoto.jpg"

Java (JDBC): Checking Out a LOB

// Checking out a LOB
import java.io.InputStream;
import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class Ex2_59
{

  static final int MAXBUFSIZE = 32767;
  public static void main (String args [])
     throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    // Connect to the database: 
    Connection conn =
       DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp");
    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);
    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
       CLOB src_lob = null;
       InputStream in = null;
       byte buf[] = new byte[MAXBUFSIZE];

       ResultSet rset = stmt.executeQuery (
        "SELECT ntab.formatted_doc FROM TABLE( "
         +" SELECT pm.ad_textdoc_ntab FROM Print_media pm "
         +" WHERE pm.product_id=3060 AND ad_id = 11001) ntab
         +" WHERE ntab.document_typ=`html'");
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getCLOB (1);
          in = src_lob.getAsciiStream();
   }

   int length = 0;
   int pos = 0;
   while ((in != null) && ((length = in.read(buf)) != -1)) 
   {
      pos += length;
      System.out.println(Integer.toString(pos));
      // Process the buffer: 
   }
   
   in.close();
   rset.close();
   stmt.close();
   conn.commit();
   conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Checking In a LOB

Figure 10-14 Use Case Diagram: Checking In a LOB

Purpose

This procedure describes how to check in a LOB.

Usage Notes

Streaming Mechanism

The most efficient way to write large amounts of LOB data is to use OCILobWrite() with the streaming mechanism enabled using polling or callback

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- OPEN, WRITE, CLOSE
• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OciLobOpen, OciLobWrite, OciLobClose
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB WRITE.
• C/C++ (ProC/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB WRITE
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > write, and > OBJECTS > Oradynaset > update
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

The checkin operation demonstrated here follows from "Checking Out a LOB". In this case, the procedure writes the data back into the CLOB formatted_doc column within the nested table ad_textdoc_ntab that contains interview segments. As noted earlier, you should use the OCI or PRO*C interface with streaming for the underlying write operation; using DBMS_LOB.WRITE will result in non-optimal performance.

The following examples illustrate how to checkin a LOB using various programmatic environments:

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Checking in a LOB
• C (OCI): Checking in a LOB
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Checking in a LOB
• C/C++ (ProC/C++): Checking in a LOB
• Visual Basic (OO4O): Checking in a LOB
• Java (JDBC): Checking in a LOB

PL/SQL (DBMS_LOB Package): Checking in a LOB

/* Checking in a LOB. The example procedure checkInLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE checkInLOB_proc IS
    Lob_loc        BLOB;
    Buffer         VARCHAR2(32767);
    Amount         BINARY_INTEGER := 32767;
    Position       INTEGER := 2147483647;
    i              INTEGER;
BEGIN
    /* Select the LOB: */
    SELECT ntab.formatted_doc INTO Lob_loc
        FROM TABLE(SELECT PMtab.Textdoc_ntab FROM Print_media PMtab
                   WHERE PMtab.product_id = 3060 AND PMtab.ad_id = 11001) ntab
        WHERE ntab.document_typ = 'pdf' FOR UPDATE;
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE)
    FOR i IN 1..3 LOOP
        /* Fill the Buffer with data to be written. */
        /* Write data: */
        DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer);
        Position := Position + Amount;
    END LOOP;
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

C (OCI): Checking in a LOB

/* Checking in a LOB. This example demonstrates how using OCI you can write
   arbitrary amounts of data to an Internal LOB in either a single piece
   of in multiple pieces using a streaming mechanism that utilizes standard
   polling. A statically allocated Buffer holds the data being
   written to the LOB. */

#define MAXBUFLEN 32767
/* Select the locator into a locator variable */
sb4 select_lock_formateddoc_locator(Lob_loc, errhp, stmthp,svchp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  OCIDefine *defnp1, *defnp2;

  text  *sqlstmt = 
     (text *) "SELECT ntab.formatted_doc \
           FROM TABLE(SELECT pm.ad_textdoc_ntab FROM Print_media pm \
              WHERE pm.product_id = 2268 AND pm.ad_id = 21001) ntab \
                 WHERE ntab.document_typ = 'PDF' FOR UPDATE";

  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_CLOB,(dvoid *) 0, 
                                  (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)
           || OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 2,
                                  (dvoid *)&Lob_loc, (sb4)0, 
                                  (ub2) SQLT_CLOB,(dvoid *) 0, 
                                  (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)
                                  );
  /* Execute and fetch one row */
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                 (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                 (ub4) OCI_DEFAULT));
  
     return OCI_SUCCESS;
}

void checkinLob(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCIClobLocator *Lob_loc;
  ub4 Total = 2.5*MAXBUFLEN;
  ub4 amtp;
  ub4 offset;
  ub4 remainder;
  ub4 nbytes;
  boolean last;
  ub1 bufp[MAXBUFLEN];
  sb4 err;

  /* Allocate locators desriptors*/
  (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &Lob_loc, 
                            (ub4)OCI_DTYPE_LOB,(size_t) 0,(dvoid **) 0);
  /* Select the CLOB */
  printf(" select the formatted_doc locator...\n");
  select_lock_formatteddoc_locator(Lob_loc, errhp, stmthp, svchp);

  /* Open the CLOB */
  printf (" open the locator.\n");
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE)));

  printf (" write the lob in pieces\n");
  if (Total > MAXBUFLEN)
    nbytes = MAXBUFLEN;   /* We will use streaming via standard polling */
  else
    nbytes = Total;                 /* Only a single write is required */
  
  /* Fill the buffer with nbytes worth of data */
  remainder = Total - nbytes;
  /* Setting Amount to 0 streams the data until use specifies OCI_LAST_PIECE */
  amtp = 0;
  /* offset = <Starting position where to begin writing the data>; */
  offset = 1; 
  
  if (0 == remainder)
  {
    amtp = nbytes;
    /* Here, (Total <= MAXBUFLEN ) so we can write in one piece */
      checkerr (errhp, OCILobWrite (svchp, errhp, Lob_loc, amtp, 
                                    offset, bufp, nbytes,
                                    OCI_ONE_PIECE, (dvoid *) 0, 
                                    (sb4 (*)(dvoid *,dvoid *,ub4 *,ub1 *)) 0,
                                    0, SQLCS_IMPLICIT));
  } 
  else
  {
    /* Here (Total > MAXBUFLEN ) so we use streaming via standard polling */
    /* write the first piece.  Specifying first initiates polling. */
    err = OCILobWrite (svchp, errhp, Lob_loc, &amtp, offset, bufp, nbytes,
                       OCI_FIRST_PIECE, (dvoid *) 0, 
                       (sb4 (*)(dvoid *,dvoid *,ub4 *,ub1 *)) 0,
                       0, SQLCS_IMPLICIT);
    if (err != OCI_NEED_DATA)
      checkerr (errhp, err);
    last = FALSE;
    
    /* write the next (interim) and last pieces */
    do 
    {
      if (remainder > MAXBUFLEN)
        nbytes = MAXBUFLEN;     /* Still have more pieces to go */
      else
      {
        nbytes = remainder;     /* Here, (remainder <= MAXBUFLEN) */
        last = TRUE;            /* This is going to be the Final piece */
      }

      /* Fill the buffer with nbytes worth of data */
      if (last)
      {
        /* Specifying LAST terminates polling */
        err = OCILobWrite (svchp, errhp, Lob_loc, &amtp, 
                           offset, bufp, nbytes,
                           OCI_LAST_PIECE, (dvoid *) 0, 
                           (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
                           0, SQLCS_IMPLICIT);
        if (err != OCI_SUCCESS)
          checkerr(errhp, err);
      }
      else
      {
        err = OCILobWrite (svchp, errhp, Lob_loc, &amtp, 
                           offset, bufp, nbytes,
                           OCI_NEXT_PIECE, (dvoid *) 0, 
                           (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
                           0, SQLCS_IMPLICIT);
        if (err != OCI_NEED_DATA)
          checkerr (errhp, err);
      }
      /* Determine how much is left to write */
      remainder = remainder - nbytes;
    } while (!last);
  }
   
  /* At this point, (remainder == 0) */
  
  /* Closing the BLOB is mandatory if you have opened it */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));
  
  /* Free resources held by the locators*/
  (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB);
     
  }

COBOL (Pro*COBOL): Checking in a LOB

     * Checking in a LOB
       IDENTIFICATION DIVISION.
       PROGRAM-ID. CHECKIN.
       ENVIRONMENT DIVISION.
       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
          SELECT INFILE
             ASSIGN TO "datfile.dat"
             ORGANIZATION IS SEQUENTIAL.
       DATA DIVISION.
       FILE SECTION.
 
       FD INFILE
          RECORD CONTAINS 80 CHARACTERS.
       01 INREC      PIC X(80).

       WORKING-STORAGE SECTION.
       01  USERID   PIC X(11) VALUES "P/PM".
       01  CLOB1          SQL-CLOB.M
       01  BUFFER         PIC X(80) VARYING.
       01  AMT            PIC S9(9) COMP VALUE 0.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
       01  END-OF-FILE    PIC X(1) VALUES "N".
       01  D-BUFFER-LEN   PIC 9.
       01  D-AMT          PIC 9.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       WRITE-CLOB.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Allocate and initialize the CLOB locator: 
           EXEC SQL ALLOCATE :CLOB1 END-EXEC.
           EXEC SQL 
                SELECT AD_SOURCETEXT INTO :CLOB1 FROM PRINT_MEDIA
                WHERE PRODUCT_ID = 3060 AND AD_ID = 11001 FOR UPDATE
           END-EXEC.

      * Open the input file for reading: 
           OPEN INPUT INFILE.

      * Either write entire record or write first piece. 
      * Read a data file here and populate BUFFER-ARR and BUFFER-LEN.
      * END-OF-FILE will be set to "Y" when the entire file has been
      * read.
           PERFORM READ-NEXT-RECORD.
           MOVE INREC TO BUFFER-ARR.
           MOVE 80 TO BUFFER-LEN.
           IF (END-OF-FILE = "Y")
              MOVE 80 TO AMT
              EXEC SQL 
                   LOB WRITE ONE :AMT FROM :BUFFER
                   INTO :CLOB1 AT :OFFSET END-EXEC
           ELSE
              DISPLAY "LOB WRITE FIRST"
              DISPLAY  BUFFER-ARR
              MOVE 321 TO AMT
              EXEC SQL 
                 LOB WRITE FIRST :AMT FROM :BUFFER INTO :CLOB1
              END-EXEC
           END-IF.

      * Continue reading from the input data file
      * and writing to the CLOB: 
           PERFORM READ-WRITE 
              UNTIL END-OF-FILE = "Y".
           PERFORM SIGN-OFF.
           STOP RUN.
 
       READ-WRITE.
           PERFORM READ-NEXT-RECORD.
           MOVE INREC TO BUFFER-ARR.
           DISPLAY "READ-WRITE".
           DISPLAY INREC.
           MOVE 80 TO BUFFER-LEN.
           IF END-OF-FILE = "Y"
               DISPLAY "LOB WRITE LAST: ", BUFFER-ARR
               MOVE 1 TO BUFFER-LEN
               EXEC SQL 
                   LOB WRITE LAST :AMT FROM :BUFFER INTO :CLOB1 END-EXEC
           ELSE
               DISPLAY "LOB WRITE NEXT: ", BUFFER-ARR
               MOVE 0 TO AMT
               EXEC SQL 
                   LOB WRITE NEXT :AMT FROM :BUFFER INTO :CLOB1 END-EXEC
           END-IF.

       READ-NEXT-RECORD.
           MOVE SPACES TO INREC.
           READ INFILE NEXT RECORD
                AT END
                 MOVE "Y" TO END-OF-FILE.

       SIGN-OFF.
           CLOSE INFILE.
           EXEC SQL FREE :CLOB1 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (ProC/C++): Checking in a LOB

/* Checking in a LOB. 
   This example shows how you can use Pro*C/C++ to WRITE
   arbitrary amounts of data to an Internal LOB in either a single piece
   or in multiple pieces using a Streaming Mechanism that utilizes standard
   polling. A static Buffer holds the data being written: */

#include <oci.h>
#include <stdio.h>
#include <string.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 512

void checkInLOB_proc(multiple) int multiple;
{
  OCIClobLocator *Lob_loc;
  VARCHAR Buffer[BufferLength];
  unsigned int Total;
  unsigned int Amount;
  unsigned int remainder, nbytes;
  boolean last;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Initialize the Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT ad_sourcetext INTO :Lob_loc
             FROM Print_media WHERE product_id = 3060 AND ad_id = 11001 FOR 
UPDATE;
  /* Open the LOB: */
  EXEC SQL LOB OPEN :Lob_loc READ WRITE;
  Total = Amount = (multiple * BufferLength);
  if (Total > BufferLength)
    nbytes = BufferLength;   /* We will use streaming via standard polling */
  else
    nbytes = Total;                     /* Only a single WRITE is required */
  /* Fill the Buffer with nbytes worth of data: */
  memset((void *)Buffer.arr, 32, nbytes);
  Buffer.len = nbytes;       /* Set the Length */
  remainder = Total - nbytes;
  if (0 == remainder)
    {
      /* Here, (Total <= BufferLength) so we can WRITE in ONE piece: */
      EXEC SQL LOB WRITE ONE :Amount FROM :Buffer INTO :Lob_loc;
      printf("Write ONE Total of %d characters\n", Amount);
    }
  else
    {
      /* Here (Total > BufferLength) so use streaming via standard polling: 
         WRITE the FIRST piece.  Specifying FIRST initiates polling: */
      EXEC SQL LOB WRITE FIRST :Amount FROM :Buffer INTO :Lob_loc;
      printf("Write FIRST %d characters\n", Buffer.len);
      last = FALSE;
      /* WRITE the NEXT (interim) and LAST pieces: */
      do 
        {
          if (remainder > BufferLength)
            nbytes = BufferLength;         /* Still have more pieces to go */
          else
            {
              nbytes = remainder;
              last = TRUE;          /* This is going to be the Final piece */
            }
          /* Fill the Buffer with nbytes worth of data: */
          memset((void *)Buffer.arr, 32, nbytes);
          Buffer.len = nbytes;       /* Set the Length */
          if (last)
            {
              EXEC SQL WHENEVER SQLERROR DO Sample_Error();
              /* Specifying LAST terminates polling: */
              EXEC SQL LOB WRITE LAST :Amount FROM :Buffer INTO :Lob_loc;
              printf("Write LAST Total of %d characters\n", Amount);
            }
          else
            {
              EXEC SQL WHENEVER SQLERROR DO break;
              EXEC SQL LOB WRITE NEXT :Amount FROM :Buffer INTO :Lob_loc;
              printf("Write NEXT %d characters\n", Buffer.len);
            }
          /* Determine how much is left to WRITE: */
          remainder = remainder - nbytes;
        } while (!last);
    }
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* At this point, (Amount == Total), the total amount that was written */
  /* Close the LOB: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  checkInLOB_proc(1);
  EXEC SQL ROLLBACK WORK;
  checkInLOB_proc(4);
  EXEC SQL ROLLBACK WORK RELEASE;
}

Visual Basic (OO4O): Checking in a LOB

'Checking in a LOB
'There are two ways of writing a lob - using  orablob.write or 
orablob.copyfromfile

'Using the OraBlob.Write mechanism
Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim fnum As Integer
Dim OraDyn As OraDynaset, OraPhoto As OraBlob, amount_written%, chunksize%, 
curchunk() As Byte

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "pm/pm", 0&)
chunksize = 500
ReDim curchunk(chunksize)
Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Print_media", ORADYN_DEFAULT)
Set OraPhoto = OraDyn.Fields("ad_photo").Value

fnum = FreeFile
Open "c:\tmp\keyboard_3016_13001.jpg" For Binary As #fnum
OraPhoto.offset = 1
OraPhoto.pollingAmount = LOF(fnum)
remainder = LOF(fnum)

 Dim piece As Byte
 Get #fnum, , curchunk
 OraDyn.Edit
 piece = ORALOB_FIRST_PIECE
 OraPhoto.Write curchunk, chunksize, ORALOB_FIRST_PIECE

 While OraPhoto.Status = ORALOB_NEED_DATA
    remainder = remainder - chunksize
    If remainder <= chunksize Then
        chunksize = remainder
        piece = ORALOB_LAST_PIECE
    Else
        piece = ORALOB_NEXT_PIECE
    End If

    Get #fnum, , curchunk
    OraPhoto.Write curchunk, chunksize, piece
 Wend

OraDyn.Update

'Using the OraBlob.CopyFromFile mechanism
Set OraDyn = OraDb.CreateDynaset("select * from Print_media order by product_
id, ad_id", ORADYN_DEFAULT)
Set OraPhoto = OraDyn.Fields("ad_photo").Value
OraDyn.Edit
OraPhoto.CopyFromFile "c:\tmp\keyboard3016_13001.jpg"
OraDyn.Update

Java (JDBC): Checking in a LOB

// Checking in a LOB
import java.io.InputStream;
import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class Ex2_66
{
  static final int MAXBUFSIZE = 32767;

  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database: 
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
     CLOB lob_loc = null;
     String buf = new String ("Some Text To Write for the Ad");
     ResultSet rset = stmt.executeQuery (
       "SELECT ad_sourcetext FROM Print_media WHERE product_id = 2268 
       AND ad_id = 21001 FOR UPDATE");
   if (rset.next())
   {
     lob_loc = ((OracleResultSet)rset).getCLOB (1);
   }

   long pos = 0;       // Offset within the CLOB where the data is to be written
   long length = 0;    // This is the size of the buffer to be written

   // This loop writes the buffer three times consecutively: 
   for (int i = 0; i < 3; i++)
   {
      pos = lob_loc.length();

      // an alternative is: lob_loc.putString(pos, buf);
      lob_loc.putString(pos, buf);

      // Some debug information: 
      System.out.println(" putString(" + Long.toString(pos) +" buf);");
   }
   stmt.close();
   conn.commit();
   conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Displaying LOB Data

Figure 10-15 Use Case Diagram: Displaying LOB Data

Purpose

This procedure describes how to display LOB data.

Usage Notes

Streaming Mechanism

The most efficient way to read large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- OPEN, READ, CLOSE.
• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OciLobOpen, OciLobRead, OciLobClose.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB READ.
• C/C++ (ProC/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB READ
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Oraclob > METHODS > read, and > OBJECTS > Oraclob > PROPERTIES > offset
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

As an example of displaying a LOB, our scenario stream-reads the image logo from the column object ad_header onto the client-side in order to view the data.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Displaying LOB Data
• C (OCI): Displaying LOB Data
• C++ (OCCI): No example is provided in this release.
• COBOL (Pro*COBOL): Displaying LOB Data
• C/C++ (ProC/C++): Displaying LOB Data
• Visual Basic (OO4O): Displaying LOB Data
• Java (JDBC): Displaying LOB Data

PL/SQL (DBMS_LOB Package): Displaying LOB Data

/* Displaying LOB data.The example procedure displayLOB_proc is not part of the 
DBMS_LOB package: */ 
CREATE OR REPLACE PROCEDURE displayLOB_proc IS 
Lob_loc  BLOB; 
Buffer   RAW(1024); 
Amount   BINARY_INTEGER := 1024; 
Position INTEGER := 1; 
BEGIN 
   /* Select the LOB: */ 
   SELECT pm.ad_header.logo INTO Lob_loc 
      FROM print_media pm WHERE pm.product_id = 3060 AND pm.ad_id = 11001; 
   /* Opening the LOB is optional: */ 
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); 
   LOOP 
      DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer); 
      /* Display the buffer contents: */ 
      DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer)); 
      Position := Position + Amount; 
   END LOOP; 
   /* Closing the LOB is mandatory if you have opened it: */ 
   DBMS_LOB.CLOSE (Lob_loc); 
   EXCEPTION 
      WHEN NO_DATA_FOUND THEN 
         DBMS_OUTPUT.PUT_LINE('End of data'); 
END; 

C (OCI): Displaying LOB Data

/* Displaying LOB data. This example reads the entire contents of a BLOB 
   piecewise into a buffer using the standard polling method, processing 
   each buffer piece after every READ operation until the entire BLOB 
   has been read. */

#define MAXBUFLEN 32767
/* Select the locator into a locator variable */
sb4 select_mapobjectdrawing_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  OCIDefine *defnp1, *defnp2;
  text  *sqlstmt = 
     (text *) "SELECT pm.Adheader_type.logo \
                FROM Print_media pm WHERE pm.product_id = 3060 AND ad_id = 
11001";
  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)
           || OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 2, 
                                  (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 displayLob(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCIBlobLocator *Lob_loc;
  ub4 amt;
  ub4 offset;
  sword retval;
  boolean done;
  ub1 bufp[MAXBUFLEN];
  ub4 buflen;
  OCILobLocator *Lob_Loc;
 
  /* Allocate the Source (bfile) & destination (blob) locators desriptors*/
  (void) OCIDescriptorAlloc((dvoid *) envhp, 
                            (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, 
                            (size_t) 0, (dvoid **) 0);
  /* Select the BLOB */
  printf(" select the adheaderlogo locator...\n");
  select_adheaderlogo_locator(Lob_loc, errhp, svchp, stmthp);

  /* Open the BLOB */
  printf(" open the lob\n");
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY)));

  /* Setting amt = 0 will read till the end of LOB*/
  amt = 0;
  buflen = sizeof(bufp);

 /* Process the data in pieces */
  printf(" Process the data in pieces\n");
  offset = 1;
  memset(bufp, '\0', MAXBUFLEN);
  done = FALSE;
  while (!done)
  {
    retval = OCILobRead(svchp, errhp, Lob_loc, &amt, offset, (dvoid *) bufp,
                        buflen, (dvoid *)0,
                        (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
                        (ub2) 0, (ub1) SQLCS_IMPLICIT);
    switch (retval)
    {
    case OCI_SUCCESS:           /* Only one piece or last piece*/
      /* Process the data in bufp. amt will give the amount of data just read in 
         bufp. This is in bytes for BLOBs and in characters for fixed 
         width CLOBS and in bytes for variable width CLOBs
       */
      done = TRUE;          
      break;
    case OCI_ERROR:
      checkerr (errhp, retval);
      done = TRUE;
      break;
    case OCI_NEED_DATA:         /* There are 2 or more pieces */
      /* Process the data in bufp. amt will give the amount of data just read in 
         bufp. This is in bytes for BLOBs and in characters for fixed 
         width CLOBS and in bytes for variable width CLOBs
       */
      break;
    default:
      checkerr (errhp, retval);
      done = TRUE;
      break;
    }
  } /* while */

  /* Closing the BLOB is mandatory if you have opened it */
   printf(" close the lob \n");
   checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));

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

}

COBOL (Pro*COBOL): Displaying LOB Data

     * DISPLAYING LOB DATA
       IDENTIFICATION DIVISION.
       PROGRAM-ID. DISPLAY-BLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BLOB1          SQL-BLOB.
       01  BUFFER2        PIC X(5) VARYING.
       01  AMT            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
       01  D-AMT          PIC 9.

           EXEC SQL VAR BUFFER2 IS RAW(5) END-EXEC.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       DISPLAY-BLOB.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.
      * Allocate and initialize the BLOB locator: 
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
            EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
             EXEC SQL SELECT PM.AD_PHOTO INTO :BLOB1
              FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 2268 AND AD_ID = 21001 
END-EXEC.
           DISPLAY "Found column AD_PHOTO".
      * Initiate polling read:
           MOVE 0 TO AMT.

           EXEC SQL LOB READ :AMT FROM :BLOB1 AT :OFFSET
              INTO :BUFFER2 END-EXEC.
           DISPLAY " ".
           MOVE AMT TO D-AMT.
           DISPLAY "first read (", D-AMT, "): " BUFFER2.
        READ-BLOB-LOOP.
           MOVE "     " TO BUFFER2.
           EXEC SQL LOB READ :AMT FROM :BLOB1 INTO :BUFFER2 END-EXEC.
            MOVE AMT TO D-AMT.
            DISPLAY "next read (", D-AMT, "): " BUFFER2.
           GO TO READ-BLOB-LOOP.
 
       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BLOB1 END-EXEC.
           MOVE AMT TO D-AMT.
           DISPLAY "last read (", D-AMT, "): " BUFFER2(1:AMT).
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (ProC/C++): Displaying LOB Data

/* Displaying LOB data. This example reads the entire contents of a BLOB 
   piecewise into a buffer using a standard polling method, processing 
   each buffer piece after every READ operation until the entire BLOB 
   has been read: */

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 32767

void displayLOB_proc()
{
  OCIBlobLocator *Lob_loc;
  int Amount;
  struct {
    unsigned short Length;
    char Data[BufferLength];
  } Buffer;
  /* Datatype equivalencing is mandatory for this datatype: */
  EXEC SQL VAR Buffer IS VARRAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  /* Select the BLOB: */
  EXEC SQL SELECT m.ad_header.header_text INTO Lob_loc
           FROM Print_media m WHERE m.product_id = 3060 AND ad_id = 11001;
  /* Open the BLOB: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* Setting Amount = 0 will initiate the polling method: */
  Amount = 0;
  /* Set the maximum size of the Buffer: */
  Buffer.Length = BufferLength;
  EXEC SQL WHENEVER NOT FOUND DO break;
  while (TRUE)
    {
      /* Read a piece of the BLOB into the Buffer: */
      EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer;
      /* Process (Buffer.Length == BufferLength) amount of Buffer.Data */
    }
  /* Process (Buffer.Length == Amount) amount of Buffer.Data */
  /* Closing the BLOB is mandatory if you have opened it: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  displayLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;  
}

Visual Basic (OO4O): Displaying LOB Data

'Displaying LOB data
'Using the OraClob.Read mechanism
Dim MySession As OraSession
Dim OraDb As OraDatabase

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Dim OraDyn as OraDynaset, OraAdSourceText as OraClob, amount_read%, chunksize%, 
chunk
chunksize = 32767
Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Print_media", ORADYN_DEFAULT)
Set OraAdSourceText = OraDyn.Fields("ad_sourcetext").Value
OraAdSourceText.PollingAmount = OraAdSourceText.Size 'Read entire CLOB contents
Do
   'chunk returned is a variant of type byte array: 
    amount_read = OraAdSourceText.Read(chunk, chunksize) 
   'Msgbox chnunk
Loop Until OraAdSourceText.Status <> ORALOB_NEED_DATA

Java (JDBC): Displaying LOB Data

// Core JDBC classes: 
import java.io.OutputStream;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class Ex2_72
{
  static final int MAXBUFSIZE = 32767;
  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database: 
    Connection conn =
       DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
     BLOB lob_loc = null;
     InputStream in = null;
     byte buf[] = new byte[MAXBUFSIZE];
     int pos = 0;
     int length = 0;
     ResultSet rset = stmt.executeQuery (
        "SELECT pm.ad_header.logo FROM Print_media pm 
            WHERE pm.product_id = 2056 AND ad_id = 12001");
   if (rset.next())
   {
     lob_loc = ((OracleResultSet)rset).getBLOB (1);
   }

   // read this LOB through an InputStream: 
   in = lob_loc.getBinaryStream();
   while ((length = in.read(buf)) != -1) 
   {
      pos += length;
      System.out.println(Integer.toString(pos));
      // Process the contents of the buffer here.
   }
      in.close();
   stmt.close();
   conn.commit();
   conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Reading Data from a LOB

Figure 10-16 Use Case Diagram: Reading Data from a LOB

Procedure

This procedure describes how to read data from LOBs.

Usage Notes

Stream Read

The most efficient way to read large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled using polling or callback.

When reading the LOB value, it is not an error to try to read beyond the end of the LOB. This means that you can always specify an input amount of 4 gigabytes - 1 regardless of the starting offset and the amount of data in the LOB. Hence, you do not need to incur a round-trip to the server to call OCILobGetLength() to find out the length of the LOB value to determine the amount to read.

Example

Assume that the length of a LOB is 5,000 bytes and you want to read the entire LOB value starting at offset 1,000. Also assume that you do not know the current length of the LOB value. Here's the OCI read call, excluding the initialization of all parameters:

#define MAX_LOB_SIZE 4294967295 
ub4  amount =  MAX_LOB_SIZE; 
ub4  offset = 1000; 
OCILobRead(svchp, errhp, locp, &amount, offset, bufp, bufl, 0, 0, 0, 0) 

• When using polling mode, be sure to look at the value of the 'amount' parameter after each OCILobRead() call to see how many bytes were read into the buffer since the buffer may not be entirely full.
• When using callbacks, the 'len' parameter, which is input to the callback, will indicate how many bytes are filled in the buffer. Be sure to check the 'len' parameter during your callback processing since the entire buffer may not be filled with data (see the Oracle Call Interface Programmer's Guide.)

Chunksize

A chunk is one or more Oracle blocks. You can specify the chunk size for the LOB when creating the table that contains the LOB. This corresponds to the chunk size used by Oracle when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The getchunksize function returns the amount of space used in the LOB chunk to store the LOB value.

You will improve performance if you execute read requests using a multiple of this chunk size. The reason for this is that you are using the same unit that the Oracle database uses when reading data from disk. If it is appropriate for your application, you should batch reads until you have enough for an entire chunk instead of issuing several LOB read calls that operate on the same LOB chunk.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- OPEN, GETCHUNKSIZE, READ, CLOSE.
• C (OCI): Oracle Call Interface Programmer's Guide Chapter 7, "Chapter 15, "Relational Functions" -- LOB Functions, OciLobOpen, OciLobRead, OciLobClose.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB READ.
• C/C++ (ProC/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB READ
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Oraclob > METHODS > read
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

The examples read data from a single image.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Reading Data from a LOB
• C (OCI): Reading Data from a LOB
• COBOL (Pro*COBOL): Reading Data from a LOB
• COBOL (Pro*COBOL): Reading Data from a LOB
• C (OCI): Reading Data from a LOB
• Visual Basic (OO4O): Reading Data from a LOB
• Java (JDBC): Reading Data from a LOB

PL/SQL (DBMS_LOB Package): Reading Data from a LOB

/* Reading LOB data. The example procedure readLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE readLOB_proc IS
    Lob_loc           BLOB;
    Buffer            RAW(32767);
    Amount            BINARY_INTEGER := 32767;
    Position          INTEGER := 1000;
    Chunksize         INTEGER;
BEGIN
    /* Select the LOB: */
   SELECT ad_composite INTO Lob_loc
      FROM print_media WHERE product_id = 3060 AND ad_id = 11001;
   /* Find out the chunksize for this LOB column: */
   Chunksize := DBMS_LOB.GETCHUNKSIZE(Lob_loc);
   IF (Chunksize < 32767) THEN
      Amount := (32767 / Chunksize) * Chunksize;
   END IF;
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
   /* Read data from the LOB: */
   DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
END;

C (OCI): Reading Data from a LOB

/* Reading LOB data. This example reads the entire contents of a BLOB
   piecewise into a buffer using a standard polling method, processing
   each buffer piece after every READ operation until the entire BLOB 
   has been read. */
#define MAXBUFLEN 1000

/* 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;     
{
  OCIDefine *defnp1;

  text  *sqlstmt = 
     (text *) "SELECT ad_composite \
                FROM Print_media pm \
                WHERE pm.product_id = 2268";

  printf("  prepare statement in select_adcomposite_locator\n");
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                  (ub4)strlen((char *)sqlstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
    printf("  OCIDefineByPos in select_adcomposite_locator\n");
  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 */
   printf("  OCIStmtExecute in select_adcomposite_locator\n");
   checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                  (ub4) OCI_DEFAULT));
       return 0;
}

void readLOB_proc(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  ub4 amt;
  ub4 offset;
  sword retval;
  ub1 bufp[MAXBUFLEN];
  ub4 buflen;
  boolean done;

  OCILobLocator *Lob_loc;
  OCILobLocator *blob;
 
  /* Allocate the Source (bfile) & destination (blob) locators descriptors*/
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, 
                            (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);
  /* Select the BLOB */
  printf(" call select_ad4read_locator\n");
  select_adcomposite_locator(Lob_loc, errhp, svchp, stmthp);

  /* Open the BLOB */
  printf(" call OCILobOpen\n");
  checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY));

  /* Setting the amt to the buffer length.  Note here that amt is in bytes 
     since we are using a BLOB  */
  amt = 0; 
  buflen = sizeof(bufp);

 /* Process the data in pieces */
  printf(" process the data in pieces\n");
  offset = 1;
  memset(bufp, '\0', MAXBUFLEN);
  done = FALSE;

  while (!done)
  {
  retval = OCILobRead(svchp, errhp, Lob_loc, &amt, offset, (dvoid *) bufp,
                      buflen, (dvoid *)0,
                      (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
                      (ub2) 0, (ub1) SQLCS_IMPLICIT);
  switch (retval)
    {
    case OCI_SUCCESS:             /* Only one piece  since amtp == bufp */
      /* Process the data in bufp. amt will give the amount of data just read in 
         bufp. This is in bytes for BLOBs and in characters for fixed 
         width CLOBS and in bytes for variable width CLOBs */
      printf("[%.*s]\n", buflen, bufp);
      done = TRUE;
     break;
    case OCI_ERROR:
      /*   report_error();        this function is not shown here */
      done = TRUE;
      break;
    case OCI_NEED_DATA:
      printf("[%.*s]\n", buflen, bufp);
      break;
    default:
      (void) printf("Unexpected ERROR: OCILobRead() LOB.\n");
       done = TRUE;
       break;
    } 
  }
  /* Closing the BLOB is mandatory if you have opened it */
     checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));

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

COBOL (Pro*COBOL): Reading Data from a LOB

     * READING LOB DATA
       IDENTIFICATION DIVISION.
       PROGRAM-ID. ONE-READ-BLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  BLOB1          SQL-BLOB.
       01  BUFFER2        PIC X(32767) VARYING.
       01  AMT            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
       01  USERID   PIC X(11) VALUES "PM/PM".
           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC SQL VAR BUFFER2 IS LONG RAW(32767) END-EXEC.
       PROCEDURE DIVISION.
       ONE-READ-BLOB.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
              CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the CLOB locator: 
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
            EXEC SQL 
              SELECT AD_COMPOSITE INTO :BLOB1
              FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 2268
              AND AD_ID = 21001 END-EXEC.
           EXEC SQL LOB OPEN :BLOB1 END-EXEC.
 
      * Perform a single read: 
           MOVE 32767 TO AMT.
           EXEC SQL 
              LOB READ :AMT FROM :BLOB1 INTO :BUFFER2 END-EXEC.
           EXEC SQL LOB CLOSE :BLOB1 END-EXEC.

       END-OF-BLOB.
           DISPLAY "BUFFER2: ", BUFFER2(1:AMT).
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BLOB1 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (Pro*C/C++): Reading Data from a LOB

/* Reading LOB data
#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 32767

void readLOB_proc()
{
  OCIBlobLocator *Lob_loc;
  int Amount = BufferLength;
  /* Here (Amount == BufferLength) so only one READ is needed: */
  char Buffer[BufferLength];
  /* Datatype equivalencing is mandatory for this datatype: */
  EXEC SQL VAR Buffer IS RAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT ad_composite INTO :Lob_loc
           FROM Print_media WHERE product_id = 3060 AND ad_id = 11001;
  /* Open the BLOB: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  /* Read the BLOB data into the Buffer: */
  EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer;
  printf("Read %d bytes\n", Amount);
  /* Close the BLOB: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp = "pm/pm";
  EXEC SQL CONNECT :pm;
  readLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Visual Basic (OO4O): Reading Data from a LOB

'Reading LOB data using the OraClob.Read mechanism
Dim MySession As OraSession
Dim OraDb As OraDatabase

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Dim OraDyn as OraDynaset, OraAdSourceText as OraClob, amount_read%, chunksize%, 
chunk

chunksize = 32767
Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Print_media", ORADYN_DEFAULT)
Set OraAdSourceText = OraDyn.Fields("ad_sourcetext").Value
OraAdSourceText.pollingAmount = OraAdSourceText.Size 
'Read entire CLOB contents
Do
amount_read = OraAdSourceText.Read(chunk,chunksize) 
'chunk returned is a variant of type byte array
Loop Until OraAdSourceText.Status <> ORALOB_NEED_DATA

Java (JDBC): Reading Data from a LOB

// Reading LOB data
// Java IO classes: 
import java.io.InputStream;
import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class Ex2_79
{
  static final int MAXBUFSIZE = 32767;
  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database: 
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
     BLOB lob_loc = null;
     byte buf[] = new byte[MAXBUFSIZE];
     ResultSet rset = stmt.executeQuery (
          "SELECT ad_composite FROM Print_media WHERE product_id = 2056 AND ad_
id = 12001");
     if (rset.next())
     {
      lob_loc = ((OracleResultSet)rset).getBLOB (1);
     }
     // MAXBUFSIZE is the number of bytes to read and 1000 is the offset from 
     // which to start reading
     buf = lob_loc.getBytes(1000, MAXBUFSIZE);

     // Display the contents of the buffer here: 
     System.out.println(new String(buf));
     stmt.close();
     conn.commit();
    conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Reading a Portion of the LOB (substr)

Figure 10-17 Use Case Diagram: Reading a Portion of the LOB (substr)

Purpose

This procedure describes how to read portion of the LOB (substring).

Usage Notes

Not applicable.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- SUBSTR, OPEN, CLOSE
• C (OCI): There is no applicable syntax reference for this use case.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- ALLOCATE, LOB OPEN, LOB READ, LOB CLOSE.
• C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB READ. See PL/SQL DBMS_LOB.SUBSTR.
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > PROPERTIES > offset, chunksize, and >OBJECTS > Oraclob > METHODS > read
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

This example demonstrates reading a portion from image ad_photo.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Reading a Portion of the LOB (substr)
• C (OCI): No example is provided with this release.
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Reading a Portion of the LOB (substr)
• C/C++ (Pro*C/C++): Reading a Portion of the LOB (substr)
• Visual Basic (OO4O): Reading a Portion of the LOB (substr)
• Java (JDBC): Reading a Portion of the LOB (substr)

PL/SQL (DBMS_LOB Package): Reading a Portion of the LOB (substr)

/* Reading portion of the LOB data using substr. 
   Example procedure substringLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE substringLOB_proc IS
    Lob_loc           BLOB;
    Amount            BINARY_INTEGER := 32767;
    Position          INTEGER := 1024;
    Buffer            RAW(32767);
BEGIN
    /* Select the LOB: */
    SELECT ad_photo INTO Lob_loc FROM Print_media
       WHERE product_id = 3060 AND ad_id = 11001;
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
    Buffer := DBMS_LOB.SUBSTR(Lob_loc, Amount, Position);
    /* Process the data */
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);
END;

/* In the following SQL statement, 255 is the amount to read 
   and 1 is the starting offset from which to read: */
SELECT DBMS_LOB.SUBSTR(ad_photo, 255, 1) FROM Print_media WHERE product_id = 
3060 AND ad_id = 11001;

COBOL (Pro*COBOL): Reading a Portion of the LOB (substr)

     * READING PORTION OF THE LOB DATA USING SUBSTR
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BLOB-SUBSTR.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  BLOB1          SQL-BLOB.
       01  BUFFER2        PIC X(32767) VARYING.
       01  AMT            PIC S9(9) COMP.
       01  POS            PIC S9(9) COMP VALUE 1.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC SQL VAR BUFFER2 IS VARRAW(32767) END-EXEC.

       PROCEDURE DIVISION.
       BLOB-SUBSTR.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
              CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the CLOB locator: 
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
 
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
             EXEC SQL 
              SELECT AD_COMPOSITE INTO :BLOB1
              FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 2268
              AND AD_ID = 21001 END-EXEC.
           DISPLAY "Selected the BLOB".

      * Open the BLOB for READ ONLY: 
           EXEC SQL LOB OPEN :BLOB1 READ ONLY END-EXEC.

      * Execute PL/SQL to get SUBSTR functionality: 
           MOVE 5 TO AMT.
           EXEC SQL EXECUTE
             BEGIN 
               :BUFFER2 := DBMS_LOB.SUBSTR(:BLOB1,:AMT,:POS); END; END-EXEC.
           EXEC SQL LOB CLOSE :BLOB1 END-EXEC.
           DISPLAY "Substr: ", BUFFER2-ARR(POS:AMT).

       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BLOB1 END-EXEC. 
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (Pro*C/C++): Reading a Portion of the LOB (substr)

/* Reading portion of the LOB using (substr). Pro*C/C++ lacks an equivalent 
   embedded SQL form for the DBMS_LOB.SUBSTR() function.  
   However, Pro*C/C++ can interoperate with PL/SQL using anonymous
   PL/SQL blocks embedded in a Pro*C/C++ program as this example shows: */

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>
void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 32767

void substringLOB_proc()
{
  OCIBlobLocator *Lob_loc;
  int Position = 1;
  int Amount = BufferLength;
  struct {
    unsigned short Length;
    char Data[BufferLength];
  } Buffer;
  /* Datatype equivalencing is mandatory for this datatype: */
  EXEC SQL VAR Buffer IS VARRAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT ad_photo INTO Lob_loc
           FROM Print_media WHERE product_id = 3060 AND ad_id = 11001;
  /* Open the BLOB: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* Invoke SUBSTR() from within an anonymous PL/SQL block: */
  EXEC SQL EXECUTE
    BEGIN
      :Buffer := DBMS_LOB.SUBSTR(:Lob_loc, :Amount, :Position);
    END;
  END-EXEC;
  /* Close the BLOB: */
  EXEC SQL LOB CLOSE :Lob_loc;
  /* Process the Data */
  /* Release resources used by the locator: */
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  substringLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(0);
}

Visual Basic (OO4O): Reading a Portion of the LOB (substr)

'Reading portion of a LOB (or BFILE). In OO4O this is accomplished by 
'setting the OraBlob.Offset and OraBlob.chunksize properties.
'Using the OraClob.Read mechanism
Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraDyn as OraDynaset, OraAdSourceText as OraClob, amount_read%, chunksize%, 
chunk

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)

Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Print_media", ORADYN_DEFAULT)
Set OraAdSourceText = OraDyn.Fields("ad_sourcetext").Value

'Let's read 100 bytes from the 500th byte onwards: 
OraAdSourceText.Offset = 500
OraAdSourceText.PollingAmount = OraAdSourceText.Size 'Read entire CLOB contents
amount_read = OraAdSourceText.Read(chunk, 100) 
'chunk returned is a variant of type byte array

Java (JDBC): Reading a Portion of the LOB (substr)

// Reading portion of a LOB using substr
import java.io.InputStream;
import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class Ex2_79
{

  static final int MAXBUFSIZE = 32767;
  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database: 
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
       BLOB lob_loc = null;
       byte buf[] = new byte[MAXBUFSIZE];

       ResultSet rset = stmt.executeQuery (
          "SELECT ad_composite FROM Print_media 
           WHERE product_id = 3106 AND ad_id = 13001");
       if (rset.next())
       {
          lob_loc = ((OracleResultSet)rset).getBLOB (1);
       }

       OracleCallableStatement cstmt = (OracleCallableStatement)
          conn.prepareCall ("BEGIN DBMS_LOB.OPEN(?, "
                            +"DBMS_LOB.LOB_READONLY); END;");
       cstmt.setBLOB(1, lob_loc);
       cstmt.execute();

       // MAXBUFSIZE is the number of bytes to read and 1000 is the offset from  
       // which to start reading: 
       buf = lob_loc.getBytes(1000, MAXBUFSIZE);
      // Display the contents of the buffer here.
   
      cstmt = (OracleCallableStatement) 
         conn.prepareCall ("BEGIN DBMS_LOB.CLOSE(?); END;");
      cstmt.setBLOB(1, lob_loc);
      cstmt.execute();

      stmt.close();
      cstmt.close();
     conn.commit();
     conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Comparing All or Part of Two LOBs

Figure 10-18 Use Case Diagram: Comparing All or Part of Two LOBs

Purpose

This procedure describes how to compare all or part of two LOBs.

Usage Notes

Not applicable.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- COMPARE.
• C (OCI): There is no applicable syntax reference for this use case.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide or information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- EXECUTE. Also reference PL/SQL DBMS_LOB.COMPARE.
• C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- EXECUTE. Also reference PL/SQL DBMS_LOB.COMPARE.
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Oradynaset > METHODS > movenext
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

The following examples compare two images from the table Print_media to see whether they are different.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Comparing All or Part of Two LOBs
• C (OCI): No example is provided with this release.
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Comparing All or Part of Two LOBs
• C/C++ (Pro*C/C++): Comparing All or Part of Two LOBs
• Visual Basic (OO4O): Comparing All or Part of Two LOBs
• Java (JDBC): Comparing All or Part of Two LOBs

PL/SQL (DBMS_LOB Package): Comparing All or Part of Two LOBs

/* Comparing all or part of two LOBs. The example procedure compareTwoLOBs_proc 
   is not part of the DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE compareTwoLOBs_proc IS
    Lob_loc1            BLOB;
    Lob_loc2            BLOB;
    Amount              INTEGER := 32767;
    Retval              INTEGER;
BEGIN
    /* Select the LOB: */
    SELECT ad_composite INTO Lob_loc1 FROM Print_media
        WHERE product_id = 3060 AND ad_id = 11001;
    SELECT ad_composite INTO Lob_loc2 FROM Print_media
        WHERE product_id = 2056 AND ad_id = 12001;
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc1, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.OPEN (Lob_loc2, DBMS_LOB.LOB_READONLY);
    /* Compare the two frames: */
    retval := DBMS_LOB.COMPARE(Lob_loc1, Lob_loc2, Amount, 1, 1);
    IF retval = 0 THEN
       DBMS_OUTPUT.PUT_LINE('Processing for equal frames');
    ELSE
       DBMS_OUTPUT.PUT_LINE('Processing for non-equal frames');
    END IF;
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc1);
    DBMS_LOB.CLOSE (Lob_loc2);
END;

COBOL (Pro*COBOL): Comparing All or Part of Two LOBs

      * COMPARING ALL OR PART OF TWO LOBS
       IDENTIFICATION DIVISION.
       PROGRAM-ID. COMPARE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BLOB1          SQL-BLOB.
       01  BLOB2          SQL-BLOB.
       01  BUFFER2        PIC X(32767) VARYING.
       01  RET            PIC S9(9) COMP.
       01  AMT            PIC S9(9) COMP.
       01  POS            PIC S9(9) COMP VALUE 1024.
       01  OFFSET         PIC S9(9) COMP VALUE 1.

           EXEC SQL VAR BUFFER2 IS VARRAW(32767) END-EXEC.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       COMPARE-BLOB.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.
      * Allocate and initialize the BLOB locators: 
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
           EXEC SQL ALLOCATE :BLOB2 END-EXEC.
            EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
             EXEC SQL 
                SELECT AD_COMPOSITE INTO :BLOB1
                FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 2268  AND AD_ID = 
21001
             END-EXEC.
            EXEC SQL 
                SELECT AD_COMPOSITE INTO :BLOB2
                FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 3060 AND AD_ID = 11001
                END-EXEC.
   
      * Open the BLOBs for READ ONLY: 
           EXEC SQL LOB OPEN :BLOB1 READ ONLY END-EXEC.
           EXEC SQL LOB OPEN :BLOB2 READ ONLY END-EXEC.

      * Execute PL/SQL to get COMPARE functionality: 
           MOVE 4 TO AMT.
           EXEC SQL EXECUTE
             BEGIN 
               :RET := DBMS_LOB.COMPARE(:BLOB1,:BLOB2,:AMT,1,1); END; END-EXEC.
           
           IF RET = 0
      *        Logic for equal BLOBs goes here
               DISPLAY "BLOBs are equal"
           ELSE
      *        Logic for unequal BLOBs goes here
               DISPLAY "BLOBs are not equal"
           END-IF.
           EXEC SQL LOB CLOSE :BLOB1 END-EXEC.
           EXEC SQL LOB CLOSE :BLOB2 END-EXEC.

       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BLOB1 END-EXEC.
           EXEC SQL FREE :BLOB2 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (Pro*C/C++): Comparing All or Part of Two LOBs

/* Comparing all or part of two LOBs
#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void compareTwoLobs_proc()
{
  OCIBlobLocator *Lob_loc1, *Lob_loc2;
  int Amount = 32767;
  int Retval;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate the LOB locators: */
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL ALLOCATE :Lob_loc2;
  /* Select the LOBs: */
  EXEC SQL SELECT ad_composite INTO :Lob_loc1
     FROM Print_media WHERE product_id = 3060 AND ad_id = 11001;
  EXEC SQL SELECT ad_composite INTO :Lob_loc2
     FROM Print_media WHERE product_id = 2056 AND ad_id = 12001;
  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Lob_loc1 READ ONLY;
  EXEC SQL LOB OPEN :Lob_loc2 READ ONLY;
  /* Compare the two Frames using DBMS_LOB.COMPARE() from within PL/SQL: */
  EXEC SQL EXECUTE
     BEGIN
        :Retval := DBMS_LOB.COMPARE(:Lob_loc1, :Lob_loc2, :Amount, 1, 1);
     END;
  END-EXEC;
  if (0 == Retval)
     printf("The frames are equal\n");
  else
     printf("The frames are not equal\n");
  /* Closing the LOBs is mandatory if you have opened them: */
  EXEC SQL LOB CLOSE :Lob_loc1;
  EXEC SQL LOB CLOSE :Lob_loc2;
  /* Release resources held by the locators: */
  EXEC SQL FREE :Lob_loc1;
  EXEC SQL FREE :Lob_loc2;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  compareTwoLobs_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Visual Basic (OO4O): Comparing All or Part of Two LOBs

'Comparing all or part of two LOBs
Dim MySession As OraSession
Dim OraDb As OraDatabase

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Dim OraDyn as OraDynaset, OraAdPhoto1 as OraBLOB, OraAdPhotoClone as OraBLOB

Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT)
Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value
'Clone it for future reference
Set OraAdPhotoClone = OraAdPhoto1.Clone

'Lets go to the next row and compare LOBs
OraDyn.MoveNext

MsgBox CBool(OraAdPhotot1.Compare(OraAdPhototClone, OraAdPhotoClone.size, 1, 1))

Java (JDBC): Comparing All or Part of Two LOBs

// Comparing all or part of two LOBs
import java.io.InputStream;
import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Types;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class Ex2_87
{
  static final int MAXBUFSIZE = 32767;
  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database: 
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
     BLOB lob_loc1 = null;
     BLOB lob_loc2 = null;
       ResultSet rset = stmt.executeQuery (
          "SELECT ad_composite FROM Print_media 
           WHERE product_id = 2056 AND ad_id = 12001");
   if (rset.next())
   {
     lob_loc1 = ((OracleResultSet)rset).getBLOB (1);
   }

       rset = stmt.executeQuery (
          "SELECT ad_composite FROM Print_media 
           WHERE product_id = 3106 AND ad_id = 13001");
   if (rset.next())
   {
     lob_loc2 = ((OracleResultSet)rset).getBLOB (1);
   }

   if (lob_loc1.length() > lob_loc2.length()) 
     System.out.println ("Looking for LOB2 inside LOB1. result = " 
       + Long.toString(lob_loc1.position(lob_loc2, 1)));
   else
     System.out.println("Looking for LOB1 inside LOB2.  result = " 
     + Long.toString(lob_loc2.position(lob_loc1, 1)));

   stmt.close();
   conn.commit();
   conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Patterns: Checking for Patterns in the LOB (instr)

Figure 10-19 Use Case Diagram: Checking for Pattern in the LOB (instr)

Purpose

This procedure describes how to see if a pattern exists in the LOB (instr).

Usage Notes

Not applicable.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- INSTR.
• C (OCI): There is no applicable syntax reference for this use case.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- EXECUTE. Also reference PL/SQL DBMS_LOB.INSTR.
• C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- EXECUTE. Also reference PL/SQL DBMS_LOB.INSTR.
• Visual Basic (OO4O): There is no applicable syntax reference for this use case.
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

The examples examine the advertisement text in the ad_sourcetext column to see if the string "children" is present.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Checking for Pattern in the LOB (instr)
• C (OCI): No example is provided with this release.
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Checking for Patterns in the LOB (instr)
• C/C++ (Pro*C/C++): Checking for Patterns in the LOB (instr)
• Visual Basic (OO4O): No example is provided with this release.
• Java (JDBC): Checking for Patterns in the LOB (instr)

PL/SQL (DBMS_LOB Package): Checking for Pattern in the LOB (instr)

/* Seeing if a pattern exists in the LOB using instr. 
   The example procedure instringLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE instringLOB_proc IS
   Lob_loc        CLOB;
   Pattern        VARCHAR2(30) := 'children';
   Position       INTEGER := 0;
   Offset         INTEGER := 1;
   Occurrence     INTEGER := 1;
BEGIN
   /* Select the LOB: */
   SELECT ad_sourcetext INTO Lob_loc
      FROM Print_media WHERE product_id = 2268 AND ad_id = 21001;
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
   /* Seek for the pattern: */
   Position := DBMS_LOB.INSTR(Lob_loc, Pattern, Offset, Occurrence);
   IF Position = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Pattern not found');
   ELSE
      DBMS_OUTPUT.PUT_LINE('The pattern occurs at '|| position);
   END IF;
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
END;

COBOL (Pro*COBOL): Checking for Patterns in the LOB (instr)

     * SEEING IF A PATTERN EXISTS IN THE LOB USING INSTR
       IDENTIFICATION DIVISION.
       PROGRAM-ID. CLOB-INSTR.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  CLOB1          SQL-CLOB.
       01  PATTERN        PIC X(8) VALUE "children".
       01  POS            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
       01  OCCURRENCE     PIC S9(9) COMP VALUE 1.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       CLOB-INSTR.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the CLOB locator: 
           EXEC SQL ALLOCATE :CLOB1 END-EXEC.
            EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC.
             EXEC SQL SELECT AD_SOURCETEXT INTO :CLOB1
                FROM PRINT_MEDIA 
                WHERE PRODUCT_ID = 2268 AND AD_ID = 21001 END-EXEC.
 
      * Open the CLOB for READ ONLY: 
           EXEC SQL LOB OPEN :CLOB1 READ ONLY END-EXEC.

      * Execute PL/SQL to get INSTR functionality:
           EXEC SQL EXECUTE
             BEGIN 
               :POS := DBMS_LOB.INSTR(:CLOB1, :PATTERN,:OFFSET,:OCCURRENCE);
               END; END-EXEC.
           
           IF POS = 0
      *        Logic for pattern not found here
               DISPLAY "Pattern not found."
           ELSE
      *        Pos contains position where pattern is found
               DISPLAY "Pattern found."
           END-IF.
           EXEC SQL LOB CLOSE :CLOB1 END-EXEC.

       END-OF-CLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :CLOB1 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (Pro*C/C++): Checking for Patterns in the LOB (instr)

/* Seeing if a pattern exists in the LOB using instr
#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void instringLOB_proc()
{
  OCIClobLocator *Lob_loc;
  char *Pattern = "The End";
  int Position = 0;
  int Offset = 1;
  int Occurrence = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT ad_sourcetext INTO :Lob_loc
           FROM Print_media WHERE product_id = 3060 AND ad_id = 11001;
  /* Opening the LOB is Optional: */
  EXEC SQL LOB OPEN :Lob_loc;
  /* Seek the Pattern using DBMS_LOB.INSTR() in a PL/SQL block: */
  EXEC SQL EXECUTE
    BEGIN
      :Position := DBMS_LOB.INSTR(:Lob_loc, :Pattern, :Offset, :Occurrence);
    END;
  END-EXEC;
  if (0 == Position)
    printf("Pattern not found\n");
  else
    printf("The pattern occurs at %d\n", Position);
  /* Closing the LOB is mandatory if you have opened it: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp = "pm/pm";
  EXEC SQL CONNECT :pm;
  instringLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Java (JDBC): Checking for Patterns in the LOB (instr)

// Seeing if a pattern exists in the LMOB using instr
import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Types;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class Ex2_91
{
  static final int MAXBUFSIZE = 32767;
  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database: 
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
     final int offset = 1;       // Start looking at the first byte
     final int occurrence = 1;  // Start at the 1st occurrence of the pattern 
within the CLOB

   CLOB lob_loc = null;
   String pattern = new String("Junk"); // Pattern to look for within the CLOB.

       ResultSet rset = stmt.executeQuery (
          "SELECT ad_sourcetext FROM Print_media 
           WHERE product_id = 2268 AND ad_id = 21001");
   if (rset.next())
   {
     lob_loc = ((OracleResultSet)rset).getCLOB (1);
   }

   // Search for location of pattern string in the CLOB, starting at offset 1: 
   long result = lob_loc.position(pattern, offset);
   System.out.println("Results of Pattern Comparison : " + 
      Long.toString(result));

   stmt.close();
   conn.commit();
   conn.close();

    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Length: Determining the Length of a LOB

Figure 10-20 Use Case Diagram: Determining the Length of a LOB

Purpose

This procedure describes how to determine the length of a LOB.

Usage Notes

Not applicable.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- GETLENGTH
• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OciLobGetLength.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB DESCRIBE.
• C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB DESCRIBE ...GET LENGTH...
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Oradynaset > PROPERTIES > fields
• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

These examples demonstrate how to determine the length of the LOB in the foreign language text (ad_fltextn) column.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Determining the Length of a LOB
• C (OCI): Determining the Length of a LOB
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Determining the Length of a LOB
• C/C++ (Pro*C/C++): Determining the Length of a LOB
• Visual Basic (OO4O): Determining the Length of a LOB
• Java (JDBC): Determining the Length of a LOB

PL/SQL (DBMS_LOB Package): Determining the Length of a LOB

/* Getting the length of a LOB.
   Example procedure getLengthLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE getLengthLOB_proc IS
   Lob_loc     NCLOB;
   Length      INTEGER;
BEGIN
   /* Select the LOB: */
   SELECT ad_fltextn INTO Lob_loc FROM Print_media
       WHERE product_id = 3106 AND ad_id = 13001;
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
   /* Get the length of the LOB: */
   length := DBMS_LOB.GETLENGTH(Lob_loc);
   IF length IS NULL THEN
       DBMS_OUTPUT.PUT_LINE('LOB is null.');
   ELSE
       DBMS_OUTPUT.PUT_LINE('The length is '|| length);
   END IF;
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
END;