Oracle8i Application Developer's Guide - Large Objects (LOBs) Release 2 (8.1.6) Part Number A76940-01 |
|
External LOBs (BFILEs), 40 of 41
See Also:
"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 which have raised errors. The exception handler should make provisions to close any files that were opened before the occurrence of the exception or abnormal termination.
If this is not done, Oracle will consider these files unclosed.
This procedure describes how to close all BFILEs.
Not applicable.
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:
Not applicable.
/* 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;
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); }
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.
#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;}
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
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(); } } }
|
![]() Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|