14
LOB APIs for Basic Operations

This chapter describes APIs that perform basic operations on BLOB, CLOB, and NCLOB datatypes. The operations given in this chapter can be used with either persistent or temporary LOB instances. Note that operations in this chapter do not apply to BFILEs. APIs covered in this chapter are listed in Table 14-1.

The following information is given for each operation described in this chapter:

• Preconditions describe dependencies that must be met and conditions that must exist before calling each operation.
• Usage Notes provide implementation guidelines such as information specific to a given programmatic environment or datatype.
• Syntax refers you to the syntax reference documentation for each supported programmatic environment.
• Examples describe any setup tasks necessary to run the examples given.

Supported Environments

Table 14-1, "Environments Supported for Basic LOB APIs" indicates which programmatic environments are supported for the APIs discussed in this chapter. The first column describes the operation that the API performs. The remaining columns indicate with "Yes" or "No" whether the API is supported in PL/SQL, OCI, OCCI, COBOL, Pro*C, Visual Basic (VB), and JDBC.

Table 14-1 Environments Supported for Basic LOB APIs  

Operation	PL/SQL	OCI	OCCI	COBOL	Pro*C	VB	JDBC
Appending One LOB to Another	Yes	Yes	No	Yes	Yes	Yes	Yes
Determining Character Set Form	No	Yes	No	No	No	No	No
Determining Character Set ID	No	Yes	No	No	No	No	No
Determining Chunk Size, See: Writing Data to a LOB	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Comparing All or Part of Two LOBs	Yes	No	No	Yes	Yes	Yes	Yes
Converting a BLOB to a CLOB	Yes	No	No	No	No	No	No
Converting a CLOB to a BLOB	Yes	No	No	No	No	No	No
Copying a LOB Locator	Yes	Yes	No	Yes	Yes	Yes	Yes
Copying All or Part of One LOB to Another LOB	Yes	Yes	No	Yes	Yes	Yes	Yes
Disabling LOB Buffering	No	Yes	No	Yes	Yes	Yes	No
Displaying LOB Data	Yes	Yes	No	Yes	Yes	Yes	Yes
Enabling LOB Buffering	No	No	No	Yes	Yes	Yes	No
Equality: Checking If One LOB Locator Is Equal to Another	No	Yes	No	No	Yes	No	Yes
Erasing Part of a LOB	Yes	Yes	No	Yes	Yes	Yes	Yes
Flushing the Buffer	No	Yes	No	Yes	Yes	No	No
Determining Whether LOB Locator Is Initialized	No	Yes	No	No	Yes	No	No
Length: Determining the Length of a LOB	Yes	Yes	No	Yes	Yes	Yes	Yes
Loading a LOB with Data from a BFILE	Yes	Yes	No	Yes	Yes	Yes	Yes
Loading a BLOB with Data from a BFILE	Yes	No	No	No	No	No	No
Loading a CLOB or NCLOB with Data from a BFILE	Yes	No	No	No	No	No	No
Opening Persistent LOBs with the OPEN and CLOSE Interfaces	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Open: Determining Whether a LOB is Open	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Patterns: Checking for Patterns in a LOB Using INSTR	Yes	No	No	Yes	Yes	No	Yes
Reading a Portion of a LOB (SUBSTR)	Yes	No	No	Yes	Yes	Yes	Yes
Reading Data from a LOB	Yes	Yes	No	Yes	Yes	Yes	Yes
Storage Limit, Determining: Maximum Storage Limit for Terabyte-Size LOBs	Yes	No	No	No	No	No	No
Trimming LOB Data	Yes	Yes	No	Yes	Yes	Yes	Yes
WriteNoAppend, see Appending to a LOB .	No	No	No	No	No	No	No
Writing Data to a LOB	Yes	Yes	Yes	Yes	Yes	Yes	Yes

Appending One LOB to Another

This operation appends one LOB instance to another.

Preconditions

Before you can append one LOB to another, the following conditions must be met:

• Two LOB instances must exist.
• Both instances must be of the same type, for example both BLOB or both CLOB types.
• You can pass any combination of persistent or temporary LOB instances to this operation.

Usage Notes

Persistent LOBs: You must lock the row you are selecting the LOB from prior to updating a LOB value if you are using the PL/SQL DBMS_LOB package or OCI. While the SQL INSERT and UPDATE statements implicitly lock the row, locking the row can be done explicitly using the SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs. For more details on the state of the locator after an update, refer to "Updating LOBs Through Updated Locators".

Syntax

See the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "DBMS_LOB" -- APPEND
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- OCILobAppend
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB APPEND.
• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide for information on embedded SQL statements and directives -- LOB APPEND
• Visual Basic (OO4O): (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > append
• Java (JDBC): Oracle Database JDBC Developer's Guide and Reference for information on creating and populating LOB columns in Java.

Examples

To run the following examples, you must create two LOB instances and pass them when you call the given append operation. Creating a LOB instance is described in Chapter 12, "Operations Specific to Persistent and Temporary LOBs".

Examples for this use case are provided in the following programmatic environments:

• PL/SQL DBMS_LOB Package: Appending One LOB to Another
• C (OCI): Appending One LOB to Another
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Appending One LOB to Another
• C/C++ (Pro*C/C++): Appending One LOB to Another
• Visual Basic (OO4O): Appending One LOB to Another
• Java (JDBC): Appending One LOB to Another

PL/SQL DBMS_LOB Package: Appending One LOB to Another

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lappend.sql */

/* Procedure appendLOB_proc is not part of the DBMS_LOB package: */

/* appending one lob to another */

CREATE OR REPLACE PROCEDURE appendLOB_proc
   (Dest_loc IN OUT BLOB, Src_loc IN OUT BLOB) IS
   /* Note: Dest_loc and Src_loc can be persistent or temporary LOBs */
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB APPEND EXAMPLE ------------');
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Dest_loc, DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN (Src_loc, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.APPEND(Dest_loc, Src_loc);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Dest_loc);
   DBMS_LOB.CLOSE (Src_loc);
   DBMS_OUTPUT.PUT_LINE('Append succeeded');

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Append failed');
      DBMS_LOB.CLOSE (Dest_loc);
      DBMS_LOB.CLOSE (Src_loc);
END;
/
SHOW ERRORS;

C (OCI): Appending One LOB to Another

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lappend.c */

/* Appending one LOB to another. */
/* This function appends the Source LOB to the end of the Destination LOB */
#include <oratypes.h>
#include <lobdemo.h>
void appendLOB_proc(OCILobLocator *Lob_loc1, OCILobLocator *Lob_loc2,
                    OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, 
                    OCIStmt *stmthp)
{
  printf ("----------- OCILobAppend Demo --------------\n");    
  /* Opening the LOBs is Optional */
  checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc2, OCI_LOB_READWRITE)); 
  checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc1, OCI_LOB_READONLY));
  
  /* Append Source LOB to the end of the Destination LOB. */
  printf(" append the source Lob to the destination Lob\n");
  checkerr(errhp, OCILobAppend(svchp, errhp, Lob_loc2, Lob_loc1));

  /* Closing the LOBs is Mandatory if they have been Opened */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc2));
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc1));
  return;
}

COBOL (Pro*COBOL): Appending One LOB to Another

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lappend.pco

     * APPENDING ONE LOB TO ANOTHER
       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOB-APPEND.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID        PIC X(11) VALUES "SAMP/SAMP".
       01  DEST          SQL-BLOB.
       01  SRC           SQL-BLOB.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       APPEND-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 :DEST END-EXEC.
           EXEC SQL ALLOCATE :SRC END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL SELECT AD_PHOTO INTO :DEST
                FROM PRINT_MEDIA WHERE PRODUCT_ID = 2268 
                AND AD_ID = 21001 FOR UPDATE END-EXEC.
           EXEC SQL SELECT AD_PHOTO INTO :SRC
                FROM PRINT_MEDIA WHERE PRODUCT_ID = 3060
                AND AD_ID = 11001 END-EXEC.
 
      * Open the DESTination LOB read/write and SRC LOB read only: 
           EXEC SQL LOB OPEN :DEST READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC READ ONLY END-EXEC.

      * Append the source LOB to the destination LOB: 
           EXEC SQL LOB APPEND :SRC TO :DEST END-EXEC.
           EXEC SQL LOB CLOSE :DEST END-EXEC.
           EXEC SQL LOB CLOSE :SRC END-EXEC.

       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :DEST END-EXEC.
           EXEC SQL FREE :SRC 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++): Appending One LOB to Another

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lappend.pc */

/* Appending one LOB to another */
#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 appendLOB_proc()
{
  OCIBlobLocator *Dest_loc, *Src_loc;
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();

  /* Allocate the locators: */
  EXEC SQL ALLOCATE :Dest_loc;
  EXEC SQL ALLOCATE :Src_loc;

  /* Select the destination locator: */
  EXEC SQL SELECT Sound INTO :Dest_loc
           FROM Print_media WHERE product_id = 2268 AND 
           ad_id = 21001 FOR UPDATE;

  /* Select the source locator: */
  EXEC SQL SELECT Sound INTO :Src_loc
           FROM Print_media WHERE product_id = 3060 AND
           ad_id = 11001;

  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Dest_loc READ WRITE;
  EXEC SQL LOB OPEN :Src_loc READ ONLY;

  /* Append the source LOB to the end of the destination LOB: */
  EXEC SQL LOB APPEND :Src_loc TO :Dest_loc;

  /* Closing the LOBs is mandatory if they have been opened: */
  EXEC SQL LOB CLOSE :Dest_loc;
  EXEC SQL LOB CLOSE :Src_loc;

  /* Release resources held by the locators: */
  EXEC SQL FREE :Dest_loc;
  EXEC SQL FREE :Src_loc;
}

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

Visual Basic (OO4O): Appending One LOB to Another

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lappend.bas

'Appending one LOB to another
Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT)
Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value
Set OraAdPhotoClone = OraAdPhoto1

OraDyn.MoveNext
OraDyn.Edit
OraAdPhoto1.Append OraAdPhotoClone
OraDyn.Update

Java (JDBC): Appending One LOB to Another

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lappend.java */

// Appending one LOB to another
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_121
{
  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
    {
     ResultSet rset = null;
     BLOB dest_loc = null;
     BLOB src_loc = null;
   InputStream in = null;
   byte[] buf = new byte[MAXBUFSIZE];
   int length = 0;
   long pos = 0;
      rset = stmt.executeQuery (
          "SELECT ad_photo FROM Print_media 
           WHERE product_id = 2268 AND ad_id = 21001");
   if (rset.next())
   {
     src_loc = ((OracleResultSet)rset).getBLOB (1);
   }
   in = src_loc.getBinaryStream();

       rset = stmt.executeQuery (
          "SELECT ad_photo FROM Print_media 
           WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE");
   if (rset.next())
   {
     dest_loc = ((OracleResultSet)rset).getBLOB (1);
   }
   // Start writing at the end of the LOB.  ie. append: 
   pos = dest_loc.length();
   while ((length = in.read(buf)) != -1)
   {
      // Write the contents of the buffer into position pos of the output LOB: 
      dest_loc.putBytes(pos, buf);
      pos += length;
   }

   // Close all streams and handles: 
   in.close();
   stmt.close();
   conn.commit();
   conn.close();

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

Determining Character Set Form

This section describes how to get the character set form of a LOB instance.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this use case.
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCILobCharSetForm
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• 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): There is no applicable syntax reference for this use case.

Example

The example demonstrates how to determine the character set form of the foreign language text (ad_fltextn).

This functionality is currently available only in OCI:

• PL/SQL (DBMS_LOB Package): No example is provided with this release.
• C (OCI): Determining Character Set Form
• C (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): No example is provided with this release.
• C/C++ (Pro*C/C++): 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.

C (OCI): Determining Character Set Form

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lgetchfm.c */

/* Getting character set form of the foreign language ad text, ad_fltextn */
#include <oratypes.h>
#include <lobdemo.h>
/* This function takes a valid LOB locator and prints the character set form
   of the LOB. 
 */
void getCsformLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                       OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)
{
  ub1 charset_form = 0 ;
  printf ("----------- OCILobCharSetForm Demo --------------\n"); 

  printf (" get the character set form of ad_fltextn\n");
  /* Get the charactersid form of the LOB*/
  checkerr (errhp, OCILobCharSetForm(envhp, errhp, Lob_loc, &charset_form));
  printf(" character Set Form of ad_fltextn is : %d\n", charset_form);
  
  return;
}

Determining Character Set ID

This section describes how to determine the character set ID.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this use case.
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCILobCharSetId
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• 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): There is no applicable syntax reference for this use case.

Example

This functionality is currently available only in OCI:

• PL/SQL (DBMS_LOB Package): No example is provided with this release.
• C (OCI): Determining Character Set ID
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): No example is provided with this release.
• C/C++ (Pro*C/C++): 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.

C (OCI): Determining Character Set ID

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lgetchar.c */

/* Getting character set id */
/* This function takes a valid LOB locator and prints the character set id of 
   the LOB. */
#include <oratypes.h>
#include <lobdemo.h>
void getCsidLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                     OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)
{
  ub2 charsetid =0 ;
  printf ("----------- OCILobCharSetID Demo --------------\n");

  printf (" get the character set id of adfltextn_locator\n");
  /* Get the charactersid ID of the LOB*/
  checkerr (errhp, OCILobCharSetId(envhp, errhp, Lob_loc, &charsetid));
  printf(" character Set ID of ad_fltextn is : %d\n", charsetid);
  
  return;
}

Loading a LOB with Data from a BFILE

This operation loads a LOB with data from a BFILE. This procedure can be used to load data into any persistent or temporary LOB instance of any LOB datatype.

Preconditions

Before you can load a LOB with data from a BFILE, the following conditions must be met:

• The BFILE must exist.
• The target LOB instance must exist.

Usage Notes

Note the following issues regarding this operation.

Use LOADCLOBFROMFILE When Loading Character Data

When you use the DBMS_LOB.LOADFROMFILE procedure to load a CLOB or NCLOB instance, you are loading the LOB with binary data from the BFILE and no implicit character set conversion is performed. For this reason, using the DBMS_LOB.LOADCLOBFROMFILE procedure is recommended when loading character data, see Loading a CLOB or NCLOB with Data from a BFILE on for more information.

Specifying Amount of BFILE Data to Load

The value you pass for the amount parameter to functions listed in Table 14-2 must be one of the following:

• An amount less than or equal to the actual size (in bytes) of the BFILE you are loading.
• The maximum allowable LOB size (in bytes).--Passing this value, loads the entire BFILE. You can use this technique to load the entire BFILE without determining the size of the BFILE before loading. To get the maximum allowable LOB size, use the technique described in Table 14-2.

