External LOBs (BFILEs), 40 of 41

Closing All Open BFILEs with FILECLOSEALL

Figure 12-36 Use Case Diagram: Closing All Open BFILEs

Text description of adl12bf2.gif follows

Text description of the illustration adl12bf2.gif

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

It is the user's responsibility to close any opened file(s) after normal or abnormal termination of a PL/SQL program block or OCI program. So, for instance, for every DBMS_LOB.FILEOPEN() or DBMS_LOB.OPEN() call on a BFILE, there must be a matching DBMS_LOB.FILECLOSE() or DBMS_LOB.CLOSE() call. You should close open files before the termination of a PL/SQL block or OCI program, and also in situations that have raised errors. The exception handler should make provision to close any files that were opened before the occurrence of the exception or abnormal termination.

If this is not done, Oracle considers these files unclosed.

Purpose

This procedure describes how to close all BFILEs.

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" -- FILECLOSEALL
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobFileCloseAll
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 FILE CLOSE ALL
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 FILE CLOSE ALL
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > CloseAll. See also OBJECTS > OraBfile > Examples
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

Not applicable.

Examples

PL/SQL (DBMS_LOB Package): Closing All Open BFiles

/* Note that the example procedure closeAllOpenFilesBFILE_proc is not part of 
   the DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE closeAllOpenFilesBFILE_proc IS
BEGIN
   /* Close all open BFILEs: */
   DBMS_LOB.FILECLOSEALL;
END;

C (OCI): Closing All Open BFiles

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

   OCILobLocator *bfile_loc1;
   OCILobLocator *bfile_loc2;
 
   /* Allocate the locator descriptors */ 
   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc1,
                             (ub4) OCI_DTYPE_FILE,  
                             (size_t) 0, (dvoid **) 0);

   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc2,
                             (ub4) OCI_DTYPE_FILE,  
                             (size_t) 0, (dvoid **) 0);

   checkerr(errhp, OCILobFileSetName(envhp, errhp, &bfile_loc1,
                        (OraText *) "PHOTO_DIR", (ub2) strlen("PHOTO_DIR"),
                        (OraText *) "Lincoln_photo",
                        (ub2) strlen("Lincoln_photo")));

   checkerr(errhp, OCILobFileSetName(envhp, errhp, &bfile_loc2,
                        (OraText *) "PHOTO_DIR", (ub2) strlen("PHOTO_DIR"),
                        (OraText *) "Washington_photo",
                        (ub2) strlen("Washington_photo")));

   checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc1,
                                          (ub1) OCI_LOB_READONLY));

   checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc2,
                                          (ub1) OCI_LOB_READONLY));

   checkerr(errhp, OCILobFileCloseAll(svchp, errhp));

   /* Free the locator descriptor */ 
   OCIDescriptorFree((dvoid *)bfile_loc1, (ub4)OCI_DTYPE_FILE); 
   OCIDescriptorFree((dvoid *)bfile_loc2, (ub4)OCI_DTYPE_FILE); 
}

COBOL (Pro*COBOL): Closing All Open BFiles

This script is also provided in:

$ORACLE_HOME/rdbms/demo/lobs/cobol/fcloseal

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

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.
       01  BFILE2         SQL-BFILE.
       01  DIR-ALIAS1     PIC X(30) VARYING.
       01  FNAME1         PIC X(20) VARYING.
       01  DIR-ALIAS2     PIC X(30) VARYING.
       01  FNAME2         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.
       BFILE-CLOSE-ALL.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate the BFILEs: 
           EXEC SQL ALLOCATE :BFILE1 END-EXEC.
           EXEC SQL ALLOCATE :BFILE2 END-EXEC.

      * Set up the directory and file information: 
           MOVE "AUDIO_DIR" TO DIR-ALIAS1-ARR.
           MOVE 9 TO DIR-ALIAS1-LEN.
           MOVE "Washington_audio" TO FNAME1-ARR.
           MOVE 16 TO FNAME1-LEN.
 
           EXEC SQL 
                LOB FILE SET :BFILE1
                DIRECTORY = :DIR-ALIAS1, FILENAME = :FNAME1 END-EXEC.
           EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.

      * Set up the directory and file information: 
           MOVE "PHOTO_DIR" TO DIR-ALIAS2-ARR.
           MOVE 9 TO DIR-ALIAS2-LEN.
           MOVE "Lincoln_photo" TO FNAME2-ARR.
           MOVE 13 TO FNAME2-LEN.
           EXEC SQL LOB FILE SET :BFILE2
                DIRECTORY = :DIR-ALIAS2, FILENAME = :FNAME2 END-EXEC.
           EXEC SQL LOB OPEN :BFILE2 READ ONLY END-EXEC.

      * Close both BFILE1 and BFILE2: 
           EXEC SQL LOB FILE CLOSE ALL 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++): Closing All Open BFiles

