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

LOB Use Case	P	O	CP	B	C	V	J
Appending One LOB to Another	+	+	-	+	+	+	+
Java (JDBC): Appending One LOB to Another	+	+	-	+	+	-	+
Character Set Form: Determining Character Set Form	-	+	-	-	-	-	-
Character Set ID: Determining Character Set ID	-	+	-	-	-	-	-
Checking In a LOB	+	+	-	+	+	+	+
Checking Out a LOB	+	+	-	+	+	+	+
Closing LOBs - see Chapter 3, "LOB Support in Different Programmatic Environments"	-	-	-	-	-	-	-
Comparing All or Part of Two LOBs	+	-	-	+	+	+	+
Copying a LOB Locator	+	+	-	+	+	+	+
Copying All or Part of One LOB to Another LOB	+	+	-	+	+	+	+
Creating a Nested Table Containing a LOB	S	S	-	S	S	S	S
Creating a Table Containing an Object Type with a LOB Attribute	S	S	S	S	S	S	S
Creating a Table Containing One or More LOB Columns	S	S	S	S	S	S	S
Creating a Varray Containing References to LOBs See Chapter 5, "Large Objects: Advanced Topics"	S	S	S	S	S	S	S
Deleting the Row of a Table Containing a LOB	S	S	S	S	S	S	S
Disabling LOB Buffering	-	+	-	+	+	+	-
Displaying LOB Data	+	+	-	+	+	+	+
Enabling LOB Buffering	-	-	-	+	+	+	-
Equality: Checking If One LOB Locator Is Equal to Another	-	+	-	-	+	-	+
Erasing Part of a LOB	+	+	-	+	+	+	+
Flushing the Buffer	-	+	-	+	+	-	-
Initialized Locator: Checking If a LOB Locator Is Initialized	-	+	-	-	+	-	-
Inserting a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()	S	S	S	S	S	S	+
Inserting a Row by Initializing a LOB Locator Bind Variable	S	+	-	+	+	+	+
Inserting a Row by Selecting a LOB From Another Table	S	S	S	S	S	S	S
Length: Determining the Length of a LOB	+	+	-	+	+	+	+
Loading a LOB with Data From a BFILE	+	+	-	+	+	+	+
Loading an Internal Persistent BLOB with Binary Data from a BFILE	+	-	-	-	-	-	-
Loading an Internal Persistent CLOB with BFILE Data	+	-	-	-	-	-	-
LONG to LOB Copying, Using the TO_LOB Operator	S	S	S	S	S	S	S
LONG to LOB Migration Using the LONG-to-LOB API	+	+	-	-	-	-	-
Java (JDBC): Appending One LOB to Another	+	+	-	+	+	-	+
Opening LOBs - see Chapter 3, "LOB Support in Different Programmatic Environments"	-	-	-	-	-	-	-
Patterns: Checking for Patterns in the LOB (instr)	+	-	-	+	+	-	+
Reading a Portion of the LOB (substr)	+	-	-	+	+	+	+
Reading Data from a LOB	+	+	-	+	+	+	+
Streaming LOB Data. See "JDBC: New LOB Streaming APIs" . Note: This API has not yet been incorporated in this chapter as a use case. See a forthcoming release.	-	-	-	-	-	-	+
Trimming LOB Data	+	+	-	+	+	+	+
Updating a LOB with EMPTY_CLOB() or EMPTY_BLOB()	S	S	S	S	S	S	S
Updating a Row by Selecting a LOB From Another Table	S	S	S	S	S	S	S
Updating by Initializing a LOB Locator Bind Variable	S	+	-	+	+	+	+
Write-Append, see Append-Writing to the End of a LOB .	-	-	-	-	-	-	-
Writing Data to a LOB	+	+	+	+	+	+	+

Creating a Table Containing One or More LOB Columns

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

Text description of adlob023.gif follows

Text description of the illustration adlob023.gif

See Also:

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.

Note:

Since you can use SQL DDL directly to create a table containing one or more LOB columns, it is not necessary to use the DBMS_LOB package.

/*  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

Text description of adlob050.gif follows

Text description of the illustration adlob050.gif

See Also:

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:

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;

See Also:

Oracle9i SQL Reference for a complete specification of the syntax for using LOBs in DDL commands, CREATE TYPE and ALTER TYPE with BLOB, CLOB, and BFILE attributes.

Note::

NCLOBs cannot be attributes of an object type.

Creating a Nested Table Containing a LOB

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

Text description of adlob051.gif follows

Text description of the illustration adlob051.gif

See Also:

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:

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()

Text description of adlob045.gif follows

Text description of the illustration adlob045.gif

See Also:

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":

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

See Also:

Oracle9i Sample Schemas for a description of the PM Schema and the Print_media table used in this example.

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

Text description of adlob049.gif follows

Text description of the illustration adlob049.gif

See Also:

Purpose

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

Usage Notes

Note:

Internal LOB types BLOB, CLOB, and NCLOB, use copy semantics, as opposed to reference semantics that apply to BFILEs. When a BLOB, CLOB, or NCLOB is copied from one row to another in the same table or a different table, the actual LOB value is copied, not just the LOB locator.

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":

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.

See Also:

Oracle9i Sample Schemas for a description of the PM Schema and the Print_media table used in this example.

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

Text description of adlob047.gif follows

Text description of the illustration adlob047.gif

See Also:

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":

Syntax

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

/* 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

Text description of adlob073.gif follows

Text description of the illustration adlob073.gif

See Also:

Purpose

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

Usage Notes

Note:

The LOADBLOBFROMFILE and LOADCLOBFROMFILE procedures implement the functionality of this procedure and provide improved features for loading binary data and character data. The improved procedures are available in the PL/SQL environment only. When possible, using one of the improved procedures is recommended. See "Loading an Internal Persistent BLOB with Binary Data from a BFILE" and "Loading an Internal Persistent CLOB with BFILE Data" for more information.

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

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, e

10 Internal Persistent LOBs

Use Case Model

Individual Use Cases

Use Case Model: Internal Persistent LOBs Operations

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

Usage Notes

Syntax

Scenario

Examples

SQL: Create a Table Containing One or More LOB Columns

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

Usage Notes

Syntax

Scenario

Examples

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

Creating a Nested Table Containing a LOB

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

Purpose

Usage Notes

Syntax

Scenario

Examples

SQL: Creating a Nested Table Containing a LOB

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

Usage Notes

Before inserting, Make the LOB Column Non-Null

Inserting LOBs For Binds of More Than 4,000 Bytes

Syntax

Scenario

Examples

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

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

Usage Notes

Inserting LOBs For Binds of More Than 4,000 Bytes

Syntax

Scenario

Examples

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

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

Usage Notes

Syntax

Scenario

Examples

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

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

Loading a LOB with Data From a BFILE

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

Purpose

Usage Notes

Binary Data to Character Set Conversion is Needed on BFILE Data

Specify Amount to be Less than the Size of BFILE

Syntax

Scenario

Examples

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

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

10
Internal Persistent LOBs