Table 14-2 Maximum LOB Size for Load from File Operations

Environment	Function	To pass maximum LOB size, get value of:
DBMS_LOB	DBMS_LOB.LOADBLOBFROMFILE	DBMS_LOB.LOBMAXSIZE
DBMS_LOB	DBMS_LOB.LOADCLOBFROMFILE	DBMS_LOB.LOBMAXSIZE
OCI	OCILobLoadFromFile2 (For LOBs less than 4 gigabytes in size.)	UB4MAXVAL
OCI	OCILobLoadFromFile2 (For LOBs 4 gigabytes and larger in size.)	UB8MAXVAL
OCI	OCILobLoadFromFile (For LOBs less than 4 gigabytes in size.)	UB4MAXVAL
OCI	OCILobLoadFromFile (For LOBs 4 gigabytes and larger in size.)	UB8MAXVAL

Syntax

See the following syntax references for details on using this operation in each programmatic environment:

• PL/SQL (DBMS_LOB Package): PL/SQL Packages and Types Reference "DBMS_LOB" -- LOADFROMFILE.
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCILobLoadFromFile.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL 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++ 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): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB): 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++ (Pro*C/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): Loading a LOB with Data from a BFILE

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lloaddat.sql */

/* Procedure loadLOBFromBFILE_proc is not part of the DBMS_LOB package: */

/* loading a lob with bfile data */

CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc (Dest_loc IN OUT BLOB) IS
   /* Note: Dest_loc can be a persistent or temporary LOB */
   Src_loc        BFILE := BFILENAME('MEDIA_DIR', 'keyboard_logo.jpg');
   Amount         INTEGER := 4000;
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB LOADFORMFILE EXAMPLE ------------');
   /* Opening the 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);
END;
/
SHOW ERRORS;

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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lloaddat.c */
#include <oratypes.h>
#include <lobdemo.h>

void loadLOBDataFromBFile_proc(OCILobLocator *Lob_loc, OCILobLocator* BFile_loc, 
                               OCIEnv *envhp,
                               OCIError *errhp, OCISvcCtx *svchp, 
                               OCIStmt *stmthp)
{
  oraub8         amount= 2000;

  printf ("----------- OCILobLoadFromFile Demo --------------\n");

  printf (" open the bfile\n");
  /* Opening the BFILE locator is Mandatory */
  checkerr (errhp, (OCILobOpen(svchp, errhp, BFile_loc, OCI_LOB_READONLY)));

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

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

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

  return;
}

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

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lloaddat.pco

       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

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lloaddat.bas

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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lloaddat.java */

// 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 a BLOB with Data from a BFILE

This procedure loads a BLOB with data from a BFILE. This procedure can be used to load data into any persistent or temporary BLOB instance.

Preconditions

The following conditions must be met before calling this procedure:

• The target BLOB instance must exist.
• The source BFILE must exist.
• You must open the BFILE. (After calling this procedure, you must close the BFILE at some point.)

Usage Notes

Note the following with respect to this operation:

New Offsets Returned

Using DBMS_LOB.LOADBLOBFROMFILE to load binary data into a BLOB achieves the same result as using DBMS_LOB.LOADFROMFILE, but also returns the new offsets of BLOB.

Specifying Amount of BFILE Data to Load

The value you pass for the amount parameter to the
DBMS_LOB.LOADBLOBFROMFILE function must be one of the following:

• An amount less than or equal to the actual size (in bytes) of the BFILE you are loading.
• The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE
Passing this value causes the function to load the entire BFILE. This is a useful technique for loading the entire BFILE without introspecting the size of the BFILE.

  See Also: Table 14-2, "Maximum LOB Size for Load from File Operations"

Syntax

See PL/SQL Packages and Types Reference, "DBMS_LOB" -- LOADBLOBFROMFILE procedure for syntax details on this procedure.

Examples

This example is available in PL/SQL only. This API is not provided in other programmatic environments. This example illustrates:

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

PL/SQL: Loading a BLOB with BFILE Data

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lldblobf.sql */

CREATE OR REPLACE PROCEDURE loadBLOB_proc (dst_loc IN OUT BLOB) IS
  src_loc     BFILE := bfilename('MEDIA_DIR','keyboard_logo.jpg') ;
  src_offset  NUMBER := 1;
  dst_offset  NUMBER := 1;
  src_osin    NUMBER;
  dst_osin    NUMBER;
  bytes_rd    NUMBER;
  bytes_wt    NUMBER; 
BEGIN
  DBMS_OUTPUT.PUT_LINE('------------ LOB LOADBLOBFORMFILE EXAMPLE 
------------');
  /* 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();

  /* 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 a CLOB or NCLOB with Data from a BFILE

This procedure loads a CLOB or NCLOB with character data from a BFILE. This procedure can be used to load data into a persistent or temporary CLOB or NCLOB instance.

Preconditions

The following conditions must be met before calling this procedure:

• The target CLOB or NCLOB instance must exist.
• The source BFILE must exist.
• You must open the BFILE. (After calling this procedure, you must close the BFILE at some point.)

Usage Notes

You can specify the character set id of the BFILE when calling this procedure. Doing so, ensures that the character set is properly converted from the BFILE data character set to the destination CLOB or NCLOB character set.

Specifying Amount of BFILE Data to Load

The value you pass for the amount parameter to the
DBMS_LOB.LOADCLOBFROMFILE function must be one of the following:

• An amount less than or equal to the actual size (in characters) of the BFILE data you are loading.
• The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE
Passing this value causes the function to load the entire BFILE. This is a useful technique for loading the entire BFILE without introspecting the size of the BFILE.

Syntax

See PL/SQL Packages and Types Reference, "DBMS_LOB" -- LOADCLOBFROMFILE procedure for syntax details on this procedure.

Examples

The following examples illustrate different techniques for using this API:

• "PL/SQL: Loading Character Data from a BFILE into a LOB"
• "PL/SQL: Loading Segments of Character Data into Different LOBs"

PL/SQL: Loading Character Data from a BFILE into a LOB

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.

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lldclobf.sql */

CREATE OR REPLACE PROCEDURE loadCLOB1_proc (dst_loc IN OUT CLOB) IS
  src_loc     bfile := bfilename('MEDIA_DIR','monitor_3060.txt') ;
  amt         number := dbms_lob.lobmaxsize;
  src_offset  number := 1 ;
  dst_offset  number := 1 ;
  lang_ctx    number := dbms_lob.default_lang_ctx;
  warning     number;
BEGIN
  DBMS_OUTPUT.PUT_LINE('------------ LOB LOADCLOBFORMFILE EXAMPLE 
------------');
  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) ;

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

PL/SQL: Loading Segments of Character Data into Different LOBs

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.

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lldclobs.sql */

CREATE OR REPLACE PROCEDURE loadCLOB2_proc (dst_loc1 IN OUT NCLOB,
                                             dst_loc2 IN OUT NCLOB) IS
  src_loc     bfile := bfilename('MEDIA_DIR','monitor_3060.txt');
  amt         number := 100;
  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_OUTPUT.PUT_LINE('------------ LOB LOADCLOBFORMFILE EXAMPLE 
------------');
  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(' ----------------------------' ) ;

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

  /* 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(' ----------------------------' ) ;


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

Determining Whether a LOB is Open

This operation determines whether a LOB is open.

Preconditions

The LOB instance must exist before executing this procedure.

Usage Notes

When a LOB is open, it must be closed at some point later in the session.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): PL/SQL Packages and Types Reference "DBMS_LOB" -- OPEN, ISOPEN.
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions -- OCILobIsOpen.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL 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++ 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): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

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++ (Pro*C/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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lisopen.sql */

/* Procedure lobIsOpen_proc is not part of the DBMS_LOB package: */

/* seeing if lob is open */

CREATE OR REPLACE PROCEDURE lobIsOpen_proc (Lob_loc IN OUT BLOB) IS
   /* Note: Lob_loc can be a persistent or a temporary LOB */
   Retval      INTEGER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB ISOPEN EXAMPLE ------------');
   /* 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. */

   if Retval = 1 THEN
     DBMS_OUTPUT.PUT_LINE('Input locator is open');
   else
     DBMS_OUTPUT.PUT_LINE('Input locator is not open');
   end if;
END;
/
SHOW ERRORS;

C (OCI): Checking If a LOB Is Open

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lisopen.c */

/* Checking if LOB is Open. */
#include <oratypes.h>
#include <lobdemo.h>
void seeIfLOBIsOpen_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                         OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)
{
  boolean isOpen;

  printf ("----------- OCILobIsOpen Demo --------------\n");
  /* 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 */
  }
  return;
}

COBOL (Pro*COBOL): Checking If a LOB Is Open

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lisopen.pco

     * 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++ (Pro*C/C++): Checking If a LOB Is Open

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lisopen.pc */

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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lisopen.java */

// 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();
    }
  }
}

Displaying LOB Data

This section describes APIs that allow you to read LOB data. You can use this operation to read LOB data into a buffer. This is useful if your application requires displaying large amounts of LOB data or streaming data operations.

Usage Notes

Note the following when using these APIs.

Streaming Mechanism

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

Amount Parameter

The value you pass for the amount parameter is restricted for the APIs described in Table 14-3.

Table 14-3 Maximum LOB Size for Amount Parameter

Environment	Function	Value of amount parameter is limited to:
DBMS_LOB	DBMS_LOB.READ	The size of the buffer, 32K bytes.
OCI	OCILobRead (For LOBs less than 4 gigabytes in size.)	UB4MAXVAL Specifying this amount reads the entier file.
OCI	OCILobRead2 (For LOBs 4 gigabytes and larger in size.)	UB8MAXVAL Specifying this amount reads the entier file.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "DBMS_LOB" -- OPEN, READ, CLOSE.
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCILobOpen, OCILobRead2, OCILobClose.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB READ.
• C/C++ (Pro*C/C++): Pro*C/C++ 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): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

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++ (Pro*C/C++): Displaying LOB Data
• Visual Basic (OO4O): Displaying LOB Data
• Java (JDBC): Displaying LOB Data

PL/SQL (DBMS_LOB Package): Displaying LOB Data

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/ldisplay.sql */

/* Procedure displayLOB_proc is not part of the DBMS_LOB package: */ 

/* displaying lob data */

CREATE OR REPLACE PROCEDURE displayLOB_proc (Lob_loc IN OUT BLOB) IS 
    /* Note: Lob_loc can be a persistent or temporary LOB */
Buffer   RAW(1024); 
Amount   BINARY_INTEGER := 1024; 
Position INTEGER := 1; 
BEGIN 
   DBMS_OUTPUT.PUT_LINE('------------ LOB DATA DISPLAY EXAMPLE ------------');
   /* 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(rawtohex(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_LOB.CLOSE (Lob_loc);
END; 
/

SHOW ERRORS;

C (OCI): Displaying LOB Data

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/ldisplay.c */

/* Displaying LOB data. This example reads the entire contents of a CLOB 
   piecewise into a buffer using the standard polling method, processing 
   each buffer piece after every READ operation until the entire CLOB 
   has been read. */
#include <oratypes.h>
#include <lobdemo.h>
void displayLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                     OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)
{
  oraub8 amt;
  ub4 offset;
  sword retval;
  boolean done;
  ub1 bufp[MAXBUFLEN];
  ub4 buflen;
  ub1 piece;

  printf ("----------- LOB Data Display Demo --------------\n"); 
  /* Open the CLOB */
  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((void *)bufp, '\0', MAXBUFLEN);
  done = FALSE;
  piece = OCI_FIRST_PIECE;
  while (!done)
  {
    retval = OCILobRead2(svchp, errhp, Lob_loc, &amt, NULL, offset,
                         (void *) bufp, buflen, piece, (void *)0,
                         (OCICallbackLobRead2) 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 in bytes.
       */
      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 in bytes.
       */
      piece = OCI_NEXT_PIECE;
      break;
    default:
      checkerr (errhp, retval);
      done = TRUE;
      break;
    }
  } /* while */

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

COBOL (Pro*COBOL): Displaying LOB Data

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/ldisplay.pco

     * 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++ (Pro*C/C++): Displaying LOB Data

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/ldisplay.pc */

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

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/ldisplay.bas

'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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/ldisplay.java */

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

This section describes how to read data from LOBs.

Usage Notes

Note the following when using this operation.

Streaming Read in OCI

The most efficient way to read of large amounts of LOB data is to use OCILobRead2() with the streaming mechanism enabled using polling or callback. To do so, specify the starting point of the read using the offset parameter and use the symbol OCI_LOBMAXSIZE for the amount, as follows:

ub8  char_amt =  20;
ub8  byte_amt = 20;
ub4  offset = 1000;

OCILobRead2(svchp, errhp, locp, &byte_amt, &char_amt, offset, bufp, bufl, 
OCI_ONE_PIECE,
0, 0, 0, 0)

When using polling mode, be sure to look at the value of the 'amount' parameter after each OCILobRead2() call to see how many bytes were read into the buffer because 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 because 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 run 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, then 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

Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "DBMS_LOB" -- OPEN, GETCHUNKSIZE, READ, CLOSE.
• C (OCI): Oracle Call Interface Programmer's Guide Chapter 7, " "Relational Functions" -- LOB Functions, OciLobOpen, OCILobRead2, OciLobClose.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB READ.
• C/C++ (Pro*C/C++): Pro*C/C++ 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): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lread.sql */

/* Procedure readLOB_proc is not part of the DBMS_LOB package: */

/* reading data from lob */

CREATE OR REPLACE PROCEDURE readLOB_proc (Lob_loc IN OUT BLOB) IS
    /* Note: Lob_loc can be a persistent or a temporary LOB */
    Buffer            RAW(32767);
    Amount            BINARY_INTEGER := 32767;
    Position          INTEGER := 2;
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB READ EXAMPLE ------------');
   /* 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);

   DBMS_OUTPUT.PUT_LINE(RAWTOHEX(substr(Buffer, 1, 200)));
END;
/

SHOW ERRORS;

C (OCI): Reading Data from a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lread.c */