This script is also provided in:

$ORACLE_HOME/rdbms/demo/lobs/proc/fcloseal

#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 closeAllOpenBFILEs_proc()
{
  OCIBFileLocator *Lob_loc1, *Lob_loc2;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL ALLOCATE :Lob_loc2;
  /* Populate the Locators: */
  EXEC SQL SELECT Music INTO :Lob_loc1
           FROM Multimedia_tab WHERE Clip_ID = 3;
  EXEC SQL SELECT Mtab.Voiced_ref.Recording INTO Lob_loc2
           FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3;
  /* Open both BFILEs: */
  EXEC SQL LOB OPEN :Lob_loc1 READ ONLY;
  EXEC SQL LOB OPEN :Lob_loc2 READ ONLY;
  /* Close all open BFILEs: */
  EXEC SQL LOB FILE CLOSE ALL;
  /* Free 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;
  closeAllOpenBFILEs_proc();
  EXEC SQL ROLLBACK WORK RELEASE;

}

Visual Basic (OO4O): Closing All Open BFiles

 Dim OraParameters as OraParameters, OraPhoto as OraBFile 
OraConnection.BeginTrans 
 
Set OraParameters = OraDatabase.Parameters 
 
'Define in out parameter of BFILE type: 
OraParameters.Add "MyPhoto", Null,ORAPARAM_BOTH,ORATYPE_BFILE 
 
'Select the photo BFile for clip_id 1: 
OraDatabase.ExecuteSQL("Begin SELECT Photo INTO :MyPhoto FROM 
Multimedia_tab WHERE Clip_ID = 1; END; " )  
 
'Get the BFile photo column: 
set OraPhoto = OraParameters("MyPhoto").Value 
 
'Open the OraPhoto: 
OraPhoto.Open 
 
'Do some processing on OraPhoto 
 
'Close all the BFILEs associated with OraPhoto: 
OraPhoto.CloseAll

Java (JDBC): Closing All Open BFiles with FILECLOSEALL

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 Ex4_66
{
  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_loc1 = null;
       BFILE lob_loc2 = null;
       ResultSet rset = null;
       OracleCallableStatement cstmt = null;
       rset = stmt.executeQuery (
          "SELECT photo FROM multimedia_tab WHERE clip_id = 3");
       if (rset.next())
       {
          lob_loc1 = ((OracleResultSet)rset).getBFILE (1);
       }

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

       cstmt = (OracleCallableStatement) conn.prepareCall (
          "BEGIN DBMS_LOB.FILEOPEN(?,DBMS_LOB.LOB_READONLY); END;");
       // Open the first LOB: 
       cstmt.setBFILE(1, lob_loc1);
       cstmt.execute();

       cstmt = (OracleCallableStatement) conn.prepareCall (
          "BEGIN DBMS_LOB.FILEOPEN(?,DBMS_LOB.LOB_READONLY); END;");
       // Use the same CallableStatement to open the second LOB: 
       cstmt.setBFILE(1, lob_loc2);
       cstmt.execute();

       lob_loc1.openFile ();
       lob_loc2.openFile ();

       // Compare MAXBUFSIZE bytes starting at the first byte of 
       // both lob_loc1 and lob_loc2: 
       cstmt = (OracleCallableStatement) conn.prepareCall (
          "BEGIN ? := DBMS_LOB.COMPARE(?, ?, ?, 1, 1); END;");
       cstmt.registerOutParameter (1, Types.NUMERIC);
       cstmt.setBFILE(2, lob_loc1);
       cstmt.setBFILE(3, lob_loc2);
       cstmt.setInt(4, MAXBUFSIZE);
       cstmt.execute();
       int result = cstmt.getInt(1);
       System.out.println("Comparison result: " + Integer.toString(result));

       // Close all BFILEs:
       stmt.execute("BEGIN DBMS_LOB.FILECLOSEALL; END;");

       stmt.close();
       cstmt.close();
       conn.commit();
       conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}