| Oracle® Database Application Developer's Guide - Large Objects 10g Release 1 (10.1) Part Number B10796-01 |
|
|
View PDF |
| Oracle® Database Application Developer's Guide - Large Objects 10g Release 1 (10.1) Part Number B10796-01 |
|
|
View PDF |
This chapter describes APIs for operations that use BFILEs. APIs covered in this chapter are listed in Table 15-1.
The following information is given for each operation described in this chapter:
|
Note: LOB APIs do not support loading data into BFILEs. See Using SQL*Loader to Load LOBs for details on techniques for loading data into BFILEs. |
Table 15-1, "Environments Supported for BFILE APIs" indicates which programmatic environments are supported for the APIs discussed in this chapter. The first column describes the operation that the API performs. The remaining columns indicate with "Yes" or "No" whether the API is supported in PL/SQL, OCI, COBOL, Pro*C, Visual Basic (VB), and JDBC.
To access BFILEs use one of the following interfaces:
| See Also:
Chapter 6, "Overview of Supplied LOB APIs" for information about supported environments for accessing BFILEs. |
The DIRECTORY object facilitates administering access and usage of BFILE datatypes (see CREATE DIRECTORY in Oracle Database SQL Reference). A DIRECTORY object specifies a logical alias name for a physical directory on the database server file system under which the file to be accessed is located. You can access a file in the server file system only if granted the required access privilege on DIRECTORY object.
The DIRECTORY object also provides the flexibility to manage the locations of the files, instead of forcing you to hard code the absolute path names of physical files in your applications. A directory object name is used in conjunction with the BFILENAME() function, in SQL and PL/SQL, or the OCILobFileSetName(), in OCI for initializing a BFILE locator.
To associate an operating system file to a BFILE, first create a DIRECTORY object which is an alias for the full path name to the operating system file.
To associate existing operating system files with relevant database records of a particular table use Oracle SQL DML (Data Manipulation Language). For example:
INSERT to initialize a BFILE column to point to an existing file in the server file systemUPDATE to change the reference target of the BFILEBFILE to NULL and then update it later to refer to an operating system file using the BFILENAME() function.OCILobFileSetName() to initialize a BFILE locator variable that is then used in the VALUES clause of an INSERT statement.The following statements associate the files Image1.gif and image2.gif with records having key_value of 21 and 22 respectively. 'IMG' is a DIRECTORY object that represents the physical directory under which Image1.gif and image2.gif are stored.
|
Note: You may need to set up data structures similar to the following for certain examples to work: CREATE TABLE Lob_table ( Key_value NUMBER NOT NULL, F_lob BFILE) |
INSERT INTO Lob_table VALUES (21, BFILENAME('IMG', 'Image1.gif')); INSERT INTO Lob_table VALUES (22, BFILENAME('IMG', 'image2.gif'));
The following UPDATE statement changes the target file to image3.gif for the row with key_value 22.
UPDATE Lob_table SET f_lob = BFILENAME('IMG', 'image3.gif') WHERE Key_value = 22;
BFILENAME() is a built-in function that you use to initialize a BFILE column to point to an external file.
Once physical files are associated with records using SQL DML, subsequent read operations on the BFILE can be performed using PL/SQL DBMS_LOB package and OCI. However, these files are read-only when accessed through BFILES, and so they cannot be updated or deleted through BFILES.
As a consequence of the reference-based semantics for BFILEs, it is possible to have multiple BFILE columns in the same record or different records referring to the same file. For example, the following UPDATE statements set the BFILE column of the row with key_value 21 in lob_table to point to the same file as the row with key_value 22.
UPDATE lob_table SET f_lob = (SELECT f_lob FROM lob_table WHERE key_value = 22) WHERE key_value = 21;
Think of BFILENAME() in terms of initialization -- it can initialize the value for the following:
Using the BFILE datatype has the following advantages:
BFILE is temporary and scoped just within the module on which you are working, then you can use the BFILE related APIs on the variable without ever having to associate this with a column in the database.BFILE column in a server side table, initialize this column value, and then retrieve this column value using a SELECT, you save a round-trip to the server.For more information, refer to the example given for DBMS_LOB.LOADFROMFILE (see "Loading a LOB with BFILE Data").
The OCI counterpart for BFILENAME() is OCILobFileSetName(), which can be used in a similar fashion.
The naming convention for DIRECTORY objects is the same as that for tables and indexes. That is, normal identifiers are interpreted in uppercase, but delimited identifiers are interpreted as is. For example, the following statement:
CREATE DIRECTORY scott_dir AS '/usr/home/scott';
creates a directory object whose name is 'SCOTT_DIR' (in uppercase). But if a delimited identifier is used for the DIRECTORY name, as shown in the following statement
CREATE DIRECTORY "Mary_Dir" AS '/usr/home/mary';
then the directory object name is 'Mary_Dir'. Use 'SCOTT_DIR' and 'Mary_Dir' when calling BFILENAME(). For example:
BFILENAME('SCOTT_DIR', 'afile') BFILENAME('Mary_Dir', 'afile')
On Windows NT, for example, the directory names are case-insensitive. Therefore the following two statements refer to the same directory:
CREATE DIRECTORY "big_cap_dir" AS "g:\data\source"; CREATE DIRECTORY "small_cap_dir" AS "G:\DATA\SOURCE";
This section introduces the BFILE security model and associated SQL statements. The main SQL statements associated with BFILE security are:
CREATE and REPLACE or ALTER a DIRECTORY objectGRANT and REVOKE the READ system and object privileges on DIRECTORY objectsThe DIRECTORY object is a system owned object. For more information on system owned objects, see Oracle Database SQL Reference. Oracle Database supports two new system privileges, which are granted only to DBA:
CREATE ANY DIRECTORY -- for creating or altering the directory object creationDROP ANY DIRECTORY -- for deleting the directory objectREAD permission on the DIRECTORY object enables you to read files located under that directory. The creator of the DIRECTORY object automatically earns the READ privilege.
If you have been granted the READ permission with GRANT option, then you may in turn grant this privilege to other users/roles and add them to your privilege domains.
|
Note: The |
The physical directory that it represents may or may not have the corresponding operating system privileges (read in this case) for the Oracle Server process.
It is the responsibility of the DBA to ensure the following:
The privilege just implies that as far as the Oracle Server is concerned, you may read from files in the directory. These privileges are checked and enforced by the PL/SQL DBMS_LOB package and OCI APIs at the time of the actual file operations.
Refer to the Oracle Database SQL Reference for information about the following SQL DDL statements that create, replace, and drop directory objects:
Refer to the Oracle Database SQL Reference for information about the following SQL DML statements that provide security for BFILEs:
GRANT (system privilege)GRANT (object privilege)REVOKE (system privilege)REVOKE (object privilege)AUDIT (new statements)AUDIT (schema objects)Catalog views are provided for DIRECTORY objects to enable users to view object names and corresponding paths and privileges. Supported views are:
ALL_DIRECTORIES (OWNER, DIRECTORY_NAME, DIRECTORY_PATH)
This view describes all directories accessible to the user.
DBA_DIRECTORIES(OWNER, DIRECTORY_NAME, DIRECTORY_PATH)
This view describes all directories specified for the entire database.
The main goal of the DIRECTORY feature is to enable a simple, flexible, non-intrusive, yet secure mechanism for the DBA to manage access to large files in the server file system. But to realize this goal, it is very important that the DBA follow these guidelines when using DIRECTORY objects:
DIRECTORY object to a data file directory. A DIRECTORY object should not be mapped to physical directories that contain Oracle data files, control files, log files, and other system files. Tampering with these files (accidental or otherwise) could corrupt the database or the server operating system.CREATE ANY DIRECTORY (granted to the DBA initially) should be used carefully and not granted to other users indiscriminately. In most cases, only the database administrator should have these privileges.WITH GRANT OPTION clause when granting privileges to users.DIRECTORY objects should not be arbitrarily dropped or replaced when the database is in operation. If this were to happen, then operations from all sessions on all files associated with this directory object will fail. Further, if a DROP or REPLACE command is executed before these files could be successfully closed, then the references to these files will be lost in the programs, and system resources associated with these files will not be released until the session(s) is shut down.
The only recourse left to PL/SQL users, for example, will be to either run a program block that calls DBMS_LOB.FILECLOSEALL() and restart their file operations, or exit their sessions altogether. Hence, it is imperative that you use these commands with prudence, and preferably during maintenance downtimes.
DIRECTORY objects. Revoking a user's privilege on a DIRECTORY object using the REVOKE statement causes all subsequent operations on dependent files from the user's session to fail. Either you must re-acquire the privileges to close the file, or run a FILECLOSEALL() in the session and restart the file operations.In general, using DIRECTORY objects for managing file access is an extension of system administration work at the operating system level. With some planning, files can be logically organized into suitable directories that have READ privileges for the Oracle process.
DIRECTORY objects can be created with READ privileges that map to these physical directories, and specific database users granted access to these directories.
The database does not support session migration for BFILE datatypes in shared server (multithreaded server) mode. This implies that operations on open BFILE instances can persist beyond the end of a call to a shared server.
In shared server sessions, BFILE operations will be bound to one shared server, they cannot migrate from one server to another. This restriction will be removed in a forthcoming release.
For BFILEs, the value is stored in a server-side operating system file; in other words, external to the database. The BFILE locator that refers to that file is stored in the row.
If a BFILE locator variable that is used in a DBMS_LOB.FILEOPEN() (for example L1) is assigned to another locator variable, (for example L2), then both L1 and L2 point to the same file. This means that two rows in a table with a BFILE column can refer to the same file or to two distinct files -- a fact that the canny developer might turn to advantage, but which could well be a pitfall for the unwary.
A BFILE locator variable operates like any other automatic variable. With respect to file operations, it operates like a file descriptor available as part of the standard I/O library of most conventional programming languages. This implies that once you define and initialize a BFILE locator, and open the file pointed to by this locator, all subsequent operations until the closure of this file must be done from within the same program block using this locator or local copies of this locator.
Note the following guidelines when working with BFILEs:
BFILE locator variable can be used, just as any scalar, as a parameter to other procedures, member methods, or external function callouts. However, it is recommended that you open and close a file from the same program block at the same nesting level.BFILE, then you must set the BFILE value before flushing the object to the database, thereby inserting a new row. In other words, you must call OCILobFileSetName() after OCIObjectNew() and before OCIObjectFlush().INSERT or UPDATE a BFILE without indicating a directory object name and filename.
This rule also applies to users using an OCI bind variable for a BFILE in an insert/update statement. The OCI bind variable must be initialized with a directory object name and filename before issuing the insert or update statement.
BFILE, you must initialize the BFILE to one of the following:
This section describes how to load a LOB with data from a BFILE.
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
The following preconditions must exist before calling this procedure:
|
Note: The |
In using OCI, or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another.
When you use the DBMS_LOB.LOADFROMFILE procedure to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. No implicit translation is performed from binary data to a character set. For this reason, you should use the LOADCLOBFROMFILE procedure when loading text (see Loading a CLOB or NCLOB with Data from a BFILE).
| See Also:
Oracle Database Globalization Support Guide for character set conversion issues. |
Note the following with respect to the amount parameter:
DBMS_LOB.LOADFROMFILEDBMS_LOB.LOBMAXSIZE. If you pass any other value, then it must be less than or equal to the size of the BFILE.OCILobLoadFromFile()
If you want to load the entire BFILE, then you can pass the constant UB4MAXVAL. If you pass any other value, then it must be less than or equal to the size of the BFILE.OCILobLoadFromFile2()
If you want to load the entire BFILE, then you can pass the constant UB8MAXVAL. If you pass any other value, then it must be less than or equal to the size of the BFILE.
| See Also:
Table 14-2, "Maximum LOB Size for Load from File Operations" for details on the maximum value of the amount parameter. |
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lloaddat.sql */ /* Procedure loadLOBFromBFILE_proc is not part of the DBMS_LOB package: */ /* loading a lob with bfile data */ CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc (Dest_loc IN OUT BLOB) IS /* Note: Dest_loc can be a persistent or temporary LOB */ Src_loc BFILE := BFILENAME('MEDIA_DIR', 'keyboard_logo.jpg'); Amount INTEGER := 4000; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB LOADFORMFILE EXAMPLE ------------'); /* Opening the BFILE is mandatory: */ DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY); /* Opening the LOB is optional: */ DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE); DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE(Dest_loc); DBMS_LOB.CLOSE(Src_loc); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lloaddat.c */ #include <oratypes.h> #include <lobdemo.h> void loadLOBDataFromBFile_proc(OCILobLocator *Lob_loc, OCILobLocator* BFile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { oraub8 amount= 2000; printf ("----------- OCILobLoadFromFile Demo --------------\n"); printf (" open the bfile\n"); /* Opening the BFILE locator is Mandatory */ checkerr (errhp, (OCILobOpen(svchp, errhp, BFile_loc, OCI_LOB_READONLY))); printf(" open the lob\n"); /* Opening the CLOB locator is optional */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); /* Load the data from the graphic file (bfile) into the blob */ printf (" load the LOB from File\n"); checkerr (errhp, OCILobLoadFromFile2(svchp, errhp, Lob_loc, BFile_loc, amount, (oraub8)1, (oraub8)1)); /* Closing the LOBs is Mandatory if they have been Opened */ checkerr (errhp, OCILobClose(svchp, errhp, BFile_loc)); checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); return; }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/floadlob.pco * Loading a LOB with BFILE data. IDENTIFICATION DIVISION. PROGRAM-ID. LOAD-BFILE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 DEST-BLOB SQL-BLOB. 01 SRC-BFILE SQL-BFILE. 01 DIR-ALIAS PIC X(30) VARYING. 01 FNAME PIC X(20) VARYING. 01 DIR-IND PIC S9(4) COMP. 01 FNAME-IND PIC S9(4) COMP. 01 AMT 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. LOAD-BFILE. * Allocate and initialize the LOB locators: EXEC SQL ALLOCATE :DEST-BLOB END-EXEC. EXEC SQL ALLOCATE :SRC-BFILE END-EXEC. * Set up the directory and file information: MOVE "ADPHOTO_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "keyboard_photo_3106_13001" TO FNAME-ARR. MOVE 16 TO FNAME-LEN. * Populate the BFILE: EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC SQL SELECT AD_GRAPHIC INTO :SRC-BFILE FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 END-EXEC. * Open the source BFILE READ ONLY. * Open the destination BLOB READ/WRITE: EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC. EXEC SQL LOB OPEN :DEST-BLOB READ WRITE END-EXEC. * Load BFILE data into the BLOB: EXEC SQL LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :DEST-BLOB END-EXEC. * Close the LOBs: EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC. EXEC SQL LOB CLOSE :DEST-BLOB END-EXEC. * And free the LOB locators: END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :DEST-BLOB END-EXEC. 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/floadlob.pc */ /* Loading a LOB with BFILE data. */ #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 loadLOBFromBFILE_proc() { OCIBlobLocator *Dest_loc; OCIBFileLocator *Src_loc; char *Dir = "ADGRAPHIC_DIR", *Name = "mousepad_graphic_2056_12001"; int Amount = 4096; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Initialize the BFILE Locator: */ EXEC SQL ALLOCATE :Src_loc; EXEC SQL LOB FILE SET :Src_loc DIRECTORY = :Dir, FILENAME = :Name; /* Initialize the BLOB Locator: */ EXEC SQL ALLOCATE :Dest_loc; EXEC SQL SELECT ad_photo INTO :Dest_loc FROM Print_media WHERE Product_ID = 2056 AND AD_ID = 12001 FOR UPDATE; /* Opening the BFILE is Mandatory: */ EXEC SQL LOB OPEN :Src_loc READ ONLY; /* Opening the BLOB is Optional: */ EXEC SQL LOB OPEN :Dest_loc READ WRITE; EXEC SQL LOB LOAD :Amount FROM FILE :Src_loc INTO :Dest_loc; /* Closing LOBs and BFILEs is Mandatory if they have been OPENed: */ EXEC SQL LOB CLOSE :Dest_loc; EXEC SQL LOB CLOSE :Src_loc; /* Release resources held by the Locators: */ EXEC SQL FREE :Dest_loc; EXEC SQL FREE :Src_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; loadLOBFromBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/floadlob.bas 'Loading a LOB with BFILE data Dim OraDyn as OraDynaset, OraDyn2 as OraDynaset, OraAdGraphic as OraBFile Dim OraAdPhoto as OraBlob chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT) Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value Set OraAdPhoto = OraDyn.Fields("ad_photo").Value OraDyn.Edit 'Load LOB with data from BFILE: OraAdPhoto.CopyFromBFile (OraAdGraphic) OraDyn.Update
This section describes how to open a BFILE using the OPEN function.
|
Note: You can also open a BFILE using the FILEOPEN function; however, using the OPEN function is recommended for new development. Using the FILEOPEN function is described in Opening a BFILE with FILEOPEN. |
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
These examples open an image in operating system file ADPHOTO_DIR.
Examples are provided in the following six programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fopen.sql */ /* Opening a BFILE with OPEN. */ /* Procedure openBFILE_procTwo is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE openBFILE_procTwo IS file_loc BFILE := BFILENAME('MEDIA_DIR', 'keyboard_logo.jpg'); BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE OPEN EXAMPLE ------------'); /* Open the BFILE: */ DBMS_LOB.OPEN (file_loc, DBMS_LOB.LOB_READONLY); /* ... Do some processing: */ DBMS_LOB.CLOSE(file_loc); END; /
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/fopen.c */ /* Opening a BFILE with OPEN. */ #include <oratypes.h> #include <lobdemo.h> void BfileLobOpen_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { printf ("----------- OCILobOpen BFILE Demo --------------\n"); checkerr(errhp, OCILobOpen(svchp, errhp, Bfile_loc, (ub1)OCI_FILE_READONLY)); /* ... Do some processing. */ checkerr(errhp, OCILobClose(svchp, errhp, Bfile_loc)); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fopen.pco * Opening a BFILE with OPEN. 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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "keyboard_photo_3106_13001" TO FNAME-ARR. MOVE 16 TO FNAME-LEN. * Assign directory object 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fopen.pc */ /* Opening a BFILE using OPEN. In Pro*C/C++ there is only one form of OPEN 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 = "GRAPHIC_DIR", *Name = "mousepad_2056"; 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; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/fopen.bas 'Opening a BFILE using OPEN. Dim OraDyn as OraDynaset, OraAdGraphic as OraBFile Set OraDyn = OraDb.CreateDynaset("select * from Print_media",ORADYN_DEFAULT) Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value 'Go to the last row and open the Bfile for reading: OraDyn.MoveLast OraAdGraphic.Open 'Open Bfile for reading 'Do some processing: OraAdGraphic.Close
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fopen.java */ // 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('ADGRAPHIC_DIR', 'monitor_graphic_3060_11001') 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(); } } }
This section describes how to open a BFILE using the FILEOPEN function.
|
Note: The FILEOPEN function is not recommended for new application development. The OPEN function is recommended for new development. See "Opening a BFILE with OPEN" for details on using OPEN. |
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
While you can continue to use the older FILEOPEN form, Oracle strongly recommends that you switch to using OPEN, because this facilitates future extensibility.
Use the following syntax references for each programmatic environment:
These examples open keyboard_photo3060 in operating system file ADPHOTO_DIR.
Examples are provided in the following four programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/ffilopen.sql */ /* Opening a BFILE with FILEOPEN */ /* Procedure openBFILE_procOne is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE openBFILE_procOne IS file_loc BFILE := BFILENAME('MEDIA_DIR', 'keyboard_logo.jpg'); BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB FILEOPEN EXAMPLE ------------'); /* Open the BFILE: */ DBMS_LOB.FILEOPEN (file_loc, DBMS_LOB.FILE_READONLY); /* ... Do some processing. */ DBMS_LOB.FILECLOSE(file_loc); END; /
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/ffilopen.c */ /* Opening a BFILE with FILEOPEN */ #include <oratypes.h> #include <lobdemo.h> void BfileFileOpen_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { printf ("----------- OCILobFileOpen Demo --------------\n"); checkerr(errhp, OCILobFileOpen(svchp, errhp, Bfile_loc, (ub1)OCI_FILE_READONLY)); /* ... Do some processing. */ checkerr(errhp, OCILobFileClose(svchp, errhp, Bfile_loc)); }
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/ffilopen.java */ // Opening a BFILE with FILEOPEN 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_38 { 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('AD_GRAPHIC', 'monitor_3060') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); src_lob.openFile(); 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(); } } }
This section describes how to determine whether a BFILE is open using ISOPEN.
|
Note: This function (ISOPEN) is recommended for new application development. The older FILEISOPEN function, described in "Determining Whether a BFILE Is Open with FILEISOPEN", is not recommended for new development. |
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
Examples are provided in the following six programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fisopen.sql */ /* Checking if the BFILE is open with ISOPEN */ /* Procedure seeIfOpenBFILE_procTwo is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE seeIfOpenBFILE_procTwo IS file_loc BFILE; RetVal INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE ISOPEN EXAMPLE ------------'); /* Select the LOB, initializing the BFILE locator: */ SELECT ad_graphic INTO file_loc FROM Print_media WHERE product_ID = 3060 AND ad_id = 11001; RetVal := DBMS_LOB.ISOPEN(file_loc); IF (RetVal = 1) THEN DBMS_OUTPUT.PUT_LINE('File is open'); ELSE DBMS_OUTPUT.PUT_LINE('File is not open'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Operation failed'); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/fisopen.c */ /* Checking if the BFILE is Open with ISOPEN. */ #include <oratypes.h> #include <lobdemo.h> void BfileIsOpen_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { boolean flag; printf ("----------- OCILobIsOpen Demo --------------\n"); /* Allocate the locator descriptor */ checkerr(errhp, OCILobOpen(svchp, errhp, Bfile_loc, (ub1)OCI_FILE_READONLY)); checkerr(errhp, OCILobIsOpen(svchp, errhp, Bfile_loc, &flag)); if (flag == TRUE) { printf("File is open\n"); } else { printf("File is not open\n"); } checkerr(errhp, OCILobFileClose(svchp, errhp, Bfile_loc)); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fisopen.pco * Checking if BFILE is open with ISOPEN 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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "keyboard_photo_3060_11001" TO FNAME-ARR. MOVE 16 TO FNAME-LEN. * Assign directory object 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fisopen.pc */ /* Checking if the BFILE is open with ISOPEN. In Pro*C/C++, there is only one form of ISOPEN to determine whether or not a BFILE is OPEN. There is no FILEISOPEN, only a simple ISOPEN. This is an attribute used in the DESCRIBE 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 seeIfOpenBFILE_proc() { OCIBFileLocator *Lob_loc; int isOpen; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; /* Select the BFILE into the locator: */ EXEC SQL SELECT ad_graphic INTO :Lob_loc FROM Print_media WHERE product_id = 2056 AND ad_id = 12001; /* Determine if the BFILE is OPEN or not: */ EXEC SQL LOB DESCRIBE :Lob_loc GET ISOPEN into :isOpen; if (isOpen) printf("BFILE is open\n"); else printf("BFILE is not open\n"); /* Note that in this example, the BFILE is not open: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; seeIfOpenBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/fisopen.bas ' Checking if the BFILE is open with ISOPEN Dim OraDyn as OraDynaset, OraAdGraphic as OraBFile, amount_read%, chunksize%, chunk chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT) Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value If OraAdGraphic.IsOpen then 'Process, if the file is already open: Else 'Process, if the file is not open, and return an error: End If
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fisopen.java */ // Checking if the BFILE is open with ISOPEN 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_48 { 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; Boolean result = null; rset = stmt.executeQuery ( "SELECT BFILENAME('ADGRAPHIC_DIR', 'monitor_graphic_3060_11001') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } result = new Boolean(src_lob.isFileOpen()); System.out.println( "result of fileIsOpen() before opening file : " + result.toString()); src_lob.openFile(); result = new Boolean(src_lob.isFileOpen()); System.out.println( "result of fileIsOpen() after opening file : " + result.toString()); // Close the BFILE, statement and connection: src_lob.closeFile(); int i = cstmt.getInt(1); System.out.println("The result is: " + Integer.toString(i)); OracleCallableStatement cstmt2 = (OracleCallableStatement) conn.prepareCall ( "BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READONLY); END;"); cstmt2.setBFILE(1, bfile); cstmt2.execute(); System.out.println("The BFILE has been opened with a call to " +"DBMS_LOB.OPEN()"); // Use the existing cstmt handle to re-query the status of the locator: cstmt.setBFILE(2, bfile); cstmt.execute(); i = cstmt.getInt(1); System.out.println("This result is: " + Integer.toString(i)); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to determine whether a BFILE is OPEN using the FILEISOPEN function.
|
Note: The FILEISOPEN function is not recommended for new application development. The ISOPEN function is recommended for new development. See Determining Whether a BFILE Is Open Using ISOPEN for details on using ISOPEN. |
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
While you can continue to use the older FILEISOPEN form, Oracle strongly recommends that you switch to using ISOPEN, because this facilitates future extensibility.
Use the following syntax references for each programmatic environment:
These examples query whether a BFILE associated with ad_graphic is open.
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/ffisopen.sql */ /* Checking if the BFILE is OPEN with FILEISOPEN. Procedure seeIfOpenBFILE_procOne is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE seeIfOpenBFILE_procOne IS file_loc BFILE; RetVal INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE FILEISOPEN EXAMPLE ------------'); /* Select the LOB, initializing the BFILE locator: */ SELECT ad_graphic INTO file_loc FROM Print_media WHERE product_ID = 3060 AND ad_id = 11001; RetVal := DBMS_LOB.FILEISOPEN(file_loc); IF (RetVal = 1) THEN DBMS_OUTPUT.PUT_LINE('File is open'); ELSE DBMS_OUTPUT.PUT_LINE('File is not open'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Operation failed'); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/ffisopen.c */ /* Checking if the BFILE is open with FILEISOPEN. */ #include <oratypes.h> #include <lobdemo.h> void BfileFileIsOpen_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { boolean flag; printf ("----------- OCILobFileIsOpen Demo --------------\n"); checkerr(errhp, OCILobFileOpen(svchp, errhp, Bfile_loc, (ub1)OCI_FILE_READONLY)); checkerr(errhp, OCILobFileIsOpen(svchp, errhp, Bfile_loc, &flag)); if (flag == TRUE) { printf("File is open\n"); } else { printf("File is not open\n"); } checkerr(errhp, OCILobFileClose(svchp, errhp, Bfile_loc)); }
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/ffisopen.java */ // Checking if a BFILE is open with FILEISOPEN 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_45 { 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; boolean result = false; rset = stmt.executeQuery ( "SELECT BFILENAME('ADGRAPHIC_DIR', 'monitor_graphic_3060_11001') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } result = src_lob.isFileOpen(); System.out.println( "result of fileIsOpen() before opening file : " + result); if (!result) src_lob.openFile(); result = src_lob.isFileOpen(); System.out.println( "result of fileIsOpen() after opening file : " + result); // Close the BFILE, statement and connection: src_lob.closeFile(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to display BFILE data.
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
Examples are provided in six programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fdisplay.sql */ /* Displaying BFILE data. */ /* Procedure displayBFILE_proc is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE displayBFILE_proc IS file_loc BFILE := BFILENAME('MEDIA_DIR', 'monitor_3060.txt'); Buffer RAW(1024); Amount BINARY_INTEGER := 200; Position INTEGER := 1; BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE DISPLAY EXAMPLE ------------'); /* Opening the BFILE: */ DBMS_LOB.OPEN (file_loc, DBMS_LOB.LOB_READONLY); LOOP DBMS_LOB.READ (file_loc, Amount, Position, Buffer); /* Display the buffer contents: */ DBMS_OUTPUT.PUT_LINE(substr(utl_raw.cast_to_varchar2(Buffer), 1, 250)); Position := Position + Amount; END LOOP; /* Closing the BFILE: */ DBMS_LOB.CLOSE (file_loc); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('End of data'); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/fdisplay.c */ /* Displaying BFILE data. */ #include <oratypes.h> #include <lobdemo.h> void BfileDisplay_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { /* Assume all handles passed as input to this routine have been allocated and initialized */ ub1 bufp[MAXBUFLEN]; oraub8 buflen, amt, offset; boolean done; ub4 retval; ub1 piece; printf ("----------- OCI BFILE Display Demo --------------\n"); checkerr(errhp, OCILobFileOpen(svchp, errhp, Bfile_loc, OCI_FILE_READONLY)); /* This example will READ the entire contents of a BFILE piecewise into a buffer using a standard polling method, processing each buffer piece after every READ operation until the entire BFILE has been read. */ /* Setting amt = 0 will read till the end of LOB*/ amt = 0; buflen = sizeof(bufp); /* Process the data in pieces */ offset = 1; memset((void *)bufp, '\0', MAXBUFLEN); done = FALSE; piece = OCI_FIRST_PIECE; while (!done) { retval = OCILobRead2(svchp, errhp, Bfile_loc, &amt, NULL, offset, (void *) bufp, buflen, piece, (void *)0, (OCICallbackLobRead2)0, (ub2) 0, (ub1) SQLCS_IMPLICIT); switch (retval) { case OCI_SUCCESS: /* Only one piece or last piece*/ /* process the data in bufp. amt will give the amount of data just read in bufp in bytes. */ done = TRUE; break; case OCI_ERROR: /* report_error(); this function is not shown here */ done = TRUE; break; case OCI_NEED_DATA: /* There are 2 or more pieces */ /* process the data in bufp. amt will give the amount of data just read in bufp in bytes. */ piece = OCI_NEXT_PIECE; break; default: (void) printf("Unexpected ERROR: OCILobRead() LOB.\n"); done = TRUE; break; } /* switch */ } /* while */ /* Closing the BFILE is mandatory if you have opened it */ checkerr (errhp, OCILobFileClose(svchp, errhp, Bfile_loc)); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fdisplay.pco * Displaying BFILE data. IDENTIFICATION DIVISION. PROGRAM-ID. DISPLAY-BFILE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(9) VALUES "SAMP/SAMP". EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 DEST-BLOB SQL-BLOB. 01 SRC-BFILE SQL-BFILE. 01 BUFFER PIC X(5) VARYING. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 AMT PIC S9(9) COMP. 01 ORASLNRD PIC 9(4). EXEC SQL END DECLARE SECTION END-EXEC. 01 D-AMT PIC 99,999,99. EXEC SQL VAR BUFFER IS LONG RAW (100) END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL INCLUDE ORACA END-EXEC. PROCEDURE DIVISION. DISPLAY-BFILE-DATA. * Connect to ORACLE 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. * Select the BFILE EXEC SQL SELECT AD_GRAPHIC INTO :SRC-BFILE FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 END-EXEC. * Open the BFILE EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC. * Set the amount = 0 will initiate the polling method MOVE 0 TO AMT; EXEC SQL LOB READ :AMT FROM :SRC-BFILE INTO :BUFFER END-EXEC. * DISPLAY "BFILE DATA". * MOVE AMT TO D-AMT. * DISPLAY "First READ (", D-AMT, "): " BUFFER. * Do READ-LOOP until the whole BFILE is read. EXEC SQL WHENEVER NOT FOUND GO TO END-LOOP END-EXEC. READ-LOOP. EXEC SQL LOB READ :AMT FROM :SRC-BFILE INTO :BUFFER END-EXEC. * MOVE AMT TO D-AMT. * DISPLAY "Next READ (", D-AMT, "): " BUFFER. GO TO READ-LOOP. END-LOOP. EXEC SQL WHENEVER NOT FOUND CONTINUE 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 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fdisplay.pc */ /* Displaying BFILE data. This example reads the entire contents of a BFILE piecewise into a buffer using a streaming mechanism through standard polling, displaying each buffer piece after every READ operation until the entire BFILE has been read: */ #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); } #define BufferLength 1024 void displayBFILE_proc() { OCIBFileLocator *Lob_loc; int Amount; struct { short Length; char Data[BufferLength]; } Buffer; /* Datatype Equivalencing is Mandatory for this Datatype: */ EXEC SQL VAR Buffer is VARRAW(BufferLength); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; /* Select the BFILE: */ EXEC SQL SELECT ad_graphic INTO :Lob_loc FROM Print_media WHERE Product_ID = 2056 AND ad_id = 12001; /* Open the BFILE: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; /* Setting Amount = 0 will initiate the polling method: */ Amount = 0; /* Set the maximum size of the Buffer: */ Buffer.Length = BufferLength; EXEC SQL WHENEVER NOT FOUND DO break; while (TRUE) { /* Read a piece of the BFILE into the Buffer: */ EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer; printf("Display %d bytes\n", Buffer.Length); } printf("Display %d bytes\n", Amount); EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; displayBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/fdisplay.bas ' Displaying BFILE data. Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdGraphio As OraBfile, amount_read%, chunksize%, chunk As Variant Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&) chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT) Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value OraAdGraphic.offset = 1 OraAdGraphic.PollingAmount = OraAdGraphic.Size 'Read entire BFILE contents 'Open the Bfile for reading: OraAdGraphic.Open amount_read = OraAdGraphic.Read(chunk, chunksize) While OraAdGraphic.Status = ORALOB_NEED_DATA amount_read = OraAdGraphic.Read(chunk, chunksize) Wend OraAdGraphic.Close
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fdisplay.java */ // Displaying BFILE data. 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_53 { 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; Boolean result = null; InputStream in = null; byte buf[] = new byte[1000]; int length = 0; boolean alreadyDisplayed = false; rset = stmt.executeQuery ( "SELECT ad_graphic FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } // Open the BFILE: src_lob.openFile(); // Get a handle to stream the data from the BFILE: in = src_lob.getBinaryStream(); // This loop fills the buf iteratively, retrieving data // from the InputStream: while ((in != null) && ((length = in.read(buf)) != -1)) { // the data has already been read into buf // We will only display the first CHUNK in this example: if (! alreadyDisplayed) { System.out.println("Bytes read in: " + Integer.toString(length)); System.out.println(new String(buf)); alreadyDisplayed = true; } } // Close the stream, BFILE, statement and connection: in.close(); src_lob.closeFile(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to read data from a BFILE.
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Note the following when using this operation.
The most efficient way to read of large amounts of LOB data is to use OCILobRead2() with the streaming mechanism enabled using polling or callback. To do so, specify the starting point of the read using the offset parameter and use the symbol OCI_LOBMAXSIZE for the amount, as follows:
ub8 amount = OCI_LOBMAXSIZE; ub4 offset = 1000; OCILobRead2(svchp, errhp, locp, &amount, offset, bufp, bufl, 0, 0, 0, 0)
When using polling mode, be sure to look at the value of the amount parameter after each OCILobRead2() call to see how many bytes were read into the buffer because the buffer may not be entirely full.
When using callbacks, the 'len' parameter, which is input to the callback, will indicate how many bytes are filled in the buffer. Be sure to check the 'len' parameter during your callback processing because the entire buffer may not be filled with data (see the Oracle Call Interface Programmer's Guide.)
DBMS_LOB.READ, the amount parameter can be larger than the size of the data; however, the amount parameter should be less than or equal to the size of the buffer. In PL/SQL, the buffer size is limited to 32K.OCILobRead2, you can pass a value of 0 (zero) for the amount parameter to read to the end of the LOB.Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fread.sql */ /* Reading data from a BFILE. */ /* Procedure readBFILE_proc is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE readBFILE_proc IS file_loc BFILE; Amount INTEGER := 32767; Position INTEGER := 1; Buffer RAW(32767); BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE READ EXAMPLE ------------'); /* Select the LOB: */ SELECT ad_graphic INTO File_loc FROM print_media WHERE product_id = 3060 AND ad_id = 11001; /* Open the BFILE: */ DBMS_LOB.OPEN(File_loc, DBMS_LOB.LOB_READONLY); /* Read data: */ DBMS_LOB.READ(File_loc, Amount, Position, Buffer); /* Close the BFILE: */ DBMS_LOB.CLOSE(File_loc); END; / show errors;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/fread.c */ /* Reading data from a BFILE. */ #include <oratypes.h> #include <lobdemo.h> void BfileRead_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { ub1 bufp[MAXBUFLEN]; oraub8 buflen, amt, offset; ub4 retval; ub1 piece; boolean done; printf ("----------- OCILobRead BFILE Demo --------------\n"); checkerr(errhp, OCILobFileOpen(svchp, errhp, Bfile_loc, OCI_FILE_READONLY)); /* This example will READ the entire contents of a BFILE piecewise into a buffer using a standard polling method, processing each buffer piece after every READ operation until the entire BFILE has been read. */ /* Setting amt = 0 will read till the end of LOB*/ amt = 0; buflen = sizeof(bufp); /* Process the data in pieces */ offset = 1; memset((void *)bufp, '\0', MAXBUFLEN); piece = OCI_FIRST_PIECE; done = FALSE; while (!done) { retval = OCILobRead2(svchp, errhp, Bfile_loc, &amt, NULL, offset, (void *) bufp, buflen, piece, (void *)0, (OCICallbackLobRead2) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); switch (retval) { case OCI_SUCCESS: /* Only one piece since amtp == bufp */ /* Process the data in bufp. amt will give the amount of data just read in bufp is in bytes. */ printf(" amt read=%d in the last call\n", (ub4)amt); done = TRUE; break; case OCI_ERROR: /* report_error(); this function is not shown here */ done = TRUE; break; case OCI_NEED_DATA: printf(" amt read=%d\n", (ub4)amt); piece = OCI_NEXT_PIECE; break; default: (void) printf("Unexpected ERROR: OCILobRead2() LOB.\n"); done = TRUE; break; } } /* Closing the BFILE is mandatory if you have opened it */ checkerr (errhp, OCILobFileClose(svchp, errhp, Bfile_loc)); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fread.pco * Reading data from a BFILE. IDENTIFICATION DIVISION. PROGRAM-ID. READ-BFILE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 BFILE1 SQL-BFILE. 01 BUFFER2 PIC X(5) VARYING. 01 AMT PIC S9(9) COMP. 01 OFFSET PIC S9(9) COMP VALUE 1. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL VAR BUFFER2 IS LONG RAW(5) END-EXEC. PROCEDURE DIVISION. READ-BFILE. * Allocate and initialize the CLOB locator EXEC SQL ALLOCATE :BFILE1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC SQL SELECT AD_GRAPHIC INTO :BFILE1 FROM PRINT_MEDIA M WHERE M.PRODUCT_ID = 3106 AND AD_ID = 13001 END-EXEC. * Open the BFILE EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC. * Initiate polling read MOVE 0 TO AMT. EXEC SQL LOB READ :AMT FROM :BFILE1 INTO :BUFFER2 END-EXEC. * * Display the data here. * * Close and free the locator EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL LOB CLOSE :BFILE1 END-EXEC. END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 END-EXEC.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fread.pc */ /* Reading data from BFILE. */ #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); } #define BufferLength 4096 void readBFILE_proc() { OCIBFileLocator *Lob_loc; /* Amount and BufferLength are equal so only one READ is necessary: */ int Amount = BufferLength; char Buffer[BufferLength]; /* Datatype Equivalencing is Mandatory for this Datatype: */ EXEC SQL VAR Buffer IS RAW(BufferLength); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_graphic INTO :Lob_loc FROM Print_media WHERE Product_ID = 2056; /* Open the BFILE: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; EXEC SQL WHENEVER NOT FOUND CONTINUE; /* Read data: */ EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer; printf("Read %d bytes\n", Amount); /* Close the BFILE: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; readBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/fread.bas ' Reading data from a BFILE Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdGraphic As OraBfile, amount_read%, chunksize%, chunk As Variant Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&) chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT) Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value OraAdGraphic.offset = 1 OraAdGraphic.PollingAmount = OraAdGraphic.Size 'Read entire BFILE contents 'Open the Bfile for reading: OraAdGraphic.Open amount_read = OraAdGraphic.Read(chunk, chunksize) While OraAdGraphic.Status = ORALOB_NEED_DATA amount_read = OraAdGraphic.Read(chunk, chunksize) Wend OraAdGraphic.Close
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fread.java */ // Reading data from a BFILE. 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_53 { 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; Boolean result = null; InputStream in = null; byte buf[] = new byte[1000]; int length = 0; boolean alreadyDisplayed = false; rset = stmt.executeQuery ( "SELECT ad_graphic FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } // Open the BFILE: src_lob.openFile(); // Get a handle to stream the data from the BFILE: in = src_lob.getBinaryStream(); // This loop fills the buf iteratively, retrieving data // from the InputStream: while ((in != null) && ((length = in.read(buf)) != -1)) { // the data has already been read into buf // We will only display the first CHUNK in this example: if (! alreadyDisplayed) { System.out.println("Bytes read in: " + Integer.toString(length)); System.out.println(new String(buf)); alreadyDisplayed = true; } } // Close the stream, BFILE, statement and connection: in.close(); src_lob.closeFile(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to read portion of BFILE data using SUBSTR.
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
Examples are provided in these five programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/freadprt.sql */ /* Reading portion of a BFILE data using substr. */ /* Procedure substringBFILE_proc is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE substringBFILE_proc IS file_loc BFILE; Position INTEGER := 1; Buffer RAW(32767); BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB SUBSTR EXAMPLE ------------'); /* Select the LOB: */ SELECT PMtab.ad_graphic INTO file_loc FROM Print_media PMtab WHERE PMtab.product_id = 3060 AND PMtab.ad_id = 11001; /* Open the BFILE: */ DBMS_LOB.OPEN(file_loc, DBMS_LOB.LOB_READONLY); Buffer := DBMS_LOB.SUBSTR(file_loc, 255, Position); /* Close the BFILE: */ DBMS_LOB.CLOSE(file_loc); END; / show errors;
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/freadprt.pco * Reading portion of a BFILE data using substr. IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-SUBSTR. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 BFILE1 SQL-BFILE. 01 BUFFER2 PIC X(32767) VARYING. 01 AMT PIC S9(9) COMP. 01 POS PIC S9(9) COMP VALUE 1024. 01 OFFSET PIC S9(9) COMP VALUE 1. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL VAR BUFFER2 IS VARRAW(32767) END-EXEC. PROCEDURE DIVISION. BFILE-SUBSTR. * Allocate and initialize the CLOB locator: EXEC SQL ALLOCATE :BFILE1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC SQL SELECT PTAB.AD_GRAPHIC INTO :BFILE1 FROM PRINT_MEDIA PTAB WHERE PTAB.PRODUCT_ID = 3106 AND PTAB.AD_ ID = 13001 END-EXEC. * Open the BFILE for READ ONLY: EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC. * Execute PL/SQL to use its SUBSTR functionality: MOVE 32767 TO AMT. EXEC SQL EXECUTE BEGIN :BUFFER2 := DBMS_LOB.SUBSTR(:BFILE1,:AMT,:POS); END; END-EXEC. * Close and free the locators: EXEC SQL LOB CLOSE :BFILE1 END-EXEC. END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 END-EXEC.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/freadprt.pc */ /* Reading portion of a BFILE data using substr. Pro*C/C++ lacks an equivalent embedded SQL form for the DBMS_LOB.SUBSTR() function. However, Pro*C/C++ can interoperate with PL/SQL using anonymous PL/SQL blocks embedded in a Pro*C/C++ program as this example shows: */ #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); } #define BufferLength 256 void substringBFILE_proc() { OCIBFileLocator *Lob_loc; int Position = 1; char Buffer[BufferLength]; EXEC SQL VAR Buffer IS RAW(BufferLength); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT PMtab.ad_graphic INTO :Lob_loc FROM Print_media PMtab WHERE PMtab.product_id = 2056 AND PMTab.ad_id = 12001; /* Open the BFILE: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; /* Invoke SUBSTR() from within an anonymous PL/SQL block: */ EXEC SQL EXECUTE BEGIN :Buffer := DBMS_LOB.SUBSTR(:Lob_loc, 256, :Position); END; END-EXEC; /* Close the BFILE: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; substringBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/freadprt.bas ' Reading portion of a BFILE data using substr. Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdGraphic As OraBfile, amount_read%, chunksize%, chunk Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&) chunk_size = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT) Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value OraMusic.PollingAmount = OraAdGraphic.Size 'Read entire BFILE contents OraAdGraphic.offset = 255 'Read from the 255th position 'Open the Bfile for reading: OraAdGraphic.Open amount_read = OraAdGraphic.Read(chunk, chunk_size) 'chunk returned is a variant of type byte array If amount_read <> chunk_size Then 'Do error processing Else 'Process the data End If
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/freadprt.java */ // Reading a portion of a BFILE data using substr. 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_62 { 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; InputStream in = null; byte buf[] = new byte[1000]; int length = 0; rset = stmt.executeQuery ( "SELECT ad_graphic FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } // Open the BFILE: src_lob.openFile(); // Get a handle to stream the data from the BFILE in = src_lob.getBinaryStream(); if (in != null) { // request 255 bytes into buf, starting from offset 1. // length = # bytes actually returned from stream: length = in.read(buf, 1, 255); System.out.println("Bytes read in: " + Integer.toString(length)); // Process the buf: System.out.println(new String(buf)); } // Close the stream, BFILE, statement and connection: in.close(); src_lob.closeFile(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to compare all or parts of two BFILES.
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
Examples are provided in these five programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fpattern.sql */ /* Checking if a pattern exists in a BFILE using instr /* Procedure compareBFILEs_proc is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE compareBFILEs_proc IS /* Initialize the BFILE locator: */ file_loc1 BFILE := BFILENAME('MEDIA_DIR', 'keyboard.jpg'); file_loc2 BFILE; Retval INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB COMPARE EXAMPLE ------------'); /* Select the LOB: */ SELECT ad_graphic INTO File_loc2 FROM print_media WHERE Product_ID = 3060 AND ad_id = 11001; /* 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; / SHOW ERRORS;
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fcompare.pco * Comparing all or parts of two BFILES. IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-COMPARE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BFILE1 SQL-BFILE. 01 BFILE2 SQL-BFILE. 01 RET PIC S9(9) COMP. 01 AMT PIC S9(9) COMP. 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. BFIlE-COMPARE. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the BLOB locators: EXEC SQL ALLOCATE :BFILE1 END-EXEC. EXEC SQL ALLOCATE :BFILE2 END-EXEC. * Set up the directory and file information: MOVE "ADGRAPHIC_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "keyboard_graphic_3106_13001" TO FNAME-ARR. MOVE 17 TO FNAME-LEN. EXEC SQL LOB FILE SET :BFILE1 DIRECTORY = :DIR-ALIAS, FILENAME = :FNAME END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC SQL SELECT AD_GRAPHIC INTO :BFILE2 FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND ad_id = 13001 END-EXEC. * Open the BLOBs for READ ONLY: EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC. EXEC SQL LOB OPEN :BFILE2 READ ONLY END-EXEC. * Execute PL/SQL to get COMPARE functionality: MOVE 5 TO AMT. EXEC SQL EXECUTE BEGIN :RET := DBMS_LOB.COMPARE(:BFILE1,:BFILE2, :AMT,1,1); END; END-EXEC. IF RET = 0 * Logic for equal BFILEs goes here DISPLAY "BFILES are equal" ELSE * Logic for unequal BFILEs goes here DISPLAY "BFILEs are not equal" END-IF. EXEC SQL LOB CLOSE :BFILE1 END-EXEC. EXEC SQL LOB CLOSE :BFILE2 END-EXEC. END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 END-EXEC. EXEC SQL FREE :BFILE2 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fcompare.pc */ /* Comparing all or parts of two BFILES. Pro*C/C++ lacks an equivalent embedded SQL form for the DBMS_LOB.COMPARE() function. Like the DBMS_LOB.SUBSTR() function, however, Pro*C/C++ can invoke DBMS_LOB.COMPARE() in an anonymous PL/SQL block as shown here: */ #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 compareBFILEs_proc() { OCIBFileLocator *Lob_loc1, *Lob_loc2; int Retval = 1; char *Dir1 = "GRAPHIC_DIR", *Name1 = "mousepad_2056"; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc1; EXEC SQL LOB FILE SET :Lob_loc1 DIRECTORY = :Dir1, FILENAME = :Name1; EXEC SQL ALLOCATE :Lob_loc2; EXEC SQL SELECT Photo INTO :Lob_loc2 FROM Print_media WHERE Product_ID = 2056; /* Open the BFILEs: */ EXEC SQL LOB OPEN :Lob_loc1 READ ONLY; EXEC SQL LOB OPEN :Lob_loc2 READ ONLY; /* Compare the BFILEs in PL/SQL using DBMS_LOB.COMPARE() */ EXEC SQL EXECUTE BEGIN :Retval := DBMS_LOB.COMPARE( :Lob_loc2, :Lob_loc1, DBMS_LOB.LOBMAXSIZE, 1, 1); END; END-EXEC; /* Close the BFILEs: */ EXEC SQL LOB CLOSE :Lob_loc1; EXEC SQL LOB CLOSE :Lob_loc2; if (0 == Retval) printf("BFILEs are the same\n"); else printf("BFILEs are not the same\n"); /* Release resources used by the locators: */ EXEC SQL FREE :Lob_loc1; EXEC SQL FREE :Lob_loc2; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; compareBFILEs_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/fcompare.bas 'Comparing all or parts of two BFILES. 'The PL/SQL packages and the tables mentioned here are not part of the 'standard OO4O installation: Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdGraphic As OraBfile, OraMyAdGraphic As OraBfile, OraSql As OraSqlStmt Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&) OraDb.Connection.BeginTrans Set OraParameters = OraDb.Parameters OraParameters.Add "id", 3106, ORAPARM_INPUT 'Define out parameter of BFILE type: OraParameters.Add "MyAdGraphic", Null, ORAPARM_OUTPUT OraParameters("MyAdGraphic").ServerType = ORATYPE_BFILE Set OraSql = OraDb.CreateSql( "BEGIN SELECT ad_graphic INTO :MyAdGraphic FROM Print_media WHERE product_ id = :id; END;", ORASQL_FAILEXEC) Set OraMyAdGraphic = OraParameters("MyAdGraphic").Value 'Create dynaset: Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Print_media WHERE product_id = 3106", ORADYN_DEFAULT) Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value 'Open the Bfile for reading: OraAdGraphic.Open OraMyAdGraphic.Open If OraAdGraphic.Compare(OraMyAdGraphic) Then 'Process the data Else 'Do error processing End If OraDb.Connection.CommitTrans
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fcompare.java */ // Comparing all or parts of two BFILES. 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; rset = stmt.executeQuery ( "SELECT ad_graphic FROM Print_media WHERE product_id = 3106"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBFILE (1); } rset = stmt.executeQuery ( "SELECT BFILENAME('AD_GRAPHIC', 'keyboard_3106') FROM DUAL"); if (rset.next()) { lob_loc2 = ((OracleResultSet)rset).getBFILE (1); } lob_loc1.openFile (); lob_loc2.openFile (); if (lob_loc1.length() > lob_loc2.length()) System.out.println("Looking for LOB2 inside LOB1. result = " + lob_loc1.position(lob_loc2, 1)); else System.out.println("Looking for LOB1 inside LOB2. result = " + lob_loc2.position(lob_loc1, 1)); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to determine whether a pattern exists in a BFILE using INSTR.
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
These examples are provided in the following four programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fcompare.sql */ /* Comparing all or parts of two BFILES. */ /* Procedure instringBFILE_proc is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE instringBFILE_proc IS file_loc BFILE; Pattern RAW(32767); Position INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE INSTR EXAMPLE ------------'); /* Select the LOB: */ SELECT PMtab.ad_graphic INTO file_loc FROM Print_media PMtab WHERE PMtab.product_id = 3060 AND PMtab.ad_id = 11001; /* Open the BFILE: */ DBMS_LOB.OPEN(file_loc, DBMS_LOB.LOB_READONLY); /* Initialize the pattern for which to search, find the 2nd occurrence of the pattern starting from the beginning of the BFILE: */ Position := DBMS_LOB.INSTR(file_loc, Pattern, 1, 2); /* Close the BFILE: */ DBMS_LOB.CLOSE(file_loc); END; / SHOW ERRORS;
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fpattern.pco * Checking if a pattern exists in a BFILE using instr 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 AD_GRAPHIC INTO :BFILE1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fpattern.pc */ /* Checking if a pattern exists in a BFILE using instr 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 Product_id = 2056, 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.ad_graphic \ FROM TABLE(SELECT PMtab.textdoc_ntab FROM Print_media PMtab \ WHERE product_id = :cid) PMtab \ WHERE PMtab.Segment = :seg'; EXEC SQL DECLARE C CURSOR FOR S; EXEC SQL OPEN C USING :Product_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; }
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fpattern.java */ // Checking if a pattern exists in a BFILE using instr 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 ad_graphic FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); 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(); } } }
This procedure determines whether a BFILE locator points to a valid BFILE instance.
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
The examples are provided in the following six programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fexists.sql */ /* Checking if a BFILE exists */ /* Procedure seeIfExistsBFILE_proc is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE seeIfExistsBFILE_proc IS file_loc BFILE; BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE FILEEXISTS EXAMPLE ------------'); /* Select the LOB: */ SELECT ad_graphic INTO File_loc FROM print_media WHERE product_id = 3060 AND ad_id = 11001; /* See If the BFILE exists: */ IF (DBMS_LOB.FILEEXISTS(file_loc) != 0) THEN DBMS_OUTPUT.PUT_LINE('Processing given that the BFILE exists'); ELSE DBMS_OUTPUT.PUT_LINE('Processing given that the BFILE does not exist'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Operation failed'); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/fexists.c */ /* Checking if a BFILE exists */ #include <oratypes.h> #include <lobdemo.h> void BfileExists_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { boolean is_exist; printf ("----------- OCILobFileExists Demo --------------\n"); checkerr (errhp, OCILobFileExists(svchp, errhp, Bfile_loc, &is_exist)); if (is_exist == TRUE) { printf("File exists\n"); } else { printf("File does not exist\n"); } }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fexists.pco * Checking if a BFILE exists. IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-EXISTS. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BFILE1 SQL-BFILE. 01 FEXISTS 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-EXISTS. 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 AD_GRAPHIC INTO :BFILE1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 END-EXEC. EXEC SQL LOB DESCRIBE :BFILE1 GET FILEEXISTS INTO :FEXISTS END-EXEC. IF FEXISTS = 1 * Logic for file exists here DISPLAY "File exists" ELSE * Logic for file does not exist here DISPLAY "File does not exist" END-IF. 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fexists.pc */ /* Checking if a BFILE exists. */ #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 seeIfBFILEExists_proc() { OCIBFileLocator *Lob_loc; unsigned int Exists = 0; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT PMtab.ad_graphic INTO :Lob_loc FROM Print_media PMtab WHERE PMtab.Product_ID = 2056 AND PMtab.ad_id = 12001; /* See if the BFILE Exists: */ EXEC SQL LOB DESCRIBE :Lob_loc GET FILEEXISTS INTO :Exists; printf("BFILE %s exist\n", Exists ? "does" : "does not"); EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; seeIfBFILEExists_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/fexists.bas 'Checking if a BFILE exists. 'The PL/SQL packages and the tables mentioned here are not part of the 'standard OO4O installation: Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraAdGraphic As OraBfile, OraSql As OraSqlStmt Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&) OraDb.Connection.BeginTrans Set OraParameters = OraDb.Parameters OraParameters.Add "id", 2056, ORAPARM_INPUT 'Define out parameter of BFILE type: OraParameters.Add "MyAdGraphic", Null, ORAPARM_OUTPUT OraParameters("MyAdGraphic").ServerType = ORATYPE_BFILE Set OraSql = OraDb.CreateSql( "BEGIN SELECT ad_graphic INTO :MyAdGraphic FROM Print_media WHERE product_id = :id; END;", ORASQL_FAILEXEC) Set OraAdGraphic = OraParameters("MyAdGraphic").Value If OraAdGraphic.Exists Then 'Process the data Else 'Do error processing End If OraDb.Connection.CommitTrans
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fexists.java */ // Checking if a BFILE exists. 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_74 { 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; ResultSet rset = stmt.executeQuery ( "SELECT ad_graphic FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBFILE (1); } // See if the BFILE exists: System.out.println("Result from fileExists(): " + lob_loc.fileExists()); // Return the length of the BFILE: long length = lob_loc.length(); System.out.println("Length of BFILE: " + length); // Get the directory object for this BFILE: System.out.println("Directory object: " + lob_loc.getDirAlias()); // Get the file name for this BFILE: System.out.println("File name: " + lob_loc.getName()); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to get the length of a BFILE.
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
The examples are provided in six programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/flength.sql */ /* Getting the length of a BFILE. */ /* Procedure getLengthBFILE_proc is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE getLengthBFILE_proc IS file_loc BFILE; Length INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE LENGTH EXAMPLE ------------'); /* Initialize the BFILE locator by selecting the LOB: */ SELECT PMtab.ad_graphic INTO file_loc FROM Print_media PMtab WHERE PMtab.product_id = 3060 AND PMtab.ad_id = 11001; /* Open the BFILE: */ DBMS_LOB.OPEN(file_loc, DBMS_LOB.LOB_READONLY); /* Get the length of the LOB: */ Length := DBMS_LOB.GETLENGTH(file_loc); IF Length IS NULL THEN DBMS_OUTPUT.PUT_LINE('BFILE is null.'); ELSE DBMS_OUTPUT.PUT_LINE('The length is ' || length); END IF; /* Close the BFILE: */ DBMS_LOB.CLOSE(file_loc); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/flength.c */ /* Getting the length of a BFILE. */ /* Select the lob/bfile from table Print_media */ #include <oratypes.h> #include <lobdemo.h> void BfileLength_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { oraub8 len; printf ("----------- OCILobGetLength BFILE Demo --------------\n"); checkerr (errhp, OCILobFileOpen(svchp, errhp, Bfile_loc, (ub1) OCI_FILE_READONLY)); checkerr (errhp, OCILobGetLength2(svchp, errhp, Bfile_loc, &len)); printf("Length of bfile = %d\n", (ub4)len); checkerr (errhp, OCILobFileClose(svchp, errhp, Bfile_loc)); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/flength.pco * Getting the length of a BFILE. IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-LENGTH. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BFILE1 SQL-BFILE. 01 LEN PIC S9(9) COMP. 01 D-LEN PIC 9(4). 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-LENGTH. 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 AD_GRAPHIC INTO :BFILE1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 END-EXEC. * Use LOB DESCRIBE to get length of lob: EXEC SQL LOB DESCRIBE :BFILE1 GET LENGTH INTO :LEN END-EXEC. MOVE LEN TO D-LEN. DISPLAY "Length of BFILE is ", D-LEN. END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/flength.pc */ /* Getting the length of a BFILE. */ #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 getLengthBFILE_proc() { OCIBFileLocator *Lob_loc; unsigned int Length = 0; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT PMtab.ad_graphic INTO :Lob_loc FROM Print_media PMtab WHERE PMtab.product_id = 3060 AND ad_id = 11001; /* Open the BFILE: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; /* Get the Length: */ EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length; /* If the BFILE is NULL or unitialized, then Length is Undefined: */ printf("Length is %d bytes\n", Length); /* Close the BFILE: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; getLengthBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/flength.bas 'Getting the length of a BFILE. 'The PL/SQL packages and the tables mentioned here are not part of the ' 'standard OO4O installation: Dim MySession As OraSession Dim OraDb As OraDatabase Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&) OraDb.Connection.BeginTrans Set OraParameters = OraDb.Parameters OraParameters.Add "id", 2056, ORAPARM_INPUT 'Define out parameter of BFILE type: OraParameters.Add "AdGraphic", Null, ORAPARM_OUTPUT OraParameters("MyAdGraphic").ServerType = ORATYPE_BFILE Set OraSql = OraDb.CreateSql( "BEGIN SELECT ad_graphic INTO :MyAdGraphic FROM Print_media WHERE product_ id = :id; END;", ORASQL_FAILEXEC) Set OraAdGraphic = OraParameters("MyAdGraphic").Value If OraAdGraphic.Size = 0 Then MsgBox "BFile size is 0" Else MsgBox "BFile size is " & OraAdGraphic.Size End If OraDb.Connection.CommitTrans
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/flength.java */ // Getting the length of a BFILE. 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_74 { 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; ResultSet rset = stmt.executeQuery ( "SELECT ad_graphic FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBFILE (1); } // See if the BFILE exists: System.out.println("Result from fileExists(): " + lob_loc.fileExists()); // Return the length of the BFILE: long length = lob_loc.length(); System.out.println("Length of BFILE: " + length); // Get the directory object for this BFILE: System.out.println("Directory object: " + lob_loc.getDirAlias()); // Get the file name for this BFILE: System.out.println("File name: " + lob_loc.getName()); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to assign one BFILE locator to another.
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
The examples are provided in the following five programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fcopyloc.sql */ /* Copying a LOB locator for a BFILE. */ /* Procedure BFILEAssign_proc is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE BFILEAssign_proc IS file_loc1 BFILE := BFILENAME('MEDIA_DIR', 'keyboard_logo.jpg'); file_loc2 BFILE; BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE ASSIGN EXAMPLE ------------'); /* SELECT Photo INTO file_loc1 FROM print_media WHERE Product_ID = 3060 AND ad_id = 11001 FOR UPDATE; */ /* Assign file_loc1 to file_loc2 so that they both */ /* refer to the same operating system file: */ file_loc2 := file_loc1; /* Now you can read the bfile from either file_loc1 or file_loc2. */ END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/fcopyloc.c */ /* Copying a LOB locator for a BFILE. */ #include <oratypes.h> #include <lobdemo.h> void BfileAssign_proc(OCILobLocator *Bfile_loc1, OCILobLocator *Bfile_loc2, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { printf ("----------- OCI BFILE Assign Demo --------------\n"); checkerr(errhp, OCILobLocatorAssign(svchp, errhp, Bfile_loc1, &Bfile_loc2)); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fcopyloc.pco * Copying a LOB locator for a BFILE. IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-COPY-LOCATOR. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BFILE1 SQL-BFILE. 01 BFILE2 SQL-BFILE. 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. BILFE-COPY-LOCATOR. 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 ALLOCATE :BFILE2 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC SQL SELECT AD_GRAPHIC INTO :BFILE1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 END-EXEC. EXEC SQLLOB ASSIGN :BFILE1 TO :BFILE2 END-EXEC. END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 END-EXEC. EXEC SQL FREE :BFILE2 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fcopyloc.pc */ /* Copying a LOB locator for a BFILE. */ #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 BFILEAssign_proc() { OCIBFileLocator *Lob_loc1, *Lob_loc2; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc1; EXEC SQL ALLOCATE :Lob_loc2; EXEC SQL SELECT ad_graphic INTO :Lob_loc1 FROM Print_media WHERE product_id = 2056 AND ad_id = 12001; /* Assign Lob_loc1 to Lob_loc2 so that they both refer to the same operating system file: */ EXEC SQL LOB ASSIGN :Lob_loc1 TO :Lob_loc2; /* Now you can read the BFILE from either Lob_loc1 or Lob_loc2 */ } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; BFILEAssign_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fcopyloc.java */ // Copying a LOB locator for a BFILE. 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_81 { 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 = stmt.executeQuery ( "SELECT ad_graphic FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBFILE (1); } // Assign lob_loc1 to lob_loc2 so that they both refer // to the same operating system file. // Now the BFILE can be read through either of the locators: lob_loc2 = lob_loc1; stmt.close(); conn.commit(); conn.close(); } //catch (SQLException e) catch (Exception e) { e.printStackTrace(); } } }
This section describes how to get the directory object name and filename of a BFILE.
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
Examples of this procedure are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fgetdir.sql */ /* Getting the directory object and filename of a BFILE*/ CREATE OR REPLACE PROCEDURE getNameBFILE_proc IS file_loc BFILE; DirAlias_name VARCHAR2(30); File_name VARCHAR2(40); BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE FILEGETNAME EXAMPLE ------------'); SELECT ad_graphic INTO file_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001; DBMS_LOB.FILEGETNAME(file_loc, DirAlias_name, File_name); /* DirAlias_name and File_name now store the directory object and filename */ END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/fgetdir.c */ /* Getting the directory object and filename */ #include <oratypes.h> #include <lobdemo.h> void BfileGetDir_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { OraText dir_alias[32]; OraText filename[256]; ub2 d_length = 32; ub2 f_length = 256; printf ("----------- OCILobFileGetName Demo --------------\n"); checkerr(errhp, OCILobFileGetName(envhp, errhp, Bfile_loc, dir_alias, &d_length, filename, &f_length)); dir_alias[d_length] = '\0'; filename[f_length] = '\0'; printf("Directory object : [%s]\n", dir_alias); printf("File name : [%s]\n", filename); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fgetdir.pco * Getting the directory object and filename IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-DIR-ALIAS. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BFILE1 SQL-BFILE. 01 DIR-ALIAS PIC X(30) VARYING. 01 FNAME PIC X(30) 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-DIR-ALIAS. 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. * Populate the BFILE locator: EXEC SQL SELECT AD_GRAPHIC INTO :BFILE1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 END-EXEC. * Use the LOB DESCRIBE functionality to get * the directory object and the filename: EXEC SQL LOB DESCRIBE :BFILE1 GET DIRECTORY, FILENAME INTO :DIR-ALIAS, :FNAME END-EXEC. DISPLAY "DIRECTORY: ", DIR-ALIAS-ARR, "FNAME: ", FNAME-ARR. END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fgetdir.pc */ /* Getting the directory object and filename */ #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 getBFILEDirectoryAndFilename_proc() { OCIBFileLocator *Lob_loc; char Directory[31], Filename[255]; /* Datatype Equivalencing is Optional: */ EXEC SQL VAR Directory IS STRING; EXEC SQL VAR Filename IS STRING; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; /* Select the BFILE: */ EXEC SQL SELECT ad_graphic INTO :Lob_loc FROM print_media WHERE product_id = 2056 AND ad_id = 12001; /* Open the BFILE: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; /* Get the Directory Alias and Filename: */ EXEC SQL LOB DESCRIBE :Lob_loc GET DIRECTORY, FILENAME INTO :Directory, :Filename; /* Close the BFILE: */ EXEC SQL LOB CLOSE :Lob_loc; printf("Directory Alias: %s\n", Directory); printf("Filename: %s\n", Filename); /* Release resources held by the locator: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; getBFILEDirectoryAndFilename_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/fgetdir.bas 'Getting the directory object and filename 'The PL/SQL packages and tables mentioned here are not part of the 'standard OO4O installation: Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraAdGraphic1 As OraBfile, OraSql As OraSqlStmt Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&) OraDb.Connection.BeginTrans Set OraParameters = OraDb.Parameters OraParameters.Add "id", 2056, ORAPARM_INPUT 'Define out parameter of BFILE type: OraParameters.Add "MyAdGraphic", Null, ORAPARM_OUTPUT OraParameters("MyAdGraphic").ServerType = ORATYPE_BFILE Set OraSql = OraDb.CreateSql( "BEGIN SELECT ad_graphic INTO :MyAdGraphic FROM Print_media WHERE product_id = :id; END;", ORASQL_FAILEXEC) Set OraAdGraphic1 = OraParameters("MyAdGraphic").Value 'Get directory object and filename: MsgBox " Directory object is " & OraAdGraphic1.DirectoryName & " Filename is " & OraAdGraphic1.filename OraDb.Connection.CommitTrans
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fgetdir.java */ // Getting the directory object and filename 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_74 { 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; ResultSet rset = stmt.executeQuery ( "SELECT ad_graphic FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBFILE (1); } // See if the BFILE exists: System.out.println("Result from fileExists(): " + lob_loc.fileExists()); // Return the length of the BFILE: long length = lob_loc.length(); System.out.println("Length of BFILE: " + length); // Get the directory object for this BFILE: System.out.println("Directory object: " + lob_loc.getDirAlias()); // Get the file name for this BFILE: System.out.println("File name: " + lob_loc.getName()); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to UPDATE a BFILE by initializing a BFILE locator.
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
The examples are provided in six programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fupdate.sql */ /* Updating a BFILE by initializing a BFILE locator. */ /* Procedure updateUseBindVariable_proc is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE updateBFILEColumn_proc IS File_loc BFILE; BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE UPDATE EXAMPLE ------------'); SELECT ad_graphic INTO File_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001; UPDATE Print_media SET ad_graphic = File_loc WHERE product_id = 3060 AND ad_id = 11001; END; /
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/fupdate.c */ /* Updating a BFILE by initializing a BFILE locator. */ #include <oratypes.h> #include <lobdemo.h> void BfileUpdate_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { OCIBind *bndhp, *bndhp2; text *updstmt = (text *) "UPDATE Print_media SET ad_graphic = :Lob_loc \ WHERE product_id = 3107 AND ad_id = 13002"; OraText *Dir = (OraText *)"MEDIA_DIR", *Name = (OraText *)"keyboard_logo.jpg"; printf ("----------- OCI BFILE Update Demo --------------\n"); /* Prepare the SQL statement: */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, updstmt, (ub4) strlen((char *) updstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); checkerr (errhp, OCILobFileSetName(envhp, errhp, &Bfile_loc, Dir, (ub2)strlen((char *)Dir), Name,(ub2)strlen((char *)Name))); checkerr (errhp, OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (void *) &Bfile_loc, (sb4) 0, SQLT_BFILE, (void *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)); /* Execute the SQL statement: */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); printf("Bfile column updated \n"); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fupdate.pco * Updating a BFILE by initializing a BFILE locator. IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-UPDATE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BFILE1 SQL-BFILE. 01 BFILE-IND PIC S9(4) COMP. 01 DIR-ALIAS PIC X(30) VARYING. 01 FNAME PIC X(30) 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-UPDATE. 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. * Populate the BFILE: EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC ORACLE OPTION (SELECT_ERROR=NO) END-EXEC. EXEC SQL SELECT AD_GRAPHIC INTO :BFILE1:BFILE-IND FROM PRINT_MEDIA WHERE PRODUCT_ID = 3060 AND AD_ID = 13001 END-EXEC. * Make graphic associated with product_id=3106 same as product_id=3060 * and ad_id = 13001: EXEC SQL UPDATE PRINT_MEDIA SET AD_GRAPHIC = :BFILE1:BFILE-IND WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 END-EXEC. * Free the BFILE: 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fupdate.pc */ /* Updating a BFILE by initializing a BFILE locator. */ #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 updateUseBindVariable_proc(Lob_loc) OCIBFileLocator *Lob_loc; { EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL UPDATE Print_media SET ad_graphic = :Lob_loc WHERE product_ID = 2056 AND ad_id = 12001; } void updateBFILE_proc() { OCIBFileLocator *Lob_loc; EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_graphic INTO :Lob_loc FROM Print_media WHERE product_id = 2056 AND ad_id 12001; updateUseBindVariable_proc(Lob_loc); EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; updateBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/fupdate.bas 'Updating a BFILE by initializing a BFILE locator. Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraParameters As OraParameters, OraAdGraphic As OraBfile Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&) OraDb.Connection.BeginTrans Set OraParameters = OraDb.Parameters 'Define in out parameter of BFILE type: OraParameters.Add "MyAdGraphic", Null, ORAPARM_BOTH, ORATYPE_BFILE 'Define out parameter of BFILE type: OraDb.ExecuteSQL ( "BEGIN SELECT ad_graphic INTO :MyAdGraphic FROM Print_media WHERE product_id = 2056 AND ad_id = 12001; END;") 'Update the ad_graphic BFile for product_id=2056 AND ad_id = 12001 to product_id=2268 AND ad_id = 21001: OraDb.ExecuteSQL ( "UPDATE Print_media SET ad_graphic = :MyAdGraphic WHERE product_id = 2268 AND ad_id = 21001") 'Get directory object and filename 'MsgBox " Directory object is " & OraAdGraphic1.DirectoryName & " Filename is " & OraAdGraphic1.filename OraDb.Connection.CommitTrans
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fupdate.java */ // Updating a BFILE by initializing a BFILE locator. 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_100 { 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; OraclePreparedStatement pstmt = null; rset = stmt.executeQuery ( "SELECT ad_graphic FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } // Prepare a CallableStatement to OPEN the LOB for READWRITE: pstmt = (OraclePreparedStatement) conn.prepareStatement ( "UPDATE Print_media SET ad_graphic = ? WHERE product_id = 3060 AND ad_id = 11001"); pstmt.setBFILE(1, src_lob); pstmt.execute(); //Close the statements and commit the transaction: stmt.close(); pstmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to close a BFILE with FILECLOSE.
|
Note: This function (FILECLOSE) is not recommended for new development. For new development, use the CLOSE function instead. See "Closing a BFILE with CLOSE" for more information. |
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Use the following syntax references for each programmatic environment:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fclose_f.sql */ /* Closing a BFILE with FILECLOSE. Procedure closeBFILE_procOne is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE closeBFILE_procOne IS file_loc BFILE := BFILENAME('MEDIA_DIR', 'keyboard_logo.jpg'); BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE FILECLOSE EXAMPLE ------------'); DBMS_LOB.FILEOPEN(file_loc, DBMS_LOB.FILE_READONLY); /* ...Do some processing. */ DBMS_LOB.FILECLOSE(file_loc); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/fclose_f.c */ /* Closing a BFILE with FILECLOSE. */ #include <oratypes.h> #include <lobdemo.h> void BfileFileClose_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { printf ("----------- OCILobFileOpen Demo --------------\n"); checkerr(errhp, OCILobFileOpen(svchp, errhp, Bfile_loc, (ub1) OCI_FILE_READONLY)); checkerr(errhp, OCILobFileClose(svchp, errhp, Bfile_loc)); }
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fclose_f.java */ // Closing a BFILE with FILECLOSE. 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_45 { 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; boolean result = false; rset = stmt.executeQuery ( "SELECT BFILENAME('ADGRAPHIC_DIR','keyboard_graphic_3106_11001') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } result = src_lob.isFileOpen(); System.out.println( "result of fileIsOpen() before opening file : " + result); src_lob.openFile(); result = src_lob.isFileOpen(); System.out.println( "result of fileIsOpen() after opening file : " + result); // Close the BFILE, statement and connection: src_lob.closeFile(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to close a BFILE with the CLOSE function.
|
Note: This function (CLOSE) is recommended for new application development. The older FILECLOSE function, is not recommended for new development. |
| See Also:
Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment. |
Opening and closing a BFILE is mandatory. You must close the instance at some point later in the session.
Use the following syntax references for each programmatic environment:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fclose_c.sql */ /* Closing a BFILE with CLOSE. Procedure closeBFILE_procTwo is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE closeBFILE_procTwo IS file_loc BFILE := BFILENAME('MEDIA_DIR', 'keyboard_logo.jpg'); BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE CLOSE EXAMPLE ------------'); DBMS_LOB.OPEN(file_loc, DBMS_LOB.LOB_READONLY); /* ...Do some processing. */ DBMS_LOB.CLOSE(file_loc); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/fclose_c.c */ /* Closing a BFILE with CLOSE. */ #include <oratypes.h> #include <lobdemo.h> void BfileLobClose_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { printf ("----------- OCILobOpen Demo --------------\n"); checkerr(errhp, OCILobOpen(svchp, errhp, Bfile_loc, (ub1) OCI_LOB_READONLY)); checkerr(errhp, OCILobClose(svchp, errhp, Bfile_loc)); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fclose_c.pco * Closing a BFILE with CLOSE. IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-CLOSE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BFILE1 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. BFILE-CLOSE. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the BFILE locators: EXEC SQL ALLOCATE :BFILE1 END-EXEC. * Set up the directory and file information: MOVE "ADGRAPHIC_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "keyboard_graphic_3106_13001" TO FNAME-ARR. MOVE 13 TO FNAME-LEN. EXEC SQL LOB FILE SET :BFILE1 DIRECTORY = :DIR-ALIAS, FILENAME = :FNAME END-EXEC. EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC. * Close the LOB: EXEC SQL LOB CLOSE :BFILE1 END-EXEC. * And free the LOB locator: EXEC SQL FREE :BFILE1 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fclose_c.pc */ /* Closing a BFILE with CLOSE. Pro*C/C++ has only one form of CLOSE for BFILEs. Pro*C/C++ has no FILECLOSE statement. A simple CLOSE statement is used instead: */ #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 closeBFILE_proc() { OCIBFileLocator *Lob_loc; char *Dir = "ADGRAPHIC_DIR", *Name = "mousepad_graphic_2056_12001"; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name; 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; closeBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/fclose_c.bas 'Closing a BFILE with CLOSE. Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdGraphic As OraBfile, amount_read%, chunksize%, chunk Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&) chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT) Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value If OraAdGraphic.IsOpen Then 'Process because the file is already open OraAdGraphic.Close End If
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fclose_c.java */ // Closing a BFILE with CLOSE. 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_48 { 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('ADGRAPHIC_DIR', 'keyboard_graphic_3106_13001') FROM DUAL"); OracleCallableStatement cstmt = null; if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); 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: cstmt = (OracleCallableStatement) conn.prepareCall ("begin dbms_lob.close(?); end;"); cstmt.setBFILE(1,src_lob); cstmt.execute(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to close all open BFILEs.
You are responsible for closing any BFILE instances at some point before your program terminates. For example, you must close any open BFILE instance before the termination of a PL/SQL block or OCI program.
You must close open BFILE instances even in cases where an exception or unexpected termination of your application occurs. In these cases, if a BFILE instance is not closed, then it is still considered open by the database. Ensure that your exception handling strategy does not allow BFILE instances to remain open in these situations.
See Also:
|
Use the following syntax references for each programmatic environment:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fclosea.sql */ /* Closing all open BFILEs. Procedure closeAllOpenFilesBFILE_proc is not part of DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE closeAllOpenBFILEs_proc IS BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE CLOSEALL EXAMPLE ------------'); /* Close all open BFILEs: */ DBMS_LOB.FILECLOSEALL; END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/fclosea.c */ /* Closing all open BFILEs. */ #include <oratypes.h> #include <lobdemo.h> void BfileCloseAll_proc(OCILobLocator *Bfile_loc1, OCILobLocator *Bfile_loc2, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { printf ("----------- OCILobFileCloseAll Demo --------------\n"); 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)); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/fclosea.pco * Closing all open BFILEs. 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 "ADGRAPHIC_DIR" TO DIR-ALIAS1-ARR. MOVE 9 TO DIR-ALIAS1-LEN. MOVE "keyboard_graphic_3106_13001" 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 "ADGRAPHIC_DIR" TO DIR-ALIAS2-ARR. MOVE 9 TO DIR-ALIAS2-LEN. MOVE "mousepad_graphic_2056_12001" 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/fclosea.pc */ /* Closing all open BFILEs. */ #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 ad_graphic INTO :Lob_loc1 FROM Print_media WHERE product_id = 2056 AND ad_id = 12001; EXEC SQL SELECT Mtab.ad_graphic INTO Lob_loc2 FROM Print_media PMtab WHERE PMtab.product_id = 3060 AND ad_id = 11001; /* 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; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/fclosea.bas 'Closing all open BFILEs. Dim OraParameters as OraParameters, OraAdGraphic as OraBFile OraConnection.BeginTrans Set OraParameters = OraDatabase.Parameters 'Define in out parameter of BFILE type: OraParameters.Add "MyAdGraphic", Null,ORAPARAM_BOTH,ORATYPE_BFILE 'Select the ad graphic BFile for product_id 2268: OraDatabase.ExecuteSQL("Begin SELECT ad_graphic INTO :MyAdGraphic FROM Print_media WHERE product_id = 2268 AND ad_id = 21001; END; " ) 'Get the BFile ad_graphic column: set OraAdGraphic = OraParameters("MyAdGraphic").Value 'Open the OraAdGraphic: OraAdGraphic.Open 'Do some processing on OraAdGraphic 'Close all the BFILEs associated with OraAdGraphic: OraAdGraphic.CloseAll
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/fclosea.java */ // Closing all open BFILEs. 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 ad_graphic FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBFILE (1); } rset = stmt.executeQuery ( "SELECT BFILENAME('ADGRAPHIC_DIR', 'keyboard_graphic_3106_13001') 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(); } } }
This section describes how to INSERT a row containing a BFILE by initializing a BFILE locator.
See Also:
|
You must initialize the BFILE locator bind variable to NULL or a directory object and filename before issuing the INSERT statement.
See the following syntax references for each programmatic environment:
Examples in the following programmatic environments are provided:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/finsert.sql */ /* Inserting row containing a BFILE by initializing a BFILE locator */ CREATE OR REPLACE PROCEDURE insertBFILE_proc IS /* Initialize the BFILE locator: */ Lob_loc BFILE := BFILENAME('MEDIA_DIR', 'keyboard_logo.jpg'); BEGIN DBMS_OUTPUT.PUT_LINE('------------ BFILE INSERT EXAMPLE ------------'); INSERT INTO print_media (product_id, ad_id, ad_graphic) VALUES (3106, 13002, Lob_loc); END; /
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/finsert.c */ /* Inserting a row by initializing a BFILE Locator. */ #include <oratypes.h> #include <lobdemo.h> void BfileInsert_proc(OCILobLocator *Bfile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { text *insstmt = (text *) "INSERT INTO Print_media (product_id, ad_id, ad_graphic) \ VALUES (2056, 60315, :Lob_loc)"; OCIBind *bndhp; OraText *Dir = (OraText *)"MEDIA_DIR", *Name = (OraText *)"keyboard_logo.jpg"; printf ("----------- OCI BFILE Insert Demo --------------\n"); /* Prepare the SQL statement: */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) strlen((char *) insstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); checkerr (errhp, OCILobFileSetName(envhp, errhp, &Bfile_loc, Dir, (ub2)strlen((char *)Dir), Name,(ub2)strlen((char *)Name))); checkerr (errhp, OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (void *) &Bfile_loc, (sb4) 0, SQLT_BFILE, (void *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)); /* Execute the SQL statement: */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/finsert.pco * Inserting a row containing a BFILE by initializing a BFILE IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-INSERT-INIT. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 TEMP-BLOB SQL-BLOB. 01 SRC-BFILE SQL-BFILE. 01 DIR-ALIAS PIC X(30) VARYING. 01 FNAME PIC X(20) VARYING. 01 DIR-IND PIC S9(4) COMP. 01 FNAME-IND PIC S9(4) COMP. 01 AMT 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-INSERT-INIT. 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 "ADGRAPHIC_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "keyboard_graphic_3106_13001" TO FNAME-ARR. MOVE 16 TO FNAME-LEN. * Set the directory object and filename in locator: EXEC SQL LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS, FILENAME = :FNAME END-EXEC. EXEC SQL INSERT INTO PRINT_MEDIA (PRODUCT_ID, AD_GRAPHIC) VALUES (3106, :SRC-BFILE)END-EXEC. EXEC SQL ROLLBACK WORK END-EXEC. EXEC SQL FREE :SRC-BFILE 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/finsert.pc */ /* Inserting a row containing a BFILE by initializing a BFILE */ #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 insertBFILELocator_proc() { OCIBFileLocator *Lob_loc; char *Dir = "ADGRAPHIC_DIR", *Name = "mousepad_graphic_2056_12001"; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate the input Locator: */ EXEC SQL ALLOCATE :Lob_loc; /* Set the Directory and Filename in the Allocated (Initialized) Locator: */ EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name; EXEC SQL INSERT INTO Print_media (Product_ID, ad_graphic) VALUES (2056, :Lob_ loc); /* Release resources held by the Locator: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; insertBFILELocator_proc(); EXEC SQL ROLLBACK WORK RELEASE; } }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/finsert.bas ' Inserting a row containing a BFILE by initializing a BFILE. Dim OraDyn as OraDynaset, OraPhoto as OraBFile, OraMusic as OraBFile Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("ad_graphic").Value 'Edit the first row and initiliaze the "ad_graphic" column: OraDyn.Edit OraPhoto.DirectoryName = "ADGRAPHIC_DIR" OraPhoto.Filename = "mousepad_graphic_2056_12001" OraDyn.Update
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/finsert.java */ // Inserting a row containing a BFILE by initializing a BFILE. // Java IO classes: 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_26 { 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; OracleCallableStatement cstmt = null; rset = stmt.executeQuery ( "SELECT BFILENAME('ADGRAPHIC_DIR','monitor_graphic_3060_11001') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } // Prepare a CallableStatement to OPEN the LOB for READWRITE: cstmt = (OracleCallableStatement) conn.prepareCall ( "INSERT INTO Print_media (product_id, ad_graphic) VALUES (3060, ?)"); cstmt.setBFILE(1, src_lob); cstmt.execute(); //Close the statements and commit the transaction: stmt.close(); cstmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|