/* Reading LOB data. 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 <oratypes.h>
#include <lobdemo.h>


static void pollingRead(OCILobLocator *Lob_loc, OCIEnv *envhp,
                        OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)
{
  oraub8 amt;
  oraub8 offset;
  sword retval;
  ub1 bufp[MAXBUFLEN];
  oraub8 buflen;
  boolean done;
  ub1 piece;

  buflen = (oraub8)MAXBUFLEN;
  /* Open the CLOB */
  printf(" call OCILobOpen\n");
  checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY));

  printf ("  ---------- OCILobRead one-piece mode  -------------\n");
  offset=1;
  amt = 5;
  checkerr(errhp, OCILobRead2(svchp, errhp, Lob_loc, NULL, &amt, offset,
                              (void *)bufp, buflen, OCI_ONE_PIECE, (void *)0,
                              (OCICallbackLobRead2) 0,
                              (ub2) 0, (ub1) SQLCS_IMPLICIT));
  printf ("  amt read= %d\n", (ub4)amt);
  printf ("  ---------- OCILobRead polling mode  -------------\n");
  /* Setting the amt to the buffer length.  Note here that amt is in chars
     since we are using a CLOB  */
  amt = 0;

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

  while (!done)
  {
  retval = OCILobRead2(svchp, errhp, Lob_loc, NULL, &amt, offset,
                       (void *) bufp, buflen, piece, (void *)0,
                       (OCICallbackLobRead2) 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 in bytes. */
      printf("  amt read=%d in the last call\n", (ub4)amt);
      done = TRUE;
     break;
    case OCI_ERROR:
      /*   report_error();        this function is not shown here */
      done = TRUE;
      break;
    case OCI_NEED_DATA:
      printf("  amt read=%d\n", (ub4)amt);
      piece = OCI_NEXT_PIECE;
      break;
    default:
      (void) printf("Unexpected ERROR: OCILobRead2() LOB.\n");
       done = TRUE;
       break;
    } 
  }
  /* Closing the CLOB is mandatory if you have opened it */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));
}

struct somestruct 
{ 
  ub4 count; 
};

sb4 lobCallback(dvoid *ctxp, CONST dvoid *bufp, oraub8 len, ub1 piece,
                dvoid **changed_bufpp, oraub8 *changed_lenp)
{
  struct somestruct *ctx = (struct somestruct *)ctxp;
  printf("  In callback, count = %d, len passed in=%d, piece=%d\n",
         ctx->count++,(ub4)len, piece);

  return OCI_CONTINUE;
} 

static void callbackRead(OCILobLocator *Lob_loc, OCIEnv *envhp,
                         OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)
{
  oraub8 amt;
  oraub8 offset;
  sword retval;
  ub1 bufp[MAXBUFLEN];
  oraub8 buflen;
  boolean done;

  struct somestruct ctx;

  printf ("  ---------- OCILobRead Callback mode  -------------\n");

  /* Open the CLOB */
  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 chars
     since we are using a CLOB  */
  amt = 0; 
  buflen =(oraub8) sizeof(bufp);

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

  ctx.count = 0;
  checkerr (errhp, OCILobRead2(svchp, errhp, Lob_loc, NULL, &amt, offset,
                       (void *) bufp, buflen, OCI_FIRST_PIECE,
                       (void *)&ctx, lobCallback,
                       (ub2) 0, (ub1) SQLCS_IMPLICIT));

  printf("  total amt read=%d in OCILobRead2 callback mode\n", (ub4)amt);
  /* Closing the CLOB is mandatory if you have opened it */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));
}

void readLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                  OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)

{
  printf ("----------- OCILobRead Demo  --------------\n");
  /* One-piece and Polling mode */
  pollingRead(Lob_loc, envhp, errhp, svchp, stmthp);
  /* Callback mode */
  callbackRead(Lob_loc, envhp, errhp, svchp, stmthp);
}

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

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lread.pco

     * 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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lread.pc */

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

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lread.bas

'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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lread.java */

// 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 a LOB (SUBSTR)

This section describes how to read a portion of a LOB using SUBSTR.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "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 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++ 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): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

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)

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lsubstr.sql */

/* Procedure substringLOB_proc is not part of the DBMS_LOB package: */

/* reading portion of lob (substr) */

CREATE OR REPLACE PROCEDURE substringLOB_proc (Lob_loc IN OUT BLOB) IS
    /* Note: Lob_loca can be a persistent or a temporary LOB */
    Amount            BINARY_INTEGER := 32767;
    Position          INTEGER := 3;
    Buffer            RAW(32767);
BEGIN
    DBMS_OUTPUT.PUT_LINE('------------ LOB SUBSTR EXAMPLE ------------');
    /* 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);
    DBMS_OUTPUT.PUT_LINE(rawtohex(substr(buffer,1,200)));
END;
/

SHOW ERRORS;

/* For persistent LOBs, DBMS_LOB.SUBSTR can be used in a SQL statement too.
   In the following SQL statement, 200 is the amount to read 
   and 1 is the starting offset from which to read: */
SELECT DBMS_LOB.SUBSTR(ad_sourcetext, 200, 1) FROM print_media 
WHERE product_id = 2268;

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

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lsubstr.pco

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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lsubstr.pc */

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

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lsubstr.bas

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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lsubstr.java */

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

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

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "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 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++ 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): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lcompare.sql */

/* Procedure compareTwoLOBs_proc is not part of the DBMS_LOB package: */

/* comparing all or part of lob */

CREATE OR REPLACE PROCEDURE compareTwoLOBs_proc
    (Lob_loc1 IN OUT BLOB, Lob_loc2 IN OUT BLOB) IS
    /* Note: Lob_loc1 and Lob_loc2 can be persistent or temporary LOBs */
    Amount              INTEGER := 32767;
    Retval              INTEGER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('------------ LOB COMPARE EXAMPLE ------------');
    /* 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 LOBs: */
    retval := DBMS_LOB.COMPARE(Lob_loc1, Lob_loc2, Amount, 1, 1);
    IF retval = 0 THEN
       DBMS_OUTPUT.PUT_LINE('LOBs are equal');
    ELSE
       DBMS_OUTPUT.PUT_LINE('LOBs are not equal');
    END IF;
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc1);
    DBMS_LOB.CLOSE (Lob_loc2);
END;
/

SHOW ERRORS;

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

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lcompare.pco

      * 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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lcompare.pc */

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

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lcompare.bas

'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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lcompare.java */

// 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 a LOB Using INSTR

This section describes how to see if a pattern exists in a LOB using INSTR.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "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 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++ 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): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Checking for Patterns in a 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 a LOB (instr)
• C/C++ (Pro*C/C++): Checking for Patterns in a LOB (instr)
• Visual Basic (OO4O): No example is provided with this release.
• Java (JDBC): Checking for Patterns in a LOB (instr)

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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/linstr.sql */

/* Procedure instringLOB_proc is not part of the DBMS_LOB package: */

/* seeing if pattern exists in lob (instr) */

CREATE OR REPLACE PROCEDURE instringLOB_proc (Lob_loc IN OUT BLOB) IS
   /* Note: Lob_loccan be a persistent or temporary LOB */
   Pattern        RAW(30) := hextoraw('aabb');
   Position       INTEGER := 0;
   Offset         INTEGER := 1;
   Occurrence     INTEGER := 1;
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB INSTR EXAMPLE ------------');
   /* 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;
/

SHOW ERRORS;

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

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/linstr.pco

     * 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 a LOB (instr)

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/linstr.pc */

/* 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 a LOB (instr)

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/linstr.java */

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

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

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "DBMS_LOB" -- GETLENGTH
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCILobGetLength2.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL 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++ 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): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB 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

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/llength.sql */

/* Procedure getLengthLOB_proc is not part of the DBMS_LOB package: */

/* getting the length of a lob */

CREATE OR REPLACE PROCEDURE getLengthLOB_proc (Lob_loc IN OUT BLOB) IS
   /* Note: Lob_loc can be a persistent or temporary LOB */
   Length      INTEGER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB GETLENGTH EXAMPLE ------------');
   /* 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;
/
SHOW ERRORS;

C (OCI): Determining the Length of a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/llength.c */

/* Getting the length of a LOB */
#include <oratypes.h>
#include <lobdemo.h>

/* This function gets the length of the LOB */
void getLengthLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                       OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)

{
  oraub8 length;

  printf("----------- OCILobGetLength Demo --------------\n");
  /* Opening the LOB is Optional */
  printf(" Open the locator (optional)\n");
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY)));
  
  printf(" get the length of ad_fltextn.\n");
  checkerr (errhp, OCILobGetLength2(svchp, errhp, Lob_loc, &length));

  /* Length is undefined if the LOB is NULL or undefined */
  printf(" Length of LOB is %d\n",(ub4)length);

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

  return;
}

COBOL (Pro*COBOL): Determining the Length of a LOB

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/llength.pco

     * GETTING THE LENGTH OF A LOB
       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOB-LENGTH.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  CLOB1          SQL-CLOB.
       01  LOB-ATTR-GRP.
           05 LEN         PIC S9(9) COMP.
       01  D-LEN          PIC 9(4).
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.
       PROCEDURE DIVISION.
       LOB-LENGTH.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Allocate and initialize the target CLOB: 
           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.
  
      * Obtain the length of the CLOB: 
           EXEC SQL 
              LOB DESCRIBE :CLOB1 GET LENGTH INTO :LEN END-EXEC.
           MOVE LEN TO D-LEN.
           DISPLAY "The length is ", D-LEN.

      * Free the resources used by the CLOB: 
       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++): Determining the Length of a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/llength.pc */

/* Getting the length of a LOB */
#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 getLengthLOB_proc()
{
  OCIClobLocator *Lob_loc;
  unsigned int Length;

  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 READ ONLY;
  /* Get the Length: */
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length;
  /* If the LOB is NULL or unitialized, then Length is Undefined: */
  printf("Length is %d characters\n", Length);
  /* 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 = "samp/samp";
  EXEC SQL CONNECT :samp;
  getLengthLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Visual Basic (OO4O): Determining the Length of a LOB

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/llength.bas

'Getting the length of a LOB
Dim MySession As OraSession
Dim OraDb As OraDatabase

Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Set OraDyn = OraDb.CreateDynaset(
    "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT)
Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value

'Display out size of the lob: 
MsgBox "Length of the lob is " & OraAdPhoto1.Size

Java (JDBC): Determining the Length of a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/llength.java */

//Getting the length of a LOB
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_95
{

  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 lob_loc = null;
      ResultSet rset = stmt.executeQuery 
          ("SELECT ad_sourcetext FROM Print_media WHERE product_id = 3106");
   if (rset.next())
   {
     lob_loc = ((OracleResultSet)rset).getCLOB (1);
   }

   System.out.println(
      "Length of this column is : " + Long.toString(lob_loc.length()));

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

Copying All or Part of One LOB to Another LOB

This section describes how to copy all or part of a LOB to another LOB. These APIs copy an amount of data you specify from a source LOB to a destination LOB.

Usage Notes

Note the following issues when using this API.

Specifying Amount of Data to Copy

The value you pass for the amount parameter to the DBMS_LOB.COPY function must be one of the following:

• An amount less than or equal to the actual size of the data you are loading.
• The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE
Passing this value causes the function to read the entire LOB. This is a useful technique for reading the entire LOB without introspecting the size of the LOB.

Note that for character data, the amount is specified in characters, while for binary data, the amount is specified in bytes.

Locking the Row Prior to Updating

If you plan to update a LOB value, then you must lock the row containing the LOB prior to updating. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs.

For more details on the state of the locator after an update, refer to "Updating LOBs Through Updated Locators".

Syntax

See the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "DBMS_LOB" -- COPY
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCILobCopy2
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB COPY. Also reference PL/SQL DBMS_LOB.COPY.
• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide for information on embedded SQL statements and directives -- LOB COPY
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > copy, and > OBJECTS > Oradynaset > METHODS > movenext, edit
• Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Copying All or Part of One LOB to Another LOB
• C (OCI): Copying All or Part of One LOB to Another LOB
• COBOL (Pro*COBOL): Copying All or Part of One LOB to Another LOB
• C/C++ (Pro*C/C++): Copying All or Part of a LOB to Another LOB
• Visual Basic (OO4O): Copying All or Part of One LOB to Another LOB
• Java (JDBC): Copying All or Part of One LOB to Another LOB

PL/SQL (DBMS_LOB Package): Copying All or Part of One LOB to Another LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lcopy.sql */

/* Procedure copyLOB_proc is not part of the DBMS_LOB package: */

/* copying all or part of a lob to another lob */

CREATE OR REPLACE PROCEDURE copyLOB_proc
   (Dest_loc IN OUT BLOB, Src_loc IN OUT BLOB) IS
   /* Note: Dest_loc and Src_loc can be persistent or temporary LOBs */
   Amount       NUMBER := 1;
   Dest_pos     NUMBER := 3;
   Src_pos      NUMBER := 2;
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB COPY EXAMPLE ------------');
   /* Opening the LOBs is optional: */
   DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Copies the LOB from the source position to the destination position: */
   DBMS_LOB.COPY(Dest_loc, Src_loc, Amount, Dest_pos, Src_pos);
   /* Closing LOBs is mandatory if you have opened them: */
   DBMS_LOB.CLOSE(Dest_loc);
   DBMS_LOB.CLOSE(Src_loc);
   DBMS_OUTPUT.PUT_LINE('Copy succeeded');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Copy failed');
      DBMS_LOB.CLOSE(Dest_loc);
      DBMS_LOB.CLOSE(Src_loc);
END;
/
SHOW ERRORS;

C (OCI): Copying All or Part of One LOB to Another LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lcopy.c */

/* This function copies part of the Source LOB into a specified position
   in the destination LOB 
 */
#include <oratypes.h>
#include <lobdemo.h>

void copyAllPartLOB_proc(OCILobLocator *Lob_loc1, OCILobLocator *Lob_loc2,
                    OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, 
                    OCIStmt *stmthp)
{
  oraub8 Amount = 100;                                  /* <Amount to Copy> */
  oraub8 Dest_pos = 100;                  /*<Position to start copying into> */
  oraub8 Src_pos = 1;                    /* <Position to start copying from> */
  printf ("----------- OCILobCopy Demo --------------\n");    

  /* Opening the LOBs is Optional */
  printf (" open the destination locator (optional)\n");
  checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc2, OCI_LOB_READWRITE)); 
  printf (" open the source locator (optional)\n");
  checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc1, OCI_LOB_READONLY));
  
  printf (" copy the lob (amount) from the source to destination\n");
  checkerr (errhp, OCILobCopy2(svchp, errhp, Lob_loc2, Lob_loc1,
                               Amount, Dest_pos, Src_pos));

  /* Closing the LOBs is Mandatory if they have been Opened */
  printf(" close the locators\n");
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc2));
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc1));

  return;
}

