Checking If a Pattern Exists (instr) in the BFILE

Figure 12-22 Use Case Diagram: Checking If a Pattern Exists in the BFILE

Text description of the illustration adl12b17.gif

See Also: "Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES).

Purpose

This procedure describes how to see if a pattern exists (instr) in the BFILE.

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 (DBMS_LOB) (Oracle9i Supplied PL/SQL Packages Reference): Chapter 18, "DBMS_LOB" -- INSTR

• C (OCI): A syntax reference is not applicable in this release.

• COBOL (Pro*COBOL) (Pro*COBOL Precompiler Programmer's Guide): Chapter 13, "Large Objects (LOBs)", "LOB Statements" for usage notes. Appendix F, "Embedded SQL and Precompiler Directives" -- LOB OPEN. See PL/SQL DBMS_LOB.INSTR.

• C/C++ (Pro*C/C++) (Pro*C/C++ Precompiler Programmer's Guide): Chapter 16, "Large Objects (LOBs)", "LOB Statements", usage notes. Appendix F, "Embedded SQL Statements and Directives" -- LOB OPEN. See PL/SQL DBMS_LOB.INSTR.

• Visual Basic (OO4O): A syntax reference is not applicable in this release.

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

Scenario

The following examples search for the occurrence of a pattern of audio data within an interview Recording. This assumes that an audio signature is represented by an identifiable bit pattern.

Examples

These examples are provided in the following four programmatic environments:

• PL/SQL (DBMS_LOB Package): Checking If a Pattern Exists (instr) in the BFILE

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

• COBOL (Pro*COBOL): Checking If a Pattern Exists (instr) in the BFILE

• C/C++ (Pro*C/C++): Checking If a Pattern Exists (instr) in the BFILE

• Visual Basic (OO4O): No example is provided with this release.

• Java (JDBC): Checking If a Pattern Exists (instr) in the BFILE

PL/SQL (DBMS_LOB Package): Checking If a Pattern Exists (instr) in the BFILE

/* Note that the example procedure compareBFILEs_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE compareBFILEs_proc IS
   /* Initialize the BFILE locator: */
   File_loc1       BFILE := BFILENAME('PHOTO_DIR', 'RooseveltFDR_photo');
   File_loc2       BFILE;
   Retval         INTEGER;
BEGIN
   /* Select the LOB: */
   SELECT Photo INTO File_loc2 FROM Multimedia_tab
      WHERE Clip_ID = 3;
   /* Open the BFILEs: */
   DBMS_LOB.OPEN(File_loc1, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.OPEN(File_loc2, DBMS_LOB.LOB_READONLY);
   Retval := DBMS_LOB.COMPARE(File_loc2, File_loc1, DBMS_LOB.LOBMAXSIZE, 1, 1);
   /* Close the BFILEs: */
   DBMS_LOB.CLOSE(File_loc1);
   DBMS_LOB.CLOSE(File_loc2);
END;

COBOL (Pro*COBOL): Checking If a Pattern Exists (instr) in the BFILE

This script is also provided in:

$ORACLE_HOME/rdbms/demo/lobs/cobol/fpattern

       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-INSTR.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.

      * The length of pattern was chosen arbitrarily: 
       01  PATTERN        PIC X(4) VALUE "2424".
           EXEC SQL VAR PATTERN IS RAW(4) END-EXEC.
       01  POS            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.
       BFILE-INSTR.

           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.

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC SQL 
                SELECT PHOTO INTO :BFILE1
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 END-EXEC.
      
      * Open the CLOB for READ ONLY: 
           EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.

      * Execute PL/SQL to get INSTR functionality: 
           EXEC SQL EXECUTE
             BEGIN 
               :POS := DBMS_LOB.INSTR(:BFILE1,:PATTERN, 1, 2); END; END-EXEC.
           
           IF POS = 0
      *        Logic for pattern not found here
               DISPLAY "Pattern is not found."
           ELSE
      *        Pos contains position where pattern is found
               DISPLAY "Pattern is found."
           END-IF.

      * Close and free the LOB: 
           EXEC SQL LOB CLOSE :BFILE1 END-EXEC.

       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE 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.
           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++): Checking If a Pattern Exists (instr) in the BFILE

This script is also provided in:

$ORACLE_HOME/rdbms/demo/lobs/proc/fpattern

/* Pro*C lacks an equivalent embedded SQL form of the DBMS_LOB.INSTR()
   function.  However, like SUBSTR() and COMPARE(), Pro*C/C++ can call
   DBMS_LOB.INSTR() from within an anonymous PL/SQL block as shown here: */
#include <sql2oci.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 PatternSize 5

void instringBFILE_proc()
{
  OCIBFileLocator *Lob_loc;
  unsigned int Position = 0;
  int Clip_ID = 3, Segment = 1;
  char Pattern[PatternSize];
  /* Datatype Equivalencing is Mandatory for this Datatype:  */
  EXEC SQL VAR Pattern IS RAW(PatternSize);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  /* Use Dynamic SQL to retrieve the BFILE Locator:  */
  EXEC SQL PREPARE S FROM
    'SELECT Intab.Recording \
       FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab \
          WHERE Clip_ID = :cid) Intab \
             WHERE Intab.Segment = :seg';
  EXEC SQL DECLARE C CURSOR FOR S;
  EXEC SQL OPEN C USING :Clip_ID, :Segment;
  EXEC SQL FETCH C INTO :Lob_loc;
  EXEC SQL CLOSE C;
  /* Open the BFILE: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  memset((void *)Pattern, 0, PatternSize);  
  /* Find the first occurrance of the pattern starting from the
     beginning of the BFILE using PL/SQL: */
  EXEC SQL EXECUTE
    BEGIN
      :Position := DBMS_LOB.INSTR(:Lob_loc, :Pattern, 1, 1);
    END;
  END-EXEC;
  /* Close the BFILE: */
  EXEC SQL LOB CLOSE :Lob_loc;
  if (0 == Position)
    printf("Pattern not found\n");
  else
    printf("The pattern occurs at %d\n", Position);
  EXEC SQL FREE :Lob_loc;
}

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

}

Java (JDBC): Checking If a Pattern Exists (instr) in the BFILE

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 Ex4_70
{

  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
    {
       BFILE lob_loc = null;
       // Pattern to look for within the BFILE: 
       String pattern = new String("children"); 

       ResultSet rset = stmt.executeQuery (
          "SELECT photo FROM multimedia_tab WHERE clip_id = 3");
       if (rset.next())
       {
          lob_loc = ((OracleResultSet)rset).getBFILE (1);
       }

       // Open the LOB: 
       lob_loc.openFile();
       // Search for the location of pattern string in the BFILE, 
       // starting at offset 1: 
      long result = lob_loc.position(pattern.getBytes(), 1);
      System.out.println(
         "Results of Pattern Comparison : " + Long.toString(result));

      // Close the LOB: 
      lob_loc.closeFile();

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

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