External LOBs (BFILEs), 18 of 41

Opening a BFILE with OPEN

Figure 12-14 Use Case Diagram: Opening a BFILE with OPEN

Text description of adl12bf8.gif follows

Text description of the illustration adl12bf8.gif

See Also:

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

Purpose

This procedure describes how to open a BFILE with OPEN.

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" -- OPEN
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations", for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobOpen, OCILobClose
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
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
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > Open, and > OBJECTS > OraDynaset > METHODS > MoveLast
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

These examples open a Lincoln_photo in operating system file PHOTO_DIR. Examples are provided in the following six programmatic environments:

Examples

PL/SQL: Opening a BFILE with OPEN

/* Note that the example procedure openBFILE_procTwo is not part of the 
   DBMS_LOB package:  */
CREATE OR REPLACE PROCEDURE openBFILE_procTwo IS 
  File_loc    BFILE := BFILENAME('PHOTO_DIR', 'Lincoln_photo'); 
BEGIN 
   /* Open the BFILE: */ 
   DBMS_LOB.OPEN (File_loc, DBMS_LOB.LOB_READONLY);
   /* ... Do some processing: */ 
   DBMS_LOB.CLOSE(File_loc);
END;

C (OCI): Opening a BFILE with OPEN

void BfileFileOpen(envhp, errhp, svchp, stmthp)
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp;
{ 

      OCILobLocator *bfile_loc; 
 
     /* Allocate the locator descriptor */ 
     (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc,  
                 (ub4) OCI_DTYPE_FILE,  
                 (size_t) 0, (dvoid **) 0);
     
     /* Set the Bfile Locator Information */ 
     checkerr(errhp, (OCILobFileSetName(envhp, errhp, &bfile_loc,  
                           (OraText *)"PHOTO_DIR", (ub2)strlen("PHOTO_DIR"),  
                           (OraText *)"Lincoln_photo",  
                           (ub2)strlen("Lincoln_photo"))));  
     checkerr(errhp, OCILobOpen(svchp, errhp, bfile_loc, 
                                (ub1)OCI_FILE_READONLY));
     /* ... Do some processing. */
     checkerr(errhp, OCILobClose(svchp, errhp, bfile_loc));
 
     /* Free the locator descriptor */ 
     OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
}

COBOL (Pro*COBOL): Opening a BFILE with OPEN

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

       IDENTIFICATION DIVISION.
       PROGRAM-ID. OPEN-BFILE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       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.
       OPEN-BFILE.

           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 :SRC-BFILE END-EXEC.

      * Set up the directory and file information: 
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "Washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
      * Assign directory alias and file name to BFILE: 
           EXEC SQL 
                LOB FILE SET :SRC-BFILE 
                DIRECTORY = :DIR-ALIAS, FILENAME = :FNAME END-EXEC.

      * Open the BFILE read only: 
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.

      * Close the LOB: 
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.

      * And free the LOB locator: 
           EXEC SQL FREE :SRC-BFILE 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++): Opening a BFILE with OPEN

This script is also provided in $ORACLE_HOME/rdbms/demo/lobs/proc/fopen

/* In Pro*C/C++ there is only one form of OPEN that is used for OPENing
   BFILEs.  There is no FILE OPEN, only a simple OPEN statement: */

#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 openBFILE_proc()
{
  OCIBFileLocator *Lob_loc;
  char *Dir = "PHOTO_DIR", *Name = "Lincoln_photo";

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Initialize the Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Open the BFILE: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* ... Do some processing: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

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

}

Visual Basic (OO4O) Opening a BFILE with OPEN

Dim OraDyn as OraDynaset, OraPhoto as OraBFile, OraMusic as OraBFile 
Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab",ORADYN_DEFAULT) 
Set OraMusic = OraDyn.Fields("Music").Value
Set OraPhoto = OraDyn.Fields("Photo").Value
 
'Go to the last rowand open Bfile for reading: 
OraDyn.MoveLast 
OraPhoto.Open 'Open Bfile for reading 
'Do some processing:  
OraPhoto.Close

Java (JDBC): Opening a BFILE with OPEN

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 Ex4_41
{
  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");
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
       BFILE src_lob = null;
       ResultSet rset = null;

       rset = stmt.executeQuery (
       "SELECT BFILENAME('PHOTO_DIR', 'Lincoln_photo') FROM DUAL");
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getBFILE (1);

          OracleCallableStatement cstmt = (OracleCallableStatement)
          conn.prepareCall ("begin dbms_lob.open (?,dbms_lob.lob_readonly); 
end;");
          cstmt.registerOutParameter(1,OracleTypes.BFILE);
          cstmt.setBFILE (1, src_lob);
          cstmt.execute();
          src_lob = cstmt.getBFILE(1);
          System.out.println ("the file is now open");
       }

       // Close the BFILE,  statement and connection: 
       src_lob.closeFile();
       stmt.close();
       conn.commit();
       conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}