COBOL (Pro*COBOL): Copying All or Part of One LOB to Another LOB

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lcopy.pco

     * COPYING ALL OR PART OF A LOB TO ANOTHER LOB
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BLOB-COPY.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  DEST           SQL-BLOB.
       01  SRC            SQL-BLOB.
     
      * Define the amount to copy.
      * This value has been chosen arbitrarily: 
       01  AMT            PIC S9(9) COMP VALUE 10.
      * Define the source and destination position.
      * These values have been chosen arbitrarily: 
       01  SRC-POS        PIC S9(9) COMP VALUE 1.
       01  DEST-POS       PIC S9(9) COMP VALUE 1.

      * The return value from PL/SQL function: 
       01  RET            PIC S9(9) COMP.
           EXEC SQL INCLUDE SQLCA END-EXEC.
       PROCEDURE DIVISION.
       COPY-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 :DEST END-EXEC.
           EXEC SQL ALLOCATE :SRC END-EXEC.
           DISPLAY "Source and destination LOBs are open.".

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL 
                SELECT AD_PHOTO INTO :SRC
                FROM PRINT_MEDIA PM 
                WHERE PM.PRODUCT_ID = 3106 AND AD_ID = 13001 END-EXEC.
           DISPLAY "Source LOB populated.".
           EXEC SQL
                SELECT AD_PHOTO INTO :DEST
                FROM PRINT_MEDIA PM 
                WHERE PM.PRODUCT_ID = 3060 AND AD_ID = 11001 FOR UPDATE 
END-EXEC.
           DISPLAY "Destination LOB populated.".
 
      * Open the DESTination LOB read/write and SRC LOB read only
           EXEC SQL LOB OPEN :DEST READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC READ ONLY END-EXEC.
           DISPLAY "Source and destination LOBs are open.".

      * Copy the desired amount
           EXEC SQL 
                LOB COPY :AMT FROM :SRC AT :SRC-POS
                TO :DEST AT :DEST-POS END-EXEC.
           DISPLAY "Src LOB copied to destination LOB.".

      * Execute PL/SQL to get COMPARE functionality
      * to make sure that the BLOBs are identical
           EXEC SQL EXECUTE
             BEGIN
               :RET := DBMS_LOB.COMPARE(:SRC,:DEST,: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 :DEST END-EXEC.
           EXEC SQL LOB CLOSE :SRC END-EXEC.

       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :DEST END-EXEC.
           EXEC SQL FREE :SRC 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++): Copying All or Part of a LOB to Another LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lcopy.pc */

/* Copying all or part of a LOB to another LOB */
#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 copyLOB_proc()
{
  OCIBlobLocator *Dest_loc, *Src_loc;
  int Amount = 5;
  int Dest_pos = 10;
  int Src_pos = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate the LOB locators: */
  EXEC SQL ALLOCATE :Dest_loc;
  EXEC SQL ALLOCATE :Src_loc;
  /* Select the LOBs: */
  EXEC SQL SELECT ad_photo INTO :Dest_loc
           FROM Print_media WHERE product_id = 2268 AND AD_ID = 21001 FOR 
UPDATE;
  EXEC SQL SELECT ad_photo INTO :Src_loc
           FROM Print_media WHERE product_id = 2056 AND ad_id = 12001;
  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Dest_loc READ WRITE;
  EXEC SQL LOB OPEN :Src_loc READ ONLY;
  /* Copies the specified Amount from the source position in the source
     LOB to the destination position in the destination LOB: */
  EXEC SQL LOB COPY :Amount
               FROM :Src_loc AT :Src_pos TO :Dest_loc AT :Dest_pos;
  /* Closing the LOBs is mandatory if they have been opened: */
  EXEC SQL LOB CLOSE :Dest_loc;
  EXEC SQL LOB CLOSE :Src_loc;
  /* Release resources held by the locators: */
  EXEC SQL FREE :Dest_loc;
  EXEC SQL FREE :Src_loc;
}

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

Visual Basic (OO4O): Copying All or Part of One LOB to Another LOB

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lcopy.bas

'Copying all or part of a LOB to another LOB
Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Set OraDyn = OraDb.CreateDynaset(
    "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT)
Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value

Set OraAdPhotoClone = OraAdPhoto1.Clone

'Go to next row and copy LOB

OraDyn.MoveNext

OraDyn.Edit
OraAdPhoto1.Copy OraAdPhotoClone, OraAdPhotoClone.Size, 1, 1
OraDyn.Update

Java (JDBC): Copying All or Part of One LOB to Another LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lcopy.java */

// Copying all or part of a LOB to another LOB
 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_100
{

  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
    {
     final int AMOUNT_TO_COPY = 2000;
     ResultSet rset = null;
     BLOB dest_loc = null;
     BLOB src_loc = null;
     InputStream in = null;
     OutputStream out = null;
     byte[] buf = new byte[AMOUNT_TO_COPY];
     rset = stmt.executeQuery (
          "SELECT ad_photo FROM Print_media 
           WHERE product_id = 3060 AND ad_ad = 11001");
   if (rset.next())
   {
     src_loc = ((OracleResultSet)rset).getBLOB (1);
   }
   in = src_loc.getBinaryStream();
   
       rset = stmt.executeQuery (
          "SELECT ad_photo FROM Print_media 
           WHERE product_id = 3106 AND ad_id = 13001 FOR UPDATE");
   if (rset.next())
   {
     dest_loc = ((OracleResultSet)rset).getBLOB (1);
   }
   out = dest_loc.getBinaryOutputStream();
   

   // read AMOUNT_TO_COPY bytes into buf from stream, starting from offset 0: 
   in.read(buf, 0, AMOUNT_TO_COPY);

   // write AMOUNT_TO_COPY bytes from buf into output stream, starting at offset 
0: 
   out.write(buf, 0, AMOUNT_TO_COPY);

   // Close all streams and handles
   in.close();
   out.flush();
   out.close();
   stmt.close();
   conn.commit();
   conn.close();

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

Copying a LOB Locator

This section describes how to copy a LOB locator. Note that different locators may point to the same or different data, or to current or outdated data.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): Refer to "Read Consistent Locators" for information on assigning one lob locator to another.
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OciLobAssign, OciLobIsEqual.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- ALLOCATE, LOB ASSIGN.
• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- SELECT, LOB ASSIGN
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > copy
• Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

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

PL/SQL (DBMS_LOB Package): Copying a LOB Locator

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lcopyloc.sql */

/* Procedure lobAssign_proc is not part of the DBMS_LOB package. */

/* copying a lob locator */

CREATE OR REPLACE PROCEDURE lobAssign_proc (Lob_loc1 IN OUT BLOB) IS 
   /* Note that Lob_loc1 can be a persistent or temporary LOB */
  Lob_loc2    BLOB; 
BEGIN 
   DBMS_OUTPUT.PUT_LINE('------------ LOB ASSIGN EXAMPLE ------------');
   /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the
    * lob at this point in time. */ 
  Lob_loc2 := Lob_loc1; 
  /* When you write some data to the lob through Lob_loc1, Lob_loc2 will not 
   * see the newly written data whereas Lob_loc1 will see the new data. */ 
END; 
/
SHOW ERRORS;

C (OCI): Copying a LOB Locator

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lcopyloc.c */

/* Copying a LOB locator */
#include <oratypes.h>
#include <lobdemo.h>
void assignLOB_proc(OCILobLocator *Lob_loc1, OCILobLocator *Lob_loc2,
                    OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, 
                    OCIStmt *stmthp)
{
  boolean       isEqual;
  printf ("----------- OCILobAssign Demo --------------\n");

  /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the LOB
     at this point in time. 
   */
  printf(" assign the src locator to dest locator\n");
  checkerr (errhp, OCILobLocatorAssign(svchp, errhp, Lob_loc1, &Lob_loc2)); 

  /* When you write some data to the LOB through Lob_loc1, Lob_loc2 will not
     see the newly written data whereas Lob_loc1 will see the new data. 
   */

  /* Call OCI to see if the two locators are Equal */

  printf (" check if Lobs are Equal.\n");
  checkerr (errhp, OCILobIsEqual(envhp, Lob_loc1, Lob_loc2, &isEqual));
  if (isEqual)
  {
    /* ... The LOB locators are Equal */
    printf(" Lob Locators are equal.\n");
  }
  else
  {
    /* ... The LOB locators are not Equal */
    printf(" Lob Locators are NOT Equal.\n");
  }
  /* Note that in this example, the LOB locators will be Equal */
  return;
}

COBOL (Pro*COBOL): Copying a LOB Locator

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lcopyloc.pco

     * COPYING A LOB LOCATOR
       IDENTIFICATION DIVISION.
       PROGRAM-ID. COPY-LOCATOR.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  DEST          SQL-BLOB.
       01  SRC           SQL-BLOB.
           EXEC SQL INCLUDE SQLCA END-EXEC.
       PROCEDURE DIVISION.
       COPY-BLOB-LOCATOR.
           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 :DEST END-EXEC.
           EXEC SQL ALLOCATE :SRC END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL 
                SELECT AD_COMPOSITE INTO :SRC
                FROM PRINT_MEDIA 
                WHERE PRODUCT_ID = 2268 AND AD_ID = 21001 FOR UPDATE
           END-EXEC.
            EXEC SQL LOB ASSIGN :SRC TO :DEST END-EXEC.

      * When you write data to the LOB through SRC, DEST will
      * not see the newly written data

       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :DEST END-EXEC.
           EXEC SQL FREE :SRC 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++): Copying a LOB Locator

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lcopyloc.pc */

/* Copying a LOB locator */
#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 lobAssign_proc()
{
  OCIBlobLocator *Lob_loc1, *Lob_loc2;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL ALLOCATE :Lob_loc2;
  EXEC SQL SELECT ad_composite INTO :Lob_loc1
           FROM Print_media 
           WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE;
  /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the
     LOB at this point in time: */
  EXEC SQL LOB ASSIGN :Lob_loc1 TO :Lob_loc2;
  /* When you write some data to the LOB through Lob_loc1, Lob_loc2 will not
     see the newly written data whereas Lob_loc1 will see the new data: */
}

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

Visual Basic (OO4O): Copying a LOB Locator

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lcopyloc.bas

'Copying a LOB locator
Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Print_media ORDER BY product_id, ad_id ", ORADYN_DEFAULT)
Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value
Set OraAdPhotoClone = OraAdPhoto1.Clone
 
OraDyn.MoveNext
 
'Copy 1000 bytes of LOB values OraAdPhotoClone to OraAdPhotol at OraAdPhotol
'offset 100: 
OraDyn.Edit
OraAdPhoto1.Copy OraAdPhotoClone, 1000, 100
OraDyn.Update

Java (JDBC): Copying a LOB Locator

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lcopyloc.java */

// Copying a LOB locator
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_104
{
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
    {
     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);
   }

   // When you write data to LOB through lob_loc1,lob_loc2 will not see changes
   lob_loc2 = lob_loc1;
   stmt.close();
   conn.commit();
   conn.close();

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

Equality: Checking If One LOB Locator Is Equal to Another

This section describes how to determine whether one LOB locator is equal to another. If two locators are equal, then this means that they refer to the same version of the LOB data.

Syntax

Use the following syntax references for each programmatic environment:

• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OciLobAssign, OciLobIsEqual.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL): There is no applicable syntax reference for this use case.
• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB ASSIGN
• Visual Basic (OO4O): There is no applicable syntax reference for this use case.
• Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

• C (OCI): Checking If One LOB Locator Is Equal to Another
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): No example is provided with this release.
• C/C++ (Pro*C/C++): Checking If One LOB Locator Is Equal to Another
• Visual Basic (OO4O): No example is provided with this release.
• Java (JDBC): Checking If One LOB Locator Is Equal to Another

C (OCI): Checking If One LOB Locator Is Equal to Another

/* This is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lequal.c */

/* Seeing if One LOB locator is Equal to Another */
#include <oratypes.h>
#include <lobdemo.h>
void locatorIsEqual_proc(OCILobLocator *Lob_loc1, OCILobLocator *Lob_loc2,
                         OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, 
                         OCIStmt *stmthp)
{
  boolean       isEqual;
  OCILobLocator *Lob_loc3;
  
  printf ("----------- OCILobIsEqual Demo --------------\n");


  /* Call OCI to see if the two locators are Equal: */
  printf (" check if Lob1 and Lob2 are Equal.\n");
  checkerr (errhp, OCILobIsEqual(envhp, Lob_loc1, Lob_loc2, &isEqual));

  if (isEqual)
  {
    /* ... The LOB locators are Equal: */
    printf(" Lob Locators are equal.\n");
  }
  else
  {
    /* ... The LOB locators are not Equal: */
    printf(" Lob Locators are NOT Equal.\n");
  }

  (void)OCIDescriptorAlloc((void *) envhp, (void **) &Lob_loc3,
                             (ub4)OCI_DTYPE_LOB, (size_t) 0, (void **) 0);

  /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the LOB
     at this point in time: */
  printf(" assign the src locator to dest locator\n");
  checkerr (errhp, OCILobLocatorAssign(svchp, errhp, Lob_loc1, &Lob_loc3)); 

  /* When you write some data to the LOB through Lob_loc1, Lob_loc2 will not
     see the newly written data whereas Lob_loc1 will see the new data: */

  /* Call OCI to see if the two locators are Equal: */
  printf (" check if Lob1 and Lob3 are Equal.\n");
  checkerr (errhp, OCILobIsEqual(envhp, Lob_loc1, Lob_loc3, &isEqual));

  if (isEqual)
  {
    /* ... The LOB locators are Equal: */
    printf(" Lob Locators are equal.\n");
  }
  else
  {
    /* ... The LOB locators are not Equal: */
    printf(" Lob Locators are NOT Equal.\n");
  }

  OCIDescriptorFree((void *)Lob_loc3, (ub4)OCI_DTYPE_LOB); 
  
  return;
}

C/C++ (Pro*C/C++): Checking If One LOB Locator Is Equal to Another

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lequal.pc */

/* Seeing if One LOB locator is equal to another */
/* Pro*C/C++ does not provide a mechanism to test the equality of two
   locators. But you can use OCI directly. Two locators can be
   compared to determine whether or not they are equal as this example
   demonstrates: */

#include <sql2oci.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 LobLocatorIsEqual_proc()
{
  OCIBlobLocator *Lob_loc1, *Lob_loc2;
  OCIEnv *oeh;
  boolean isEqual;
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL ALLOCATE :Lob_loc2;
  EXEC SQL SELECT ad_composite INTO Lob_loc1
           FROM Print_media 
           where product_id = 3060 AND ad_id = 11001 FOR UPDATE;
  /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the
     LOB at this point in time: */
  EXEC SQL LOB ASSIGN :Lob_loc1 TO :Lob_loc2;
  /* When you write some data to the lob through Lob_loc1, Lob_loc2 will
     not see the newly written data whereas Lob_loc1 will see the new
     data. */
  /* Get the OCI Environment Handle using a SQLLIB Routine: */
  (void) SQLEnvGet(SQL_SINGLE_RCTX, &oeh);
  /* Call OCI to see if the two locators are Equal: */
  (void) OCILobIsEqual(oeh, Lob_loc1, Lob_loc2, &isEqual);
  if (isEqual)
    printf("The locators are equal\n");
  else
    printf("The locators are not equal\n");
  /* Note that in this example, the LOB locators will be Equal */
  EXEC SQL FREE :Lob_loc1;
  EXEC SQL FREE :Lob_loc2;
}

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

Java (JDBC): Checking If One LOB Locator Is Equal to Another

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lequal.java */

// Seeing if one LOB locator is equal to another
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_108
{
 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
    {
      BLOB lob_loc1 = null;
      BLOB lob_loc2 = null;
      ResultSet rset = stmt.executeQuery (
          "SELECT ad_photo FROM Print_media 
            WHERE product_id = 3106 AND ad_id = 13001");
   if (rset.next())
   {
     lob_loc1 = ((OracleResultSet)rset).getBLOB (1);
   }

   // When you write data to LOB through lob_loc1,lob_loc2 will not see the 
changes: 
   lob_loc2 = lob_loc1;

   // Note that in this example, the Locators will be equal.
   if (lob_loc1.equals(lob_loc2))  
   {
      // The Locators are equal: 
      System.out.println("Locators are equal");
   }
   else 
   {
      // The Locators are different: 
      System.out.println("Locators are NOT equal");
   }

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

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

Determining Whether LOB Locator Is Initialized

This section describes how to determine whether a LOB locator is initialized.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this use case.
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- OciLobLocatorIsInit.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL): There is no applicable syntax reference for this use case.
• C/C++ (Pro*C/C++) Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives". See C(OCI), OciLobLocatorIsInit.
• Visual Basic (OO4O): There is no applicable syntax reference for this use case.
• Java (JDBC): There is no applicable syntax reference for this use case.

Examples

Examples are provided in the following programmatic environments:

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

C (OCI): Determining Whether a LOB Locator Is Initialized

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/linit.c */

/* Seeing if a LOB locator is initialized */
#include <oratypes.h>
#include <lobdemo.h>

void isInitializedLOB_proc(OCILobLocator *Lob_loc1, OCILobLocator *Lob_loc2,
                           OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, 
                           OCIStmt *stmthp)
{
  boolean       isInitialized;
  printf ("----------- OCILobLocatorIsInit Demo --------------\n");
  /* Determine if the locator 1 is Initialized -: */
  checkerr(errhp, OCILobLocatorIsInit(envhp, errhp, Lob_loc1, &isInitialized));
                                    /* IsInitialized should return TRUE here */
  printf(" for Locator 1, isInitialized = %d\n", isInitialized);

  /* Determine if the locator 2 is Initialized -: */
  checkerr(errhp, OCILobLocatorIsInit(envhp, errhp, Lob_loc2, &isInitialized));
                                    /* IsInitialized should return FALSE here */
  printf(" for Locator 2, isInitialized = %d\n", isInitialized);

  return;
}

C/C++ (Pro*C/C++): Determining Whether a LOB Locator Is Initialized

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/linit.pc */

/* Seeing if a LOB locator is initialized */
/* Pro*C/C++ has no form of embedded SQL statement to determine if a LOB
   locator is initialized. Locators in Pro*C/C++ are initialized when they
   are allocated through the EXEC SQL ALLOCATE statement. An example
   can be written that uses embedded SQL and the OCI as is shown here: */

#include <sql2oci.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 LobLocatorIsInit_proc()
{
  OCIBlobLocator *Lob_loc;
  OCIEnv *oeh;
  OCIError *err;
  boolean isInitialized;

  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 = 2056 AND ad_id =  12001;
  /* Get the OCI Environment Handle using a SQLLIB Routine: */
  (void) SQLEnvGet(SQL_SINGLE_RCTX, &oeh);
  /* Allocate the OCI Error Handle: */
  (void) OCIHandleAlloc((dvoid *)oeh, (dvoid **)&err,
                        (ub4)OCI_HTYPE_ERROR, (ub4)0, (dvoid **)0);
  /* Use the OCI to determine if the locator is Initialized: */
  (void) OCILobLocatorIsInit(oeh, err, Lob_loc, &isInitialized);
  if (isInitialized)
    printf("The locator is initialized\n");
  else
    printf("The locator is not initialized\n");
  /* Note that in this example, the locator is initialized */
  /* Deallocate the OCI Error Handle: */
  (void) OCIHandleFree(err, OCI_HTYPE_ERROR);
  /* Release resources held by the locator: */
  EXEC SQL FREE :Lob_loc;
}

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

Appending to a LOB

This section describes how to write-append the contents of a buffer to a LOB.

Usage Notes

Note the following issues regarding usage of this API.

Writing Singly or Piecewise

The writeappend operation writes a buffer to the end of a LOB.

For OCI, the buffer can be written to the LOB in a single piece with this call; alternatively, it can be rendered piecewise using callbacks or a standard polling method.

Writing Piecewise: When to Use Callbacks or Polling

If the value of the piece parameter is OCI_FIRST_PIECE, then data must be provided through callbacks or polling.

• If a callback function is defined in the cbfp parameter, then this callback function will be called to get the next piece after a piece is written to the pipe. Each piece will be written from bufp.
• If no callback function is defined, then OCILobWriteAppend2() returns the OCI_NEED_DATA error code. The application must call OCILobWriteAppend2() again to write more pieces of the LOB. In this mode, the buffer pointer and the length can be different in each call if the pieces are of different sizes and from different locations. A piece value of OCI_LAST_PIECE terminates the piecewise write.

Locking the Row Prior to Updating

Prior to updating a LOB value using the PL/SQL DBMS_LOB package or the OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of an SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs.

For more details on the state of the locator after an update, refer to "Updating LOBs Through Updated Locators".

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "DBMS_LOB" -- WRITEAPPEND
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCILobWriteAppend2
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB WRITE APPEND.
• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB WRITE APPEND
• Visual Basic (OO4O): No syntax reference is provided with this release.
• Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Writing to the End of (Appending to) a LOB
• C (OCI): Writing to the End of (Appending to) a LOB
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Writing to the End of (Appending to) a LOB
• C/C++ (Pro*C/C++): Writing to the End of (Appending to) a LOB
• Visual Basic (OO4O): No example is provided with this release.
• Java (JDBC): Writing to the End of (Append-Write to) a LOB

PL/SQL (DBMS_LOB Package): Writing to the End of (Appending to) a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lwriteap.sql */

/* Procedure lobWriteAppend_proc is not part of the DBMS_LOB package: */

/* writing to the end of lob (write append) */

CREATE OR REPLACE PROCEDURE lobWriteAppend_proc (Lob_loc IN OUT BLOB) IS
   /* Note: Lob_loc can be a persistent or temporary LOB */
   Buffer     RAW(32767);
   Amount     Binary_integer := 4;
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB WRITEAPPEND EXAMPLE ------------');
   /* Fill the buffer with data... */
   Buffer := hextoraw('ab2daa44');
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Append the data from the buffer to the end of the LOB: */
   DBMS_LOB.WRITEAPPEND(Lob_loc, Amount, Buffer);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE(Lob_loc);
END;
/
SHOW ERRORS;

C (OCI): Writing to the End of (Appending to) a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lwriteap.c */

/* Write-appending to a LOB */
#include <oratypes.h>
#include <lobdemo.h>
void writeAppendLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                  OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)
{
  oraub8 amt;
  oraub8 offset;
  sword retval;
  ub1 bufp[MAXBUFLEN];
  oraub8 buflen;

  printf ("----------- OCILobWriteAppend Demo --------------\n"); 
  /* Open the CLOB: */
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE)));

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

  /* Fill bufp with data: */
  /* Write the data from the buffer at the end of the LOB: */
  printf(" write-append data to the frame Lob\n");
  checkerr (errhp, OCILobWriteAppend2(svchp, errhp, Lob_loc, NULL,
                             &amt, (void *)bufp, buflen,
                             OCI_ONE_PIECE, (void *)0,
                             (OCICallbackLobWrite2) 0,
                             0, SQLCS_IMPLICIT));

  /* Closing the CLOB is mandatory if you have opened it: */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));
  return;
}

