Oracle8i Application Developer's Guide - Large Objects (LOBs)
Release 2 (8.1.6)
Part Number A76940-01

Library
Product
Contents
Index

Go to previous page Go to beginning of chapter Go to next page

External LOBs (BFILEs), 26 of 41

See If a Pattern Exists (instr) in the BFILE

Figure 11-22 Use Case Diagram: See If a Pattern Exists in the BFILE


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 Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

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): See 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: */
   Lob_loc1       BFILE := BFILENAME('PHOTO_DIR', 'RooseveltFDR_photo');
   Lob_loc2       BFILE;
   Retval         INTEGER;
BEGIN
   /* Select the LOB: */
   SELECT Photo INTO Lob_loc2 FROM Multimedia_tab
      WHERE Clip_ID = 3;
   /* Open the BFILEs: */
   DBMS_LOB.OPEN(Lob_loc1, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.OPEN(Lob_loc2, DBMS_LOB.LOB_READONLY);
   Retval := DBMS_LOB.COMPARE(Lob_loc2, Lob_loc1, DBMS_LOB.LOBMAXSIZE, 1, 1);
   /* Close the BFILEs: */
   DBMS_LOB.CLOSE(Lob_loc1);
   DBMS_LOB.CLOSE(Lob_loc2);
END;

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

       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++): See If a Pattern Exists (instr) in the BFILE

/* 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): See 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();
    }
  }
}
Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index