Checking Out a LOB

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

Text description of the illustration adl10p24.gif

See: "Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations.

Purpose

This procedure describes how to checkout a LOB.

Usage Notes

Streaming Mechanism

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

Syntax

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

• PL/SQL (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages Reference Chapter 18, "DBMS_LOB" -- OPEN, READ, CLOSE.

• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions -- OCILobOpen, OCILobRead, OCILobClose.

• C++ (OCCI): Oracle C++ Interface Programmer's Guide

• COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- LOB READ.

• C/C++ (ProC/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB OPEN, LOB READ

• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > read, copytofile

• Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

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

Here we portray the checkout portion of the scenario: the code lets the user read the CLOB Transcript from the nested table InSeg_ntab which contains interview segments for the purpose of processing in some text editor on the client. The checkin portion of the scenario is described in "Checking In a LOB".

Examples

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

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

• C (OCI): Checking Out a LOB

• C++ (OCCI): No example is provided with this release.

• COBOL (Pro*COBOL): Checking Out a LOB

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

• Visual Basic (OO4O): Checking Out a LOB

• Java (JDBC): Checking Out a LOB

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

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/pls/ichkout.

/* Note that the example procedure checkOutLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE checkOutLOB_proc IS
    Lob_loc     CLOB;
    Buffer      VARCHAR2(32767);
    Amount      BINARY_INTEGER := 32767;
    Position    INTEGER := 2147483647;
BEGIN
   /* Select the LOB: */
   SELECT Intab.Transcript INTO Lob_loc
       FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab
          WHERE Mtab.Clip_ID = 1) Intab
             WHERE Intab.Segment = 1;
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
   LOOP
      DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
      /* Process the buffer: */
      Position := Position + Amount;
   END LOOP;
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('End of data');
END;

C (OCI): Checking Out a LOB

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/oci/ichkout

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

#define MAXBUFLEN 32767
/* Select the locator into a locator variable: */
sb4 select_transcript_locator(Lob_loc, errhp, stmthp, svchp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text  *sqlstmt = 
     (text *) "SELECT Intab.Transcript \
           FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab \
              WHERE Mtab.Clip_ID = 1) Intab \
                  WHERE Intab.Segment = 1";
 OCIDefine *defnp1;
 checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                 (ub4)strlen((char *)sqlstmt),
                                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                                 (dvoid *)&Lob_loc, (sb4)0, 
                                 (ub2) SQLT_CLOB,(dvoid *) 0, 
                                 (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT));
 /* Execute the select and fetch one row: */
 checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                (ub4) OCI_DEFAULT));
 return 0;
}

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

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


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

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

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

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

  return;
}

COBOL (Pro*COBOL): Checking Out a LOB

This script is provided in $ORACLE_HOME/rdbms/demo/lobs/cobol/ichkout

       IDENTIFICATION DIVISION.
       PROGRAM-ID. CHECKOUT.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

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

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

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

You can find this script at $ORACLE_HOME/rdbms/demo/lobs/proc/ichecko

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

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

#define BufferLength 256

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

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

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

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

Visual Basic (OO4O): Checking Out a LOB

You can find this script at $ORACLE_HOME/rdbms/demo/lobs/vbasic/ichkout

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

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

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

'Using OraBlob.CopyToFile mechanism
Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT)
Set OraSound = OraDyn.Fields("Sound").Value

'Read entire BLOB contents
OraSound.CopyToFile "c:\mysound.aud"

Java (JDBC): Checking Out a LOB

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/java/ichkout.

import java.io.InputStream;
import java.io.OutputStream;

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

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

public class Ex2_59
{

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

       ResultSet rset = stmt.executeQuery (
          "SELECT intab.transcript FROM TABLE( "
             +" SELECT mtab.inseg_ntab FROM multimedia_tab mtab "
               +" WHERE mtab.clip_id = 1) intab WHERE intab.segment = 1");
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getCLOB (1);
          in = src_lob.getAsciiStream();
   }

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