COBOL (Pro*COBOL): Writing to the End of (Appending to) a LOB

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lwriteap.pco

      * WRITE-APPENDING TO A LOB
       IDENTIFICATION DIVISION.
       PROGRAM-ID. WRITE-APPEND-BLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  BLOB1         SQL-BLOB.
       01  AMT           PIC S9(9) COMP.
       01  BUFFER        PIC X(32767) VARYING.
           EXEC SQL VAR BUFFER IS LONG RAW (32767) END-EXEC.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.
        
       PROCEDURE DIVISION.
       WRITE-APPEND-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 WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL SELECT AD_COMPOSITE INTO :BLOB1
                FROM PRINT_MEDIA 
                WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 FOR UPDATE END-EXEC.
 
      * Open the target LOB: 
           EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC.

      *    Populate AMT here: 
           MOVE 5 TO AMT.
           MOVE "2424242424" to BUFFER.

      * Append the source LOB to the destination LOB: 
           EXEC SQL LOB WRITE APPEND :AMT FROM :BUFFER INTO :BLOB1 END-EXEC.
           EXEC SQL LOB CLOSE :BLOB1 END-EXEC.

       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++): Writing to the End of (Appending to) a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lwriteap.pc */

/* Write-appending to a LOB */
#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 128

void LobWriteAppend_proc()
{
  OCIBlobLocator *Lob_loc;
  int Amount = BufferLength;
  /* Amount == BufferLength so only a single WRITE is needed: */
  char Buffer[BufferLength];
  /* Datatype equivalencing is mandatory for this datatype: */
  EXEC SQL VAR Buffer IS RAW(BufferLength);
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT ad_composite INTO :Lob_loc
           FROM Print_media 
           WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE;
  /* Opening the LOB is Optional: */
  EXEC SQL LOB OPEN :Lob_loc;
  memset((void *)Buffer, 1, BufferLength);  
  /* Write the data from the buffer at the end of the LOB: */
  EXEC SQL LOB WRITE APPEND :Amount FROM :Buffer INTO :Lob_loc;
  /* Closing the LOB is mandatory if it has been opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

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

Java (JDBC): Writing to the End of (Append-Write to) a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lwriteap.java */

// Write-appending to a LOB
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_126
{
  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
    {
     BLOB dest_loc = null;
     byte[] buf = new byte[MAXBUFSIZE];
     long pos = 0;
     ResultSet rset = stmt.executeQuery (
        "SELECT ad_composite FROM Print_media 
         WHERE product_id = 2056 AND ad_id = 12001 FOR UPDATE");
   if (rset.next())
   {
     dest_loc = ((OracleResultSet)rset).getBLOB (1);
   }

   // Start writing at the end of the LOB. ie. append: 
   pos = dest_loc.length();
   
   // fill buf with contents to be written: 
   buf = (new String("Hello World")).getBytes();

   // Write the contents of the buffer into position pos of the output LOB: 
   dest_loc.putBytes(pos, buf);

   // Close all streams and handles: 
   stmt.close();
   conn.commit();
   conn.close();

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

Writing Data to a LOB

This section describes how to write the contents of a buffer to a LOB.

Usage Notes

Note the following issues regarding usage of this API.

Stream Write

The most efficient way to write large amounts of LOB data is to use OCILobWrite2() with the streaming mechanism enabled using polling or a callback. If you know how much data will be written to the LOB, then specify that amount when calling OCILobWrite2(). This ensures that LOB data on the disk is contiguous. Apart from being spatially efficient, the contiguous structure of the LOB data will make for faster reads and writes in subsequent operations.

Chunksize

A chunk is one or more Oracle blocks. As noted previously, 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/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.

Use a Multiple of Chunksize to Improve Write Performance

You will improve performance if you run write requests using a multiple of this chunk size. The reason for this is that the LOB chunk is versioned for every write operation. If all writes are done on a chunk basis, then no extra or excess versioning is incurred or duplicated. If it is appropriate for your application, then you should batch writes until you have enough for an entire chunk instead of issuing several LOB write calls that operate on the same LOB chunk.

Locking the Row Prior to Updating

Prior to updating a LOB value using the PL/SQL DBMS_LOB package or OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs.

For more details on the state of the locator after an update, refer to "Updating LOBs Through Updated Locators".

Using DBMS_LOB.WRITE() to Write Data to a BLOB

When you are passing a hexadecimal string to DBMS_LOB.WRITE() to write data to a BLOB, use the following guidelines:

• The amount parameter should be <= the buffer length parameter
• The length of the buffer should be ((amount*2) - 1). This guideline exists because the two characters of the string are seen as one hexadecimal character (and an implicit hexadecimal-to-raw conversion takes place), that is, every two bytes of the string are converted to one raw byte.

The following example is correct:

declare
   blob_loc  BLOB;
   rawbuf RAW(10);
   an_offset INTEGER := 1;
   an_amount BINARY_INTEGER := 10;
begin
   select blob_col into blob_loc from a_table
where id = 1;
   rawbuf := '1234567890123456789';
   dbms_lob.write(blob_loc, an_amount, an_offset,
rawbuf);
   commit;
end;

Replacing the value for 'an_amount' in the previous example with the following values, yields error message, ora_21560:

    an_amount BINARY_INTEGER := 11;

or

    an_amount BINARY_INTEGER := 19;

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "DBMS_LOB" -- WRITE
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCILobWrite2.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB WRITE.
• C/C++ (Pro*C/C++): Pro*C/C++ 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, copyfromfile
• Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

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

PL/SQL (DBMS_LOB Package): Writing Data to a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lwrite.sql */

/* Procedure writeDataToLOB_proc is not part of the DBMS_LOB package: */

/* writing data to a lob */

CREATE or REPLACE PROCEDURE writeDataToLOB_proc (Lob_loc IN OUT BLOB) IS
   /* Note: Lob_loc can be a persistent or temporary LOB */
   Buffer          RAW(32767);
   Amount          BINARY_INTEGER := 10;
   OptimalAmount   BINARY_INTEGER;
   Position        INTEGER := 1;
   i               INTEGER;
   Chunk_size      INTEGER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB WRITE EXAMPLE ------------');
   /* For persistent LOBs, for each DBMS_LOB call, 
    * we write data in multiples of chunksize,
    * and write on chunk boundaries. This ensures best performance */
    Chunk_size := DBMS_LOB.GETCHUNKSIZE(Lob_loc);
    OptimalAmount := (Amount/Chunk_size) * Chunk_size;
    if OptimalAmount = 0 then
      OptimalAmount := Amount;
    end if;

   /* Fill a buffer */
   Buffer := hextoraw(lpad('4', Amount*4, '4'));
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   FOR i IN 1..3 LOOP
      Amount := OptimalAmount;
      DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer);
      /* Fill the buffer with more data to write to the LOB: */
      Position := Position + Amount;
   END LOOP;
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
END;
/
SHOW ERRORS;

C (OCI): Writing Data to a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lwrite.c */

/* Writing data to a LOB.
   Using OCI you can write arbitrary amounts of data
   to an Internal LOB in either a single piece or in multiple pieces using
   streaming with standard polling. A dynamically allocated Buffer  
   holds the data being written to the LOB. */
#include <oratypes.h>
#include <lobdemo.h>
void writeLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                   OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp, 
                   oraub8 amt_to_write)
{
  /* <total amount of data to write to the CLOB in bytes> */
  oraub8 amt;
  oraub8 offset;
  unsigned int remainder, nbytes;
  boolean last;
  ub1 bufp[MAXBUFLEN];
  sword err;
  oraub8 lob_len;

  printf ("----------- OCILobWrite Demo --------------\n");
  /* Open the CLOB */
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE)));
  if (amt_to_write > MAXBUFLEN)
    nbytes = MAXBUFLEN;   /* We will use streaming through standard polling */
  else
    nbytes = (unsigned int)amt_to_write;  /* Only a single write is required */

  /* Fill in all a's */
  memset((void *)bufp, 'a', MAXBUFLEN);
  
  /* Fill the buffer with nbytes worth of data */
  remainder = (unsigned int)(amt_to_write - nbytes);

  /* Setting Amount to 0 streams the data until use specifies OCI_LAST_PIECE */
  amt = 0;                                  
  offset = 1;  

  /* check lob length before update */
  checkerr (errhp, OCILobGetLength2(svchp, errhp, Lob_loc, &lob_len));
  printf(" Lob length before update = %d\n", (ub4)lob_len);


  if (0 == remainder)
  {
    printf(" writing the Lob data in one-piece mode\n");
    amt = (oraub8)nbytes;
    /* Here, (amt_to_write <= MAXBUFLEN ) so we can write in one piece */
    checkerr (errhp, OCILobWrite2(svchp, errhp, Lob_loc, &amt, NULL,
                                  offset, (void *)bufp, nbytes,
                                  OCI_ONE_PIECE, (void *)0,
                                  (OCICallbackLobWrite2)0,
                                  0, SQLCS_IMPLICIT));
  }    
  else
  {
    printf(" writing the Lob data in streaming polling mode\n");
    /* Here (amt_to_write > MAXBUFLEN ) so we use streaming 
       through standard polling */
    /* write the first piece.  Specifying first initiates polling. */
    err =  OCILobWrite2(svchp, errhp, Lob_loc, &amt, NULL,
                        offset, (void *)bufp, nbytes,
                        OCI_FIRST_PIECE, (void *)0, 
                        (OCICallbackLobWrite2) 0,
                        0, SQLCS_IMPLICIT);

    printf("  1st call. amt returned = %d\n", (ub4)amt);
    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 = OCILobWrite2(svchp, errhp, Lob_loc, &amt, NULL,
                           offset, (void *)bufp, nbytes,
                           OCI_LAST_PIECE, (void *)0, 
                           (OCICallbackLobWrite2) 0,
                           0, SQLCS_IMPLICIT);
        printf("  last call. amt returned = %d\n", (ub4)amt);
        if (err != OCI_SUCCESS)
          checkerr(errhp, err);
      }
      else
      {
        err = OCILobWrite2(svchp, errhp, Lob_loc, &amt, NULL,
                           offset, (void *)bufp, nbytes,
                           OCI_NEXT_PIECE, (void *)0, 
                           (OCICallbackLobWrite2) 0,
                           0, SQLCS_IMPLICIT);
        printf("  subsequent call. amt returned = %d\n", (ub4)amt);

        if (err != OCI_NEED_DATA)
          checkerr (errhp, err);
      }
      /* Determine how much is left to write */
      remainder = remainder - nbytes;
    } while (!last);
  }

  /* check lob length after update */
  checkerr (errhp, OCILobGetLength2(svchp, errhp, Lob_loc, &lob_len));
  printf(" Lob length after update = %d\n", (ub4)lob_len);

  /* At this point, (remainder == 0) */
  
  /* Closing the LOB is mandatory if you have opened it */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));

  return;
 }

