Comparing All or Part of Two LOBs

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

Text description of the illustration adl10p30.gif

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

Purpose

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

Usage Notes

Not applicable.

Syntax

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

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

• C (OCI): There is no applicable syntax reference for this use case.

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

• COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- EXECUTE. Also reference PL/SQL DBMS_LOB.COMPARE.

• C/C++ (Pro*C/C++) (Pro*C/C++ Precompiler Programmer's Guide): Appendix F, "Embedded SQL Statements and Directives" -- LOB OPEN, LOB CLOSE. Also reference PL/SQL DBMS_LOB.COMPARE.

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

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

Scenario

The following examples compare two frames from the archival table VideoframesLib_tab to see whether they are different and, depending on the result of the comparison, inserts the Frame into the Multimedia_tab.

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 script is also located at $ORACLE_HOME/rdbms/demo/lobs/pls/icompare.

/* Note that the example procedure compareTwoLOBs_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE compareTwoLOBs_proc IS
    Lob_loc1            BLOB;
    Lob_loc2            BLOB;
    Amount              INTEGER := 32767;
    Retval              INTEGER;
BEGIN
    /* Select the LOB: */
    SELECT Frame INTO Lob_loc1 FROM Multimedia_tab
        WHERE Clip_ID = 1;
    SELECT Frame INTO Lob_loc2 FROM Multimedia_tab
        WHERE Clip_ID = 2;
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc1, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.OPEN (Lob_loc2, DBMS_LOB.LOB_READONLY);
    /* Compare the two frames: */
    retval := DBMS_LOB.COMPARE(Lob_loc1, Lob_loc2, Amount, 1, 1);
    IF retval = 0 THEN
       DBMS_OUTPUT.PUT_LINE('Processing for equal frames');
    ELSE
       DBMS_OUTPUT.PUT_LINE('Processing for non-equal frames');
    END IF;
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc1);
    DBMS_LOB.CLOSE (Lob_loc2);
END;

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

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

       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 FRAME INTO :BLOB1
                FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 1 END-EXEC.
            EXEC SQL 
                SELECT FRAME INTO :BLOB2
                FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 2 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

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

#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 Frame INTO :Lob_loc1
     FROM Multimedia_tab WHERE Clip_ID = 1;
  EXEC SQL SELECT Frame INTO :Lob_loc2
     FROM Multimedia_tab WHERE Clip_ID = 2;
  /* 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

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

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, OraSound1 as OraBLOB, OraSoundClone as OraBLOB

Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT)
Set OraSound1 = OraDyn.Fields("Sound").Value
'Clone it for future reference
Set OraSoundClone = OraSound1.Clone

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

MsgBox CBool(OraSound1.Compare(OraSoundClone, OraSoundClone.size, 1, 1))
 

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

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

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:@", "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 frame FROM multimedia_tab WHERE clip_id = 1");
   if (rset.next())
   {
     lob_loc1 = ((OracleResultSet)rset).getBLOB (1);
   }

       rset = stmt.executeQuery (
          "SELECT frame FROM multimedia_tab WHERE clip_id = 99");
   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();
    }
  }
}