Internal Persistent LOBs, 20 of 43

Reading Data from a LOB

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

Text description of adl10p28.gif follows

Text description of the illustration adl10p28.gif

See:

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

Procedure

This procedure describes how to read data from LOBs.

Usage Notes

Stream Read

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

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

Example

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

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

Note:

In DBMS_LOB.READ, the amount can be larger than the size of the data. In PL/SQL, the amount should be less than or equal to the size of the buffer, and the buffer size is limited to 32K.
In OCILobRead, you can specify amount = 4 gigabytes-1, and it will read to the end of the LOB.

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

Chunksize

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

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

Syntax

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

PL/SQL (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages Reference Chapter 18, "DBMS_LOB" -- OPEN, GETCHUNKSIZE, READ, CLOSE.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 7, "Chapter 15, "Relational Functions" -- LOB Functions, OciLobOpen, OciLobRead, OciLobClose.
C++ (OCCI): Oracle C++ 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 READ
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Oraclob > METHODS > read
Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

The examples read data from a single video frame.

Examples

Examples are provided in the following programmatic environments:

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

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

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

C (OCI): Reading Data from a LOB

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

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

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

  text  *sqlstmt = 
     (text *) "SELECT Frame \
                FROM Multimedia_tab m WHERE m.Clip_ID = 3";

  printf("  prepare statement in select_frame_locator\n");
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                  (ub4)strlen((char *)sqlstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
    printf("  OCIDefineByPos in select_frame_locator\n");
  checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                                  (dvoid *)&Lob_loc, (sb4)0, 
                                  (ub2) SQLT_BLOB,(dvoid *) 0, 
                                  (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT));
   /* Execute the select and fetch one row */
   printf("  OCIStmtExecute in select_frame_locator\n");
   checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                  (ub4) OCI_DEFAULT));
       return 0;
}

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

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

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

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

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

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

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

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

This script is provided at $ORACLE_HOME/rdbms/demo/lobs/cobol/iread

       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 "SAMP/SAMP".
           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 FRAME INTO :BLOB1
              FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 1 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

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

#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 Frame INTO :Lob_loc
           FROM Multimedia_tab WHERE Clip_ID = 1;
  /* 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 = "samp/samp";
  EXEC SQL CONNECT :samp;
  readLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Visual Basic (OO4O): Reading Data from a LOB

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

'Using 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, OraStory as OraClob, amount_read%, chunksize%, chunk

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

Java (JDBC): Reading Data from a LOB

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

// 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:@", "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;
     byte buf[] = new byte[MAXBUFSIZE];
     ResultSet rset = stmt.executeQuery (
          "SELECT frame FROM multimedia_tab WHERE clip_id = 1");
     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();
    }
  }
}