COBOL (Pro*COBOL): Writing Data to a LOB

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lwrite.pco

     * WRITING DATA TO A LOB
       IDENTIFICATION DIVISION.
       PROGRAM-ID. WRITE-CLOB.
       ENVIRONMENT DIVISION.
       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
          SELECT INFILE
             ASSIGN TO "datfile.dat"
             ORGANIZATION IS SEQUENTIAL.
       DATA DIVISION.
       FILE SECTION.
 
       FD INFILE
          RECORD CONTAINS 5 CHARACTERS.
       01 INREC      PIC X(5).

       WORKING-STORAGE SECTION.
       01  CLOB1          SQL-CLOB.
       01  BUFFER         PIC X(5) VARYING.
       01  AMT            PIC S9(9) COMP VALUES 321.
       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.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
     
           EXEC SQL INCLUDE SQLCA END-EXEC.

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

      * Open the input file: 
           OPEN INPUT INFILE.
      * 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 = 3106 AND AD_ID = 13001 FOR UPDATE
           END-EXEC.

      * 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 5 TO BUFFER-LEN.
           IF (END-OF-FILE = "Y")
              EXEC SQL 
                   LOB WRITE ONE :AMT FROM :BUFFER
                   INTO :CLOB1 AT :OFFSET 
              END-EXEC
           ELSE
              DISPLAY "LOB WRITE FIRST: ", BUFFER-ARR
              EXEC SQL 
                 LOB WRITE FIRST :AMT FROM :BUFFER 
                 INTO :CLOB1 AT :OFFSET
              END-EXEC.

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

         WRITE-TO-CLOB.
           IF ( END-OF-FILE = "N")
              MOVE INREC TO BUFFER-ARR.
              MOVE 5 TO BUFFER-LEN.
              DISPLAY "LOB WRITE NEXT: ", BUFFER-ARR(1:BUFFER-LEN).
              EXEC SQL 
                  LOB WRITE NEXT :AMT FROM :BUFFER INTO :CLOB1
              END-EXEC.
              PERFORM READ-NEXT-RECORD.

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

       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++): Writing Data to a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lwrite.pc */

/* Writing data to 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
   of in multiple pieces using a Streaming Mechanism that utilizes standard
   polling.  A dynamically allocated Buffer holds the data being
   written to the LOB: */
#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 1024

void writeDataToLOB_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 CLOB: */
  EXEC SQL LOB OPEN :Lob_loc READ WRITE;
  Total = Amount = (multiple * BufferLength);
  if (Total > BufferLength)
    nbytes = BufferLength;   /* Using streaming through 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 we use streaming through 
      /* standard polling to 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;    /* Here, (remainder <= BufferLength) */
              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 CLOB: */
  EXEC SQL LOB CLOSE :Lob_loc;
  /* Free resources held by the Locator: */
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O):Writing Data to a LOB

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lwrite.bas

'Writing data to a LOB
'There are two ways of writing a lob, with orablob.write or 
orablob.copyfromfile

'Using the OraBlob.Write mechanism
Dim OraDyn As OraDynaset, OraAdPhoto As OraBlob, amount_written%, chunksize%, 
curchunk() As Byte

chunksize = 32767
Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Print_media", ORADYN_DEFAULT)
Set OraAdPhoto = OraDyn.Fields("ad_photo").Value

fnum = FreeFile
Open "c:\tmp\keyboard_3106_13001" For Binary As #fnum

OraAdPhoto.offset = 1
OraAdPhoto.pollingAmount = LOF(fnum)
remainder = LOF(fnum)

Dim piece As Byte
Get #fnum, , curchunk
 
OraDyn.Edit
  
piece = ORALOB_FIRST_PIECE
OraAdPhoto.Write curchunk, chunksize, ORALOB_FIRST_PIECE
 
While OraAdPhoto.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
   OraAdPhoto.Write curchunk, chunksize, piece
    
Wend

OraDyn.Update

'Using the OraBlob.CopyFromFile mechanism

Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT)
Set OraAdPhoto = OraDyn.Fields("ad_photo").Value

Oradyn.Edit
OraAdPhoto.CopyFromFile "c:\keyboardphoto3106.jpg"
Oradyn.Update

Java (JDBC): Writing Data to a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lwrite.java */

//Writing data to a LOB
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_126
{
  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
    {
     BLOB dest_loc = null;
     byte[] buf = new byte[MAXBUFSIZE];
     long pos = 0;
     ResultSet rset = stmt.executeQuery (
        "SELECT ad_composite FROM Print_media 
         WHERE product_id = 2056 AND ad_id = 12001 FOR UPDATE");
   if (rset.next())
   {
     dest_loc = ((OracleResultSet)rset).getBLOB (1);
   }

   // Start writing at the end of the LOB. ie. append: 
   pos = dest_loc.length();
   
   // fill buf with contents to be written: 
   buf = (new String("Hello World")).getBytes();

   // Write the contents of the buffer into position pos of the output LOB: 
   dest_loc.putBytes(pos, buf);

   // Close all streams and handles: 
   stmt.close();
   conn.commit();
   conn.close();

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

Trimming LOB Data

This section describes how to trim a LOB to the size you specify.

Usage Notes

Note the following issues regarding usage of this API.

Locking the Row Prior to Updating

Prior to updating a LOB value using the PL/SQL DBMS_LOB package, or OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of:

• A SELECT FOR UPDATE statement in SQL and PL/SQL programs.
• An OCI pin or lock function in OCI programs.

For more details on the state of the locator after an update, refer to "Updating LOBs Through Updated Locators".

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "DBMS_LOB" -- TRIM
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCILobTrim2.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB TRIM.
• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- LOB TRIM
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > trim
• Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

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

PL/SQL (DBMS_LOB Package): Trimming LOB Data

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/ltrim.sql */

/* Procedure trimLOB_proc is not part of the DBMS_LOB package: */

/* trimming lob data */

CREATE OR REPLACE PROCEDURE trimLOB_proc (Lob_loc IN OUT BLOB) IS
    /* Note: Lob_loc can be a persistent or temporary LOB */
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB TRIM EXAMPLE ------------');
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Trim the LOB data: */
   DBMS_LOB.TRIM(Lob_loc,3);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
   DBMS_OUTPUT.PUT_LINE('Trim succeeded');
/* Exception handling: */
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Trim failed');
END;
/
SHOW ERRORS;

C (OCI): Trimming LOB Data

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/ltrim.c */

/* Trimming LOB data */
#include <oratypes.h>
#include <lobdemo.h>
void trimLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                  OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)
{
  oraub8 trimLength;

  printf ("----------- OCILobTrim Demo --------------\n");

  /* Open the CLOB */
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE)));

  /* Trim the LOB to its new length */
  trimLength = 200;                      /* <New truncated length of the LOB>*/

  printf (" trim the lob to %d bytes\n", (ub4)trimLength);
  checkerr (errhp, OCILobTrim2(svchp, errhp, Lob_loc, trimLength ));

  /* Closing the CLOB is mandatory if you have opened it */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));
}

COBOL (Pro*COBOL): Trimming LOB Data

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/ltrim.pco

     * Trimming LOB data
       IDENTIFICATION DIVISION.
       PROGRAM-ID. TRIM-CLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  CLOB1          SQL-CLOB.
       01  NEW-LEN        PIC S9(9) COMP.
      * Define the source and destination position and location: 
       01  SRC-POS        PIC S9(9) COMP.
       01  DEST-POS       PIC S9(9) COMP.
       01  SRC-LOC        PIC S9(9) COMP.
       01  DEST-LOC       PIC S9(9) COMP.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.
        
       PROCEDURE DIVISION.
       TRIM-CLOB.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Allocate and initialize the CLOB locators: 
           EXEC SQL ALLOCATE :CLOB1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC.
           EXEC SQL 
                SELECT PM.AD_SOURCETEXT INTO :CLOB1
                FROM PRINT_MEDIA PM
                WHERE PM.PRODUCT_ID = 3060 
                AND AD_ID = 11001 FOR UPDATE END-EXEC.
 
      * Open the CLOB: 
           EXEC SQL LOB OPEN :CLOB1 READ WRITE END-EXEC.

      * Move some value to  NEW-LEN: 
           MOVE 3 TO NEW-LEN.
           EXEC SQL 
                LOB TRIM :CLOB1 TO :NEW-LEN END-EXEC.

           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++): Trimming LOB Data

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/ltrim.pc */

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

void trimLOB_proc()
{
  voiced_typ_ref *vt_ref;
  voiced_typ *vt_typ;
  OCIClobLocator *Lob_loc;
  unsigned int Length, trimLength;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL ALLOCATE :vt_ref;
  EXEC SQL ALLOCATE :vt_typ;

  /* Retrieve the REF using Associative SQL */
           EXEC SQL SELECT PMtab.ad_sourctext INTO :vt_ref
           FROM Print_media PMtab 
           WHERE PMtab.product_id = 3060 AND ad_id = 11001 FOR UPDATE;

  /* Dereference the Object using the Navigational Interface */
  EXEC SQL OBJECT DEREF :vt_ref INTO :vt_typ FOR UPDATE;
  Lob_loc = vt_typ->script; 

  /* Opening the LOB is Optional */
  EXEC SQL LOB OPEN :Lob_loc READ WRITE;
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length;
  printf("Old length was %d\n", Length);
  trimLength = (unsigned int)(Length / 2);

  /* Trim the LOB to its new length */
  EXEC SQL LOB TRIM :Lob_loc TO :trimLength;

  /* Closing the LOB is mandatory if it has been opened */
  EXEC SQL LOB CLOSE :Lob_loc;

  /* Mark the Object as Modified (Dirty) */
  EXEC SQL OBJECT UPDATE :vt_typ;

  /* Flush the changes to the LOB in the Object Cache */
  EXEC SQL OBJECT FLUSH :vt_typ;

  /* Display the new (modified) length */
  EXEC SQL SELECT Mtab.Voiced_ref.Script INTO :Lob_loc
           FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 2;
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length;
  printf("New length is now %d\n", Length);

  /* Free the Objects and the LOB Locator */
  EXEC SQL FREE :vt_ref;
  EXEC SQL FREE :vt_typ;
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Trimming LOB Data

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/ltrim.bas

'Trimming LOB data
Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT)
Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value

OraDyn.Edit
OraAdPhoto1.Trim 10
OraDyn.Update

Java (JDBC): Trimming LOB Data

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/ltrim.java */

// Trimming BLOBs and CLOBs.
// You need to import the java.sql package to use JDBC 
import java.sql.*; 

// You need to import the oracle.sql package to use oracle.sql.BLOB 
import oracle.sql.*; 

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

    String url = "jdbc:oracle:oci8:@"; 
    try { 
      String url1 = System.getProperty("JDBC_URL"); 
      if (url1 != null) 
        url = url1; 
    } catch (Exception e) { 
      // If there is any security exception, ignore it 
      // and use the default 
    } 

    // Connect to the database 
    Connection conn = 
      DriverManager.getConnection (url, "pm", "pm"); 
    // It's faster when auto commit is off 
    conn.setAutoCommit (false); 

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

    try 
    { 
      stmt.execute ("drop table basic_lob_table"); 
    } 
    catch (SQLException e) 
    { 
      // An exception could be raised here if the table did not exist already. 
    } 

    // Create a table containing a BLOB and a CLOB 
    stmt.execute ("create table basic_lob_table (x varchar2 (30), b blob, c 
clob)"); 

    // Populate the table 
    stmt.execute ("insert into basic_lob_table values ('one', 
'010101010101010101010101010101', 'onetwothreefour')"); 

    // Select the lobs 
    ResultSet rset = stmt.executeQuery ("select * from basic_lob_table"); 
    while (rset.next ()) 
    { 
      // Get the lobs 
      BLOB blob = (BLOB) rset.getObject (2); 
      CLOB clob = (CLOB) rset.getObject (3); 

      // Show the original lob length 
      System.out.println ("Open the lobs"); 
      System.out.println ("blob.length()="+blob.length()); 
      System.out.println ("clob.length()="+clob.length()); 

      // Trim the lobs 
      System.out.println ("Trim the lob to length = 6"); 
      blob.trim (6); 
      clob.trim (6); 

      // Show the lob length after trim() 
      System.out.println ("Open the lobs"); 
      System.out.println ("blob.length()="+blob.length()); 
      System.out.println ("clob.length()="+clob.length()); 
    } 

    // Close the ResultSet 
    rset.close (); 

    // Close the Statement 
    stmt.close (); 

    // Close the connection 
    conn.close (); 
  } 
} 

Here is the old way of trimming LOB data, using DBMS_LOB.TRIM:

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/oldltrim.java */

// Trimming 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.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_141
{
  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 lob_loc = null;

     ResultSet rset = stmt.executeQuery (
        "SELECT pm.ad_finaltext FROM Print_media pm
            WHERE pm.product_id = 2056 AND ad_id = 12001 FOR UPDATE");
   if (rset.next())
   {
     lob_loc = ((OracleResultSet)rset).getCLOB (1);
   }

   // Open the LOB for READWRITE: 
       OracleCallableStatement cstmt = (OracleCallableStatement)
          conn.prepareCall ("BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READWRITE);  
          END;");
   cstmt.setCLOB(1, lob_loc);
   cstmt.execute();

   // Trim the LOB to length of 400: 
   cstmt = (OracleCallableStatement) 
      conn.prepareCall ("BEGIN DBMS_LOB.TRIM(?, 400); END;");
   cstmt.setCLOB(1, lob_loc);
   cstmt.execute();

   // Close the LOB: 
   cstmt = (OracleCallableStatement) conn.prepareCall (
      "BEGIN DBMS_LOB.CLOSE(?); END;");
   cstmt.setCLOB(1, lob_loc);
   cstmt.execute();

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

Erasing Part of a LOB

This section describes how to erase part of a LOB.

Usage Notes

Note the following issues regarding usage of this API.

Locking the Row Prior to Updating

Prior to updating a LOB value using the PL/SQL DBMS_LOB package or OCI, you must lock the row containing the LOB. While INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using the OCI pin or lock function in OCI programs.

For more details on the state of the locator after an update, refer to"Updating LOBs Through Updated Locators".

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBM_LOB Package): PL/SQL Packages and Types Reference "DBMS_LOB" -- ERASE
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCILobErase2.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB ERASE.
• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- LOB ERASE
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > erase
• Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

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

PL/SQL (DBMS_LOB Package): Erasing Part of a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lerase.sql */

/* Procedure eraseLOB_proc is not part of the DBMS_LOB package: */

/* erasing part of a lob */

CREATE OR REPLACE PROCEDURE eraseLOB_proc (Lob_loc IN OUT BLOB) IS
   /* Note: Lob_loc can be a persistent or temporary LOB */
   Amount         INTEGER := 3000;
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB ERASE EXAMPLE ------------');
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Erase the data: */
   DBMS_LOB.ERASE(Lob_loc, Amount, 4);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
   DBMS_OUTPUT.PUT_LINE('Erase succeeded');
/* Exception handling: */
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Erase failed');
END;
/
SHOW ERRORS;

C (OCI): Erasing Part of a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lerase.c */

/* Erasing part of a LOB (persistent LOBs) */
#include <oratypes.h>
#include <lobdemo.h>
void eraseLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                  OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)
{
  oraub8 amount = 300;
  oraub8 offset = 10;

  printf ("----------- OCILobErase Demo --------------\n");     
  /* Open the CLOB: */
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE)));

  /* Erase the data starting at the specified Offset: */
  printf(" erase %d bytes at offset %d from the Lob\n", (ub4)amount, 
(ub4)offset); 
  checkerr (errhp, OCILobErase2(svchp, errhp, Lob_loc, &amount, offset ));

  /* Closing the CLOB is mandatory if you have opened it: */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));

  return;
}

COBOL (Pro*COBOL): Erasing Part of a LOB

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lerase.pco

     * ERASING PART OF A LOB
       IDENTIFICATION DIVISION.
       PROGRAM-ID. ERASE-BLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BLOB1          SQL-BLOB.
       01  AMT            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP.
           EXEC SQL INCLUDE SQLCA END-EXEC.
        
       PROCEDURE DIVISION.
       ERASE-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 WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL 
                SELECT AD_PHOTO INTO :BLOB1
                FROM PRINT_MEDIA PM
                WHERE PM.PRODUCT_ID = 2268 AND AD_ID = 21001 FOR UPDATE
           END-EXEC.
 
      * Open the BLOB: 
           EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC.

      * Move some value to AMT and OFFSET: 
           MOVE 2 TO AMT.
           MOVE 1 TO OFFSET.
           EXEC SQL 
                LOB ERASE :AMT FROM :BLOB1 AT :OFFSET END-EXEC.
           EXEC SQL LOB CLOSE :BLOB1 END-EXEC.
       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++): Erasing Part of a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lerase.pc */

/* Erasing part of a LOB */
#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 eraseLob_proc()
{
  OCIBlobLocator *Lob_loc;
  int Amount = 5;
  int Offset = 5;

  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 FOR 
UPDATE;
  /* Opening the LOB is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ WRITE;
  /* Erase the data starting at the specified Offset: */
  EXEC SQL LOB ERASE :Amount FROM :Lob_loc AT :Offset;
  printf("Erased %d bytes\n", Amount);
  /* Closing the LOB is mandatory if it has been opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Erasing Part of a LOB

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lerase.bas

'Erasing part of a LOB
Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob

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

Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Print_media ORDER BY product_
id", ORADYN_DEFAULT)
Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value
'Erase 10 bytes begining from the 100th byte: 
OraDyn.Edit
OraAdPhoto1.Erase 10, 100
OraDyn.Update

Java (JDBC): Erasing Part of a LOB

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lerase.java */

// Erasing part of a LOB
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_145
{
  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
    {
     BLOB lob_loc = null;
     int eraseAmount = 30;
     ResultSet rset = stmt.executeQuery (
        "SELECT ad_photo FROM Print_media 
         WHERE product_id = 2056 AND ad_id = 12001 FOR UPDATE");
   if (rset.next())
   {
     lob_loc = ((OracleResultSet)rset).getBLOB (1);
   }

   // Open the LOB for READWRITE: 
       OracleCallableStatement cstmt = (OracleCallableStatement)
          conn.prepareCall ("BEGIN DBMS_LOB.OPEN(?, "
                            +"DBMS_LOB.LOB_READWRITE); END;");
   cstmt.setBLOB(1, lob_loc);
   cstmt.execute();

   // Erase eraseAmount bytes starting at offset 2000: 
   cstmt = (OracleCallableStatement) 
      conn.prepareCall ("BEGIN DBMS_LOB.ERASE(?, ?, 1); END;");
   cstmt.registerOutParameter (1, OracleTypes.BLOB);
   cstmt.registerOutParameter (2, Types.INTEGER);
   cstmt.setBLOB(1, lob_loc);
   cstmt.setInt(2, eraseAmount);
   cstmt.execute();
   lob_loc = cstmt.getBLOB(1);
   eraseAmount = cstmt.getInt(2);

   // Close the LOB: 
   cstmt = (OracleCallableStatement) conn.prepareCall (
      "BEGIN DBMS_LOB.CLOSE(?); END;");
   cstmt.setBLOB(1, lob_loc);
   cstmt.execute();

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

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

Enabling LOB Buffering

This section describes how to enable LOB buffering.

Usage Notes

Enable LOB buffering when you are performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.

For more information, refer to "LOB Buffering Subsystem".

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL: This API is not available in any supplied PL/SQL packages.
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCIEnableLobBuffering, OCIDisableLobBuffering, OCIFlushBuffer
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB ENABLE BUFFERING.
• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- LOB ENABLE BUFFERING
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > EnableBuffering
• Java (JDBC): There is no applicable syntax reference for this use case.

Examples

Examples are provided in the following programmatic environments:

• C (OCI): No example is provided with this release. Using this API is similar to that described in the example, "Disabling LOB Buffering".
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Enabling LOB Buffering
• C/C++ (Pro*C/C++): Enabling LOB Buffering
• Visual Basic (OO4O): Enabling LOB Buffering

COBOL (Pro*COBOL): Enabling LOB Buffering

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lenbuf.pco

     * ENABLING LOB BUFFERING
       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOB-BUFFERING.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BLOB1          SQL-BLOB.
       01  BUFFER         PIC X(10).
       01  AMT            PIC S9(9) COMP.
           EXEC SQL VAR BUFFER IS RAW(10) END-EXEC.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       LOB-BUFFERING.

           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 ad_photo INTO :BLOB1
                FROM PRINT_MEDIA 
                WHERE PRODUCT_ID = 3060 AND AD_ID = 11001
                FOR UPDATE END-EXEC.
 
      * Open the BLOB and enable buffering: 
           EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC.
           EXEC SQL 
                LOB ENABLE BUFFERING :BLOB1 END-EXEC.
        
      * Write some data to the BLOB: 
           MOVE "242424" TO BUFFER.
           MOVE 3 TO AMT.
           EXEC SQL 
                LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC.

           MOVE "212121" TO BUFFER.
           MOVE 3 TO AMT.
           EXEC SQL 
                LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC.

      * Now flush the buffered writes: 
           EXEC SQL LOB FLUSH BUFFER :BLOB1 END-EXEC.
           EXEC SQL LOB DISABLE BUFFERING :BLOB1 END-EXEC.
           EXEC SQL LOB CLOSE :BLOB1 END-EXEC.

       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++): Enabling LOB Buffering

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lenbuf.pc */

/* Enabling LOB buffering
#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 256

void enableBufferingLOB_proc()
{
  OCIBlobLocator *Lob_loc;
  int Amount = BufferLength;
  int multiple, Position = 1;
  /* Datatype equivalencing is mandatory for this datatype: */
  char Buffer[BufferLength];
  EXEC SQL VAR Buffer is RAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Initialize the LOB: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT ad_composite INTO :Lob_loc
             FROM Print_media 
             WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE;

  /* Enable use of the LOB Buffering Subsystem: */
  EXEC SQL LOB ENABLE BUFFERING :Lob_loc;
  memset((void *)Buffer, 0, BufferLength);
  for (multiple = 0; multiple < 8; multiple++)
    {
      /* Write data to the LOB: */
      EXEC SQL LOB WRITE ONE :Amount
                    FROM :Buffer INTO :Lob_loc AT :Position;
      Position += BufferLength;
    }
  /* Flush the contents of the buffers and Free their resources: */
  EXEC SQL LOB FLUSH BUFFER :Lob_loc FREE;
  /* Turn off use of the LOB Buffering Subsystem: */
  EXEC SQL LOB DISABLE BUFFERING :Lob_loc;
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Enabling LOB Buffering

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lenbuf.bas

'Enabling LOB buffering (persistent LOBs)
Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Print_media ORDER BY product_id", ORADYN_DEFAULT)
Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value

'Enable buffering: 
OraAdPhoto1.EnableBuffering

Flushing the Buffer

This section describes how to flush the LOB buffer.

Usage Notes

Enable buffering when performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.

For more information, refer to "LOB Buffering Subsystem".

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this use case.
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCIEnableLobBuffering, OCIDisableLobBuffering, OCIFlushBuffer.
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB FLUSH BUFFER.
• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB FLUSH BUFFER.
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > FlushBuffer.
• Java (JDBC): There is no applicable syntax reference for this use case.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): No example is provided with this release.
• C (OCI): No example is provided with this release. Using this API is similar to that described in the example, "Disabling LOB Buffering".
• C++ (OCCI): No example is provided with this release.
• COBOL (Pro*COBOL): Flushing the Buffer
• C/C++ (Pro*C/C++): Flushing the Buffer
• Visual Basic (OO4O): No example is provided with this release.
• Java (JDBC): No example is provided with this release.

COBOL (Pro*COBOL): Flushing the Buffer

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lflbuf.pco

     * Flushing the LOB buffer (persistent LOBs)
       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOB-BUFFERING.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BLOB1          SQL-BLOB.
       01  BUFFER         PIC X(10).
       01  AMT            PIC S9(9) COMP.
           EXEC SQL VAR BUFFER IS RAW(10) END-EXEC.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       LOB-BUFFERING.

           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 AD_PHOTO INTO :BLOB1
                FROM PRINT_MEDIA 
                WHERE PRODUCT_ID = 2056 AND AD_ID = 12001 FOR UPDATE
           END-EXEC.
 
      * Open the BLOB and enable buffering: 
           EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC.
           EXEC SQL LOB ENABLE BUFFERING :BLOB1 END-EXEC.
        
      * Write some data to the BLOB: 
           MOVE "242424" TO BUFFER.
           MOVE 3 TO AMT.
           EXEC SQL 
                LOB WRITE :AMT FROM :BUFFER INTO :BLOB1
           END-EXEC.

           MOVE "212121" TO BUFFER.
           MOVE 3 TO AMT.
           EXEC SQL 
                LOB WRITE :AMT FROM :BUFFER INTO :BLOB1
           END-EXEC.

      * Now flush the buffered writes: 
           EXEC SQL LOB FLUSH BUFFER :BLOB1 END-EXEC.
           EXEC SQL LOB DISABLE BUFFERING :BLOB1 END-EXEC.
           EXEC SQL LOB CLOSE :BLOB1 END-EXEC.
       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++): Flushing the Buffer

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lflbuf.pc */

/* Flushing the LOB Buffer (persistent LOBs)
#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 256

void flushBufferingLOB_proc()
{
  OCIBlobLocator *Lob_loc;
  int Amount = BufferLength;
  int multiple, Position = 1;

  /* Datatype equivalencing is mandatory for this datatype: */
  char Buffer[BufferLength];
  EXEC SQL VAR Buffer is RAW(BufferLength);
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();

  /* Allocate and Initialize the LOB: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT Sound INTO :Lob_loc
             FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE;

  /* Enable use of the LOB Buffering Subsystem: */
  EXEC SQL LOB ENABLE BUFFERING :Lob_loc;
  memset((void *)Buffer, 0, BufferLength);
  for (multiple = 0; multiple < 8; multiple++)
    {
      /* Write data to the LOB: */
      EXEC SQL LOB WRITE ONE :Amount
                    FROM :Buffer INTO :Lob_loc AT :Position;
      Position += BufferLength;
    }
  /* Flush the contents of the buffers and Free their resources: */
  EXEC SQL LOB FLUSH BUFFER :Lob_loc FREE;
  /* Turn off use of the LOB Buffering Subsystem: */
  EXEC SQL LOB DISABLE BUFFERING :Lob_loc;
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Lob_loc;
}

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

Disabling LOB Buffering

This section describes how to disable LOB buffering.

Usage Notes

Enable buffering when performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.

For more information, refer to"LOB Buffering Subsystem"

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this use case.
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCIEnableLobBuffering, OCIDisableLobBuffering, OCIFlushBuffer
• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB DISABLE BUFFER.
• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB DISABLE BUFFER
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > DisableBuffering
• Java (JDBC): There is no applicable syntax reference for this use case.

Examples

Examples are provided in the following programmatic environments:

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

C (OCI): Disabling LOB Buffering

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/ldisbuf.c */

/* Disabling LOB buffering (persistent LOBs) */
#include <oratypes.h>
#include <lobdemo.h>
void LOBBuffering_proc(OCILobLocator *Lob_loc, OCIEnv *envhp,
                       OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp)
{
  ub4 amt;
  ub4 offset;
  sword retval;
  ub1 bufp[MAXBUFLEN];
  ub4 buflen;
  printf ("----------- OCI LOB Buffering Demo --------------\n");

  /* Open the CLOB: */
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE)));

  /* Enable LOB Buffering: */
  printf (" enable LOB buffering\n");
  checkerr (errhp, OCILobEnableBuffering(svchp, errhp, Lob_loc));

  printf (" write data to LOB\n");

  /* Write data into the LOB: */
  amt    = sizeof(bufp);
  buflen = sizeof(bufp);
  offset = 1;

  checkerr (errhp, OCILobWrite (svchp, errhp, Lob_loc, &amt, 
                                offset, (void *)bufp, buflen,
                                OCI_ONE_PIECE, (void *)0, 
                                (sb4 (*)(void*,void*,ub4*,ub1 *))0,
                                0, SQLCS_IMPLICIT));

  /* Flush the buffer: */
  printf(" flush the LOB buffers\n");
  checkerr (errhp, OCILobFlushBuffer(svchp, errhp, Lob_loc,
                                     (ub4)OCI_LOB_BUFFER_FREE));

  /* Disable Buffering: */
  printf (" disable LOB buffering\n");
  checkerr (errhp, OCILobDisableBuffering(svchp, errhp, Lob_loc));

  /* Subsequent LOB WRITEs will not use the LOB Buffering Subsystem: */

  /* Closing the CLOB is mandatory if you have opened it: */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));

  return;
}

COBOL (Pro*COBOL): Disabling LOB Buffering

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/ldisbuf.pco

     * DISABLING LOB BUFFERING (PERSISTENT LOBS)
       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOB-BUFFERING.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BLOB1          SQL-BLOB.
       01  BUFFER         PIC X(10).
       01  AMT            PIC S9(9) COMP.
           EXEC SQL VAR BUFFER IS RAW(10) END-EXEC.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       LOB-BUFFERING.

           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 SOUND INTO :BLOB1
                FROM MULTIMEDIA_TAB 
                WHERE CLIP_ID = 1 FOR UPDATE
           END-EXEC.
 
      * Open the BLOB and enable buffering: 
           EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC.
           EXEC SQL 
                LOB ENABLE BUFFERING :BLOB1 
           END-EXEC.
        
      * Write some data to the BLOB: 
           MOVE "242424" TO BUFFER.
           MOVE 3 TO AMT.
           EXEC SQL LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC.

           MOVE "212121" TO BUFFER.
           MOVE 3 TO AMT.
           EXEC SQL LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC.

      * Now flush the buffered writes: 
           EXEC SQL LOB FLUSH BUFFER :BLOB1 END-EXEC.
           EXEC SQL LOB DISABLE BUFFERING :BLOB1 END-EXEC.
           EXEC SQL LOB CLOSE :BLOB1 END-EXEC.

       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++): Disabling LOB Buffering

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/ldisbuf.pc */

/* Disabling LOB buffering (persistent LOBs) */
#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 256

void disableBufferingLOB_proc()
{
  OCIBlobLocator *Lob_loc;
  int Amount = BufferLength;
  int multiple, Position = 1;
  /* Datatype equivalencing is mandatory for this datatype: */
  char Buffer[BufferLength];
  EXEC SQL VAR Buffer is RAW(BufferLength);
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  
/* Allocate and Initialize the LOB: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT ad_photo INTO :Lob_loc
             FROM Print_media 
             WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE;
  /* Enable use of the LOB Buffering Subsystem: */
  EXEC SQL LOB ENABLE BUFFERING :Lob_loc;
  memset((void *)Buffer, 0, BufferLength);
  for (multiple = 0; multiple < 7; multiple++)
    {
      /* Write data to the LOB: */
      EXEC SQL LOB WRITE ONE :Amount
                    FROM :Buffer INTO :Lob_loc AT :Position;
      Position += BufferLength;
    }
  /* Flush the contents of the buffers and Free their resources: */
  EXEC SQL LOB FLUSH BUFFER :Lob_loc FREE;
  /* Turn off use of the LOB Buffering Subsystem: */
  EXEC SQL LOB DISABLE BUFFERING :Lob_loc;
  /* Write APPEND can only be done when Buffering is Disabled: */
  EXEC SQL LOB WRITE APPEND ONE :Amount FROM :Buffer INTO :Lob_loc;
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Disabling LOB Buffering

' This file is installed in the following path when you install
' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/ldisbuf.bas

'Disabling LOB buffering (persistent LOBs)
Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT)

Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value
'Disable buffering: 
OraAdPhoto1.DisableBuffering

Determining Whether a LOB instance Is Temporary

This section describes how to determine whether a LOB instance is temporary.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB): PL/SQL Packages and Types Reference "DBMS_LOB" -- ISTEMPORARY, FREETEMPORARY
• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" -- LOB Functions, OCILobIsTemporary
• COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB DESCRIBE, ISTEMPORARY.
• C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB DESCRIBE...ISTEMPORARY
• Visual Basic (OO4O): There is no applicable syntax reference for this use case.
• Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Determining Whether a LOB Is Temporary
• C (OCI): Determining Whether a LOB Is Temporary
• COBOL (Pro*COBOL): Determining Whether a LOB Is Temporary
• C/C++ (Pro*C/C++): Determining Whether a LOB Is Temporary
• Visual Basic (OO4O): No example is provided with this release.
• Java (JDBC): Determining Whether a BLOB Is Temporary
• Java (JDBC): Determining Whether a CLOB Is Temporary.

PL/SQL (DBMS_LOB Package): Determining Whether a LOB Is Temporary

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/listemp.sql */

/* Procedure isTempLob_proc is not part of the DBMS_LOB package: */

/* seeing if lob is temporary. */

CREATE or REPLACE PROCEDURE isTempLob_proc(Lob_loc IN OUT BLOB) IS
   /* Note: Lob_loc can be persistent or temporary LOB */
BEGIN
   DBMS_OUTPUT.PUT_LINE('------------ LOB ISTEMPORARY EXAMPLE ------------');
   /* Check to make sure that the locator is pointing to a temporary LOB */
    IF DBMS_LOB.ISTEMPORARY(Lob_loc) = 1 THEN
        DBMS_OUTPUT.PUT_LINE('Input locator is a temporary LOB locator');
    ELSE
        /* Print an error: */
        DBMS_OUTPUT.PUT_LINE('Input locator is not a temporary LOB locator');
    END IF;
END;
/
SHOW ERRORS;

C (OCI): Determining Whether a LOB Is Temporary

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/listemp.c */

/* Checking if a LOB is temporary.
   This function frees a temporary LOB. It takes a locator as an argument,   
   checks to see if it is a temporary LOB. If it is, the function frees 
   the temporary LOB. Otherwise, it prints out a message saying the locator 
   was not a temporary LOB locator. This function returns 0 if it 
   completes successfully, -1 otherwise: */ 
#include <oratypes.h>
#include <lobdemo.h>
void isTempLOBAndFree_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError 
*errhp, 
                           OCISvcCtx *svchp, OCIStmt *stmthp)
{
  boolean is_temp;
  is_temp = FALSE;
  printf ("-----------  OCILobIsTemporary and OCILobFreeTemporary Demo \
--------------\n");
  checkerr (errhp, OCILobIsTemporary(envhp, errhp, Lob_loc, &is_temp));

  if(is_temp)
  {
      checkerr(errhp, (OCILobFreeTemporary(svchp, errhp, Lob_loc)));
      printf("Temporary LOB freed\n");

  }
  else
  {
      printf("locator is not a temporary LOB locator\n");
  }

}

COBOL (Pro*COBOL): Determining Whether a LOB Is Temporary

      * This file is installed in the following path when you install
      * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/listemp.pco

     * Checking if a LOB is temporary.
       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-ISTEMP.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  TEMP-BLOB      SQL-BLOB.
       01  IS-TEMP        PIC S9(9) COMP.
       01  ORASLNRD       PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       CREATE-TEMPORARY.
           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 :TEMP-BLOB END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

      * Check if the LOB is temporary: 
           EXEC SQL 
                LOB DESCRIBE :TEMP-BLOB
                GET ISTEMPORARY INTO :IS-TEMP
           END-EXEC.
     
           IF IS-TEMP = 1
      *      Logic for a temporary LOB goes here
             DISPLAY "LOB is temporary."
           ELSE
      *      Logic for a persistent LOB goes here.
             DISPLAY "LOB is persistent."
           END-IF.

           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (Pro*C/C++): Determining Whether a LOB Is Temporary

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/listemp.pc */

/* Checking if a LOB is temporary.
#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 lobIsTemp_proc()
{
  OCIBlobLocator *Temp_loc;
  int isTemporary = 0;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Determine if the Locator is a Temporary LOB Locator: */
  EXEC SQL LOB DESCRIBE :Temp_loc GET ISTEMPORARY INTO :isTemporary;
   
  /* Note that in this example, isTemporary should be 1 (TRUE) */
  if (isTemporary)
    printf("Locator is a Temporary LOB locator\n");
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Temp_loc;
else
    printf("Locator is not a Temporary LOB locator \n");
   }

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

Java (JDBC): Determining Whether a BLOB Is Temporary

To see if a BLOB is temporary, the JDBC application can either use the isTemporary instance method to determine whether the current BLOB object is temporary, or pass the BLOB object to the static isTemporary method to determine whether the specified BLOB object is temporary. These two methods are defined as follows:

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/listempb.java */

/** 
* Checking if a BLOB is temporary.
* Returns true if LOB locator points to a temporary BLOB, False if not. 
* @param lob the BLOB to test. 
* @returns true if LOB locator points to a temporary BLOB, False if not. 
*/ 
  public static boolean isTemporary (BLOB lob) throws SQLException 

/** 
 * Returns true if LOB locator points to a temporary BLOB, False if not. 
 * @returns true if LOB locator points to a temporary BLOB, False if not. 
 */ 
   public boolean isTemporary () throws SQLException

  //The usage example is--

  BLOB blob = ... 

  // See if the BLOB is temporary 
  boolean isTemporary = blob.isTemporary (); 

  // See if the specified BLOB is temporary 
  boolean isTemporary2 = BLOB.isTemporary(blob);

This JDBC API replaces previous workarounds that use
DBMS_LOB.isTemporary().

Java (JDBC): Determining Whether a CLOB Is Temporary

To determine whether a CLOB is temporary, the JDBC application can either use the isTemporary instance method to determine whether the current CLOB object is temporary, or pass the CLOB object to the static isTemporary method. These two methods are defined as follows:

/* This file is installed in the following path when you install */
/* the database: $ORACLE_HOME/rdbms/demo/lobs/java/listempc.java */

/** 
 * Checking if LOB is temporary.
 * Return true if the LOB locator points to a temporary CLOB, False if it 
 * does not. 
 * 
 * @param lob the BLOB to test. 
 * @return true if the LOB locator points to a temporary CLOB, False if it 
 *         does not. 
 */ 
  public static boolean isTemporary (CLOB lob) throws SQLException 

  /** 
   * Return true if the LOB locator points to a temporary CLOB, False if it 
   * does not. 
   * 
   * @return true if the LOB locator points to a temporary CLOB, False if it 
   *         does not. 
   */ 
    public boolean isTemporary () throws SQLException 

  //The usage example is--

  CLOB clob = ... 

  // See if the CLOB is temporary 
  boolean isTemporary = clob.isTemporary (); 

  // See if the specified CLOB is temporary 
  boolean isTemporary2 = CLOB.isTemporary(clob);

Converting a BLOB to a CLOB

You can convert a BLOB instance to a CLOB using the PL/SQL procedure DBMS_LOB.CONVERTTOCLOB. This technique is convenient if you have character data stored in binary format that you want to store in a CLOB. You specify the character set of the binary data when calling this procedure. See PL/SQL Packages and Types Reference for details on syntax and usage of this procedure.

Converting a CLOB to a BLOB

You can convert a CLOB instance to a BLOB instance using the PL/SQL procedure DBMS_LOB.CONVERTTOBLOB. This technique is a convenient way to convert character data to binary data using LOB APIs. See PL/SQL Packages and Types Reference for details on syntax and usage of this procedure.