Release 8.1.5
A68004-01
 Library |
 Product |
 Contents |
 Index |
| Oracle 8i Application Developer's Guide - Large Objects (LOBs) Release 8.1.5 A68004-01 |
|
In this chapter we describe how to work with External LOBs (BFILEs) in terms of use cases. That is, we discuss each operation on a LOB (such as "See If a Temporary LOB is Open") in terms of a use case by that name. The table listing all the use cases is provided at the head of the chapter (see "Use Case Model: External LOBs"). A summary figure, "Use Case Model Diagram: External LOBs", locates all the use cases in single drawing. If you are using the HTML version of this document, you can use this figure to navigate to the use case in which you are interested by clicking on the relevant use case title.
The individual use cases are themselves laid out as follows:
| Use Case and Page |
|---|
|
INSERT a Row Containing a BFILE by Initializing a BFILE Locator |
The DIRECTORY object enables administering the access and usage of BFILEs in an Oracle Server (see the CREATE DIRECTORY command in the Oracle8i SQL Reference). A DIRECTORY specifies a logical alias name for a physical directory on the server's filesystem under which the file to be accessed is located. You can access a file in the server's filesystem only if granted the required access privilege on the DIRECTORY object.
The DIRECTORY object also provides the flexibility to manage the locations of the files, instead of forcing you to hardcode the absolute pathnames of the physical files in your applications. A DIRECTORY alias is used in conjunction with the BFILENAME() function (in SQL and PL/SQL), or the OCILobFileSetName() (in OCI) for initializing a BFILE locator.
In order to associate an operating system file to a BFILE, it is necessary to first create a DIRECTORY object which is an alias for the full pathname to the operating system file.
You use Oracle SQL DML to associate existing operating system files with the relevant database records of a particular table. You can use the SQL INSERT statement to initialize a BFILE column to point to an existing file in the server's filesystem, and you can use a SQL UPDATE statement to change the reference target of the BFILE. You can also initialize a BFILE to NULL and then update it later to refer to an operating system file via the BFILENAME() function. OCI users can also use OCILobFIleSetName() to initialize a BFILE locator variable that is then used in the VALUES clause of an INSERT statement.
For example, 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.dif and image2.dif are stored.
INSERT INTO Lob_table VALUES (21, BFILENAME('IMG', 'Image1.gif')); INSERT INTO Lob_table VALUES (22, BFILENAME('IMG', 'image2.gif'));
The UPDATE statement below 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 is used to initialize the BFILE column to point to the 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 UPDATE statements below 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;
You should think of BFILENAME() in terms of initialization -- it can initialize the value for both a BFILE column and that of a BFILE (automatic) variable declared inside a PL/SQL module. This has the unique advantage that if your need for a particular BFILE is temporary, and scoped just within the module on which you are working, you can utilize the BFILE related APIs on the variable without ever having to associate this with a column in the database. There is a further advantage as well. Since you are not forced to create a BFILE column in a server side table, initialize this column value, and then retrieve this column value via a SELECT, you save a roundtrip to the server.
For more information, refer to the example given for DBMS_LOB.LOADFROMFILE (see "Load a LOB with Data from a BFILE"). 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 done 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';
the directory object's name is 'Mary_Dir'. Use 'SCOTT_DIR' and 'Mary_Dir' when calling BFILENAME(). For example:
BFILENAME('SCOTT_DIR', 'afile') BFILENAME('Mary_Dir', 'afile')
This section introduces the BFILE security model and the associated SQL DDL and DML. The main features for BFILE security are:
CREATE and REPLACE/ALTER a DIRECTORY object.
GRANT and REVOKE the READ system and object privileges on DIRECTORY objects.
The DIRECTORY is a system owned object. For more information on system owned objects, see Oracle8i SQL Reference. Oracle8i supports two new system privileges, which are granted only to the DBA account:
CREATE ANY DIRECTORY -- for creating or altering the directory object creation
DROP ANY DIRECTORY -- for deleting the directory object
The READ privilege on the DIRECTORY object allows 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 privilege with GRANT option, you may in turn grant this privilege to other users/roles and add them to your privilege domains.
It is important to note that the READ privilege is defined only on the DIRECTORY object. 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 DBA's responsibility to ensure that the physical directory exists, and read permission for the Oracle Server process is enabled on the file, the directory, and the path leading to it. It is also the DBA's responsibility to make sure that the directory remains available, and the read permission remains enabled, for the entire duration of file access by database users.
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 Oracle8i SQL Reference for information about the following SQL DDL commands that create, replace, and drop directory objects:
Refer to the Oracle8i SQL Reference for information about the following SQL DML commands 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 their corresponding paths and privileges. The supported views are:
ALL_DIRECTORIES (OWNER, DIRECTORY_NAME, DIRECTORY_PATH)
This view describes all the directories accessible to the user.
DBA_DIRECTORIES(OWNER, DIRECTORY_NAME, DIRECTORY_PATH)
This view describes all the 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 filesystem. But to realize this goal, it is very important that the DBA follow these guidelines when using directory objects:
DIRECTORY should not be mapped to physical directories which contain Oracle data files, control files, log files, and other system files. Tampering with these files (accidental or otherwise) could potentially 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, DBMS_LOB or OCI 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, 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 shutdown.
The only recourse left to PL/SQL users, for example, will be to either execute 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.
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 execute 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.
Oracle8i does not support session migration for BFILEs in MTS mode. This implies that operations on open BFILEs can persist beyond the end of a call to an MTS server. Sessions involving BFILE operations need to be bound to one shared server, they cannot migrate from one server to another.
For BFILEs, the value is stored in a server-side operating system file; i.e., 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), 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 in a PL/SQL or OCI program behaves like any other automatic variable. With respect to file operations, it behaves 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.
The 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, in PL/SQL and OCI programs.
If the object contains a BFILE, 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(). It is an error to INSERT/UPDATE a BFILE without indicating a directory alias 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 alias and filename before issuing the insert or update statement. Note that OCISetAttr() does not allow the user to set a BFILE locator to NULL.
General rule: Before using SQL to insert or update a row with a BFILE, the user must either initialize the BFILE
It is possible to incorporate BFILEs into tables in three ways.
BFILEs may be columns in a table -- see "CREATE a Table of an Object Type with a BFILE Attribute"
BFILEs may be attributes of an object type -- see "CREATE a Table of an Object Type with a BFILE Attribute"
BFILEs may be contained within a nested table -- see "CREATE a Table with a Nested Table Containing a BFILE"
In all cases SQL DDL is used -- to define BFILE columns in a table and BFILE attributes in an object type.
The heart of our hypothetical application is the table Multimedia_tab. The varied types which make up the columns of this table make it possible to collect together the many different kinds multimedia elements used in the composition of clips.
Because you can use SQL DDL directly to create a table containing one or more LOB columns, it is not necessary to use the DBMS_LOB package.
CREATE TABLE Multimedia_tab ( Clip_ID NUMBER NOT NULL, Story CLOB default EMPTY_CLOB(), FLSub NCLOB default EMPTY_CLOB(), Photo BFILE default NULL, Frame BLOB default EMPTY_BLOB(), Sound BLOB default EMPTY_BLOB(), Voiced_ref REF Voiced_typ, InSeg_ntab InSeg_tab, Music BFILE default NULL, Map_obj Map_typ ) NESTED TABLE InSeg_ntab STORE AS InSeg_nestedtab;
As shown in the diagram, you must create the object type that contains the BFILE attributes before you can proceed to create a table that makes use of that object type.
Our example application contains examples of two different ways in which object types can contain BFILEs:
Multimedia_tab contains a column Voiced_ref that references row objects in the table VoiceOver_tab which is based on the type Voiced_typ. This type contains two kinds of LOBs -- a CLOB to store the script that's read by the actor, and a BFILE to hold the audio recording.
Multimedia_tab contains a column Map_obj that contains column objects of the type Map_typ. This type utilizes the BFILE datatype for storing aerial pictures of the region.
/* Create type Voiced_typ as a basis for tables that can contain recordings of voice-over readings using SQL DDL: */ CREATE TYPE Voiced_typ AS OBJECT ( Originator VARCHAR2(30), Script CLOB, Actor VARCHAR2(30), Take NUMBER, Recording BFILE ); /* Create table Voiceover_tab Using SQL DDL: */ CREATE TABLE Voiceover_tab OF Voiced_typ ( Script DEFAULT EMPTY_CLOB(), CONSTRAINT Take CHECK (Take IS NOT NULL), Recording DEFAULT NULL ); /* Create Type Map_typ using SQL DDL as a basis for the table that will contain the column object: */ CREATE TYPE Map_typ AS OBJECT ( Region VARCHAR2(30), NW NUMBER, NE NUMBER, SW NUMBER, SE NUMBER, Drawing BLOB, Aerial BFILE ); /* Create support table MapLib_tab as an archive of maps using SQL DDL: */ CREATE TABLE Map_tab of MapLib_typ;
|
For more information see:
-- Oracle8i SQL Reference for a complete specification of the syntax for using |
As shown in the diagram, you must create the object type that contains the BFILE attributes before you can proceed to create a nested table based on that object type.
In our example, Multimedia_tab contains a nested table Inseg_ntab that is based on the type InSeg_typ. This type makes use of two LOB datatypes -- a BFILE for audio recordings of the interviews, and a CLOB should the user wish to make transcripts of the recordings.
We have already described how to create a table with BFILE columns (see "CREATE a Table Containing a BFILE"), so here we only describe the SQL DDL syntax the creating the underlying type:
Because you use SQL DDL directly to create a table, the DBMS_LOB package is not relevant.
CREATE TYPE InSeg_typ AS OBJECT ( Segment NUMBER, Interview_Date DATE, Interviewer VARCHAR2(30), Interviewee VARCHAR2(30), Recording BFILE, Transcript CLOB );
The actual embedding of the nested table is accomplished when the structure of the containing table is defined. In our example, this is effected by means of the following statement at the time that Multimedia_tab is created.
NESTED TABLE InSeg_ntab STORE AS InSeg_nestedtab;
Note that before you insert, you must initialize the BFILE either to NULL or to a directory alias and filename.
The BFILENAME() function should be called as part of a SQL INSERT to initialize a BFILE column or attribute for a particular row by associating it with a physical file in the server's filesystem.
The DIRECTORY object represented by the directory_alias parameter to this function need not already be defined using SQL DDL before the BFILENAME() function is called in SQL DML or a PL/SQL program. However, the directory object and operating system file must exist by the time you actually use the BFILE locator (for example, as having been used as a parameter to an operation such as OCILobFileOpen(), DBMS_LOB.FILEOPEN(), OCILobOpen(), or DBMS_LOB.OPEN()).
Note that BFILENAME() does not validate privileges on this DIRECTORY object, or check if the physical directory that the DIRECTORY object represents actually exists. These checks are performed only during file access using the BFILE locator that was initialized by the BFILENAME() function.
You can use BFILENAME() as part of a SQL INSERT and UPDATE statement to initialize a BFILE column. You can also use it to initialize a BFILE locator variable in a PL/SQL program, and use that locator for file operations. However, if the corresponding directory alias and/or filename does not exist, then PL/SQL DBMS_LOB routines that use this variable will generate errors.
The directory_alias parameter in the BFILENAME() function must be specified taking case-sensitivity of the directory name into consideration.
/* Note that this is the same insert statement as applied to internal persistent LOBs but with the BFILENAME() function added to initialize the BFILE columns: */ INSERT INTO Multimedia_tab VALUES (1, EMPTY_CLOB(), EMPTY_CLOB(), BFILENAME('PHOTO_DIR', 'LINCOLN_PHOTO'), EMPTY_BLOB(), EMPTY_BLOB(), VOICED_TYP('Abraham Lincoln', EMPTY_CLOB(), 'James Earl Jones', 1, NULL), NULL, BFILENAME('AUDIO_DIR', 'LINCOLN_AUDIO'), MAP_TYP('Gettysburg', 23, 34, 45, 56, EMPTY_BLOB(), NULL));
/* Insert a row using BFILENAME: */ void insertUsingBfilename(svchp, stmthp, errhp) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; { text *insstmt = (text *) "INSERT INTO Multimedia_tab VALUES (3, EMPTY_CLOB(), EMPTY_CLOB(), BFILENAME('PHOTO_DIR', 'Lincoln_photo'), EMPTY_BLOB(), EMPTY_BLOB(), VOICED_TYP('Abraham Lincoln', EMPTY_CLOB(), 'James Earl Jones', 1, NULL), NULL, BFILENAME('AUDIO_DIR', 'Lincoln_audio'), MAP_TYP('Gettysburg', 23, 34, 45, 56, EMPTY_BLOB(), NULL))"; /* Prepare the SQL statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) strlen((char *) insstmt), (ub4) OCI_NTV_SYNTAX, (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));
}
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-INSERT. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. EXEC SQL INSERT INTO MULTIMEDIA_TAB (CLIP_ID, PHOTO) VALUES (1, BFILENAME('PHOTO_DIR', 'LINCOLN_PHOTO')) END-EXEC. EXEC SQL COMMIT 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.
#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 BFILENAMEInsert_proc() { EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL WHENEVER NOT FOUND CONTINUE; /* Delete any existing row: */ EXEC SQL DELETE FROM Multimedia_tab WHERE Clip_ID = 1; /* Insert a new row using the BFILENAME() function for BFILEs: */ EXEC SQL INSERT INTO Multimedia_tab VALUES (1, EMPTY_CLOB(), EMPTY_CLOB(), BFILENAME('PHOTO_DIR', 'Lincoln_photo'), EMPTY_BLOB(), EMPTY_BLOB(), NULL, InSeg_tab(InSeg_typ(1, NULL, 'Ted Koppell', 'Abraham Lincoln', BFILENAME('AUDIO_DIR', 'Lincoln_audio'), EMPTY_CLOB())), BFILENAME('AUDIO_DIR', 'Lincoln_audio'), Map_typ('Moon Mountain', 23, 34, 45, 56, EMPTY_BLOB(), BFILENAME('PHOTO_DIR', 'Lincoln_photo'))); printf("Inserted %d row\n", sqlca.sqlerrd[2]); } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; BFILENAMEInsert_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
Dim OraDyn as OraDynaset, OraPhoto as OraBFile, OraMusic as OraBFile Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("Music").Value Set OraPhoto = OraDyn.Fields("Photo").Value OraDyn.AddNew OraDyn.Fields("Clip_ID").value = 1 OraDyn.Fields("Story").value = Empty 'This is equivalent to EMPTY_BLOB() in SQL OraDyn.Fields("FLSub").value = Empty 'Initialize BFile Data:OraPhoto.Directory = "PHOTO_DIR" OraPhoto.FileName = "LINCOLN_PHOTO" OraDyn.Fields("Frame").Value = Empty OraDyn.Fields("Sound").Value = Empty 'Initialize BFile Data:OraMusic.DirectoryName = "AUDIO_DIR" OraMusic.FileName = "LINCOLN_AUDIO" OraDyn.Edit OraDyn.Update 'Add the row to the table
// 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_21 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 { stmt.execute("INSERT INTO multimedia_tab VALUES (99, EMPTY_CLOB(), EMPTY_CLOB(), BFILENAME ('PHOTO_DIR','Lincoln_photo'), EMPTY_BLOB(), EMPTY_BLOB(), (SELECT REF(Vref) FROM Voiceover_tab Vref WHERE Actor = 'James Earl Jones'), NULL, BFILENAME('AUDIO_DIR', 'Lincoln_audio'), MAP_TYP('Gettysburg', 23, 34, 45, 56, EMPTY_BLOB(), NULL))"); // Commit the transaction: conn.commit(); } catch (SQLException e) { e.printStackTrace(); } } }
With regard to LOBs, one of the advantages of utilizing an object-relational approach is that you can define a type as a common template for related tables. For instance, it makes sense that both the tables that store archival material and the working tables that use those libraries share a common structure. The following code fragment is based on the fact that a library table VoiceoverLib_tab is of the same type (Voiced_typ) as Voiceover_tab referenced by the Voiced_ref column of the Multimedia_tab table. It inserts values from the library table into Multimedia_tab by means of a SELECT operation.
INSERT INTO Voiceover_tab (SELECT * from VoiceoverLib_tab WHERE Take = 12345);
Note that you must initialize the BFILE locator bind variable to a directory alias and filename before issuing the insert statement. In this case we insert a Photo from an operating system source file (PHOTO_DIR).
DECLARE /* Initialize the BFILE locator: */ Lob_loc BFILE := BFILENAME('PHOTO_DIR', 'Washington_photo'); BEGIN INSERT INTO Multimedia_tab (Clip_ID, Photo) VALUES (3, Lob_loc); COMMIT; END;
/* Insert a row using BFILE Locator: */ void insertUsingBfileLocator(envhp, svchp, stmthp, errhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIError *errhp; { text *insstmt = (text *) "INSERT INTO Multimedia_tab (Clip_ID, Photo) VALUES (3, :Lob_loc)"; OCIBind *bndhp; OCILobLocator *Lob_loc; OraText *Dir = (OraText *)"PHOTO_DIR", *Name = (OraText *)"Washington_photo"; /* Prepare the SQL statement: */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) strlen((char *) insstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); * Allocate Locator resources: */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0)) checkerr (errhp, OCILobFileSetName(envhp, errhp, &Lob_loc, Dir, (ub2)strlen((char *)Dir), Name,(ub2)strlen((char *)Name))); checkerr (errhp, OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (dvoid *) &Lob_Loc, (sb4) 0, SQLT_BFILE, (dvoid *) 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)); /* Free LOB resources: */ OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_FILE);
}
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-INSERT-INIT. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 "PHOTO_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "washington_photo" TO FNAME-ARR. MOVE 16 TO FNAME-LEN. * Set the directory alias and filename in locator: EXEC SQL LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS, FILENAME = :FNAME END-EXEC. EXEC SQL INSERT INTO MULTIMEDIA_TAB (CLIP_ID, PHOTO) VALUES (6, :SRC-BFILE) END-EXEC. EXEC SQL COMMIT 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.
#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 = "PHOTO_DIR", *Name = "Washington_photo"; 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 Multimedia_tab (Clip_ID, Photo) VALUES (4, :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; }
Dim OraDyn as OraDynaset, OraPhoto as OraBFile, OraMusic as OraBFile Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("Music").Value Set OraPhoto = OraDyn.Fields("Photo").Value 'Edit the first row and initiliaze the "Photo" column: OraDyn.Edit OraPhoto.DirectoryName = "PHOTO_DIR" OraPhoto.Filename = "Washington_photo" OraDynaset.Update
// 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: Class.forName ("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('PHOTO_DIR', 'Washington_photo') 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 multimedia_tab (clip_id, photo) VALUES (3, ?)"); 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(); } } }
The BFILE datatype stores unstructured binary data in operating-system files outside of the database. A BFILE column or attribute stores a file locator that points to a server-side external file containing the data
The SQL Loader assumes that the necessary directory objects (a logical alias name for a physical directory on the server's filesystem) have already been created.
A control file field corresponding to a BFILE column consists of column name followed by the BFILE directive. The BFILE directive takes as arguments a DIRECTORY OBJECT name followed by a BFILE name. Both of these can be provided as string constants, or they can be dynamically sourced through some other field.
The following two examples illustrate the loading of BFILES. In the first example only the file name is specified dynamically. In the second example, the BFILE and the DIRECTORY OBJECT are specified dynamically.
LOAD DATA INFILE sample9.dat INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' (Clip_ID INTEGER EXTERNAL(5), FileName FILLER CHAR(30), Photo BFILE(CONSTANT "DETECTIVE_PHOTO", FileName))
007,/tmp/JamesBond.jpeg, 008,/tmp/SherlockHolmes.jpeg, 009,/tmp/MissMarple.jpeg,
Clip_ID defaults to (255) if a size is not specified; it is mapped to the file names in the datafile. Detectivel_dir is the directory where all the files are stored (Detectivel_dir is a directory object created previously).
Control File:
LOAD DATA INFILE sample10.dat INTO TABLE Multimedia_tab replace FIELDS TERMINATED BY ',' ( Clip_ID INTEGER EXTERNAL(5), Photo BFILE (DirName, FileName), FileName FILLER CHAR(30), DirName FILLER CHAR(30) )
007,JamesBond.jpeg,DETECTIVE_PHOTO, 008,SherlockHolmes.jpeg,DETECTIVE_PHOTO, 009,MissMarple.jpeg,PHOTO_DIR,
DirName FILLER CHAR (30) is mapped to the datafile field containing the directory name corresponding to the file being loaded.
In using the 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. However, no implicit translation is ever performed from binary data to a character set. When you use the loadfromfile operation to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. In that case, you will need to perform character set conversions on the BFILE data before executing loadfromfile.
The example procedure assumes that there is an operating system source file (AUDIO_DIR) that contains the LOB data to be loaded into the target LOB (Music).
/* Note that the example procedure loadLOBFromBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc IS Dest_loc BLOB; Src_loc BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio'); Amount INTEGER := 4000; BEGIN SELECT Music INTO Dest_loc FROM Multimedia_tab WHERE Clip_ID = 3 FOR UPDATE; /* Opening the LOB 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); COMMIT; END;
/* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Define the column being selected */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } /* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Define the column being selected */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); /* Free the locator descriptors */ OCIDescriptorFree((dvoid *)dest_loc, (ub4)OCI_DTYPE_BLOB); OCIDescriptorFree((dvoid *)dest_loc, (ub4)OCI_DTYPE_FILE); } void loadLobFromBfile(svchp, errhp, dest_loc, src_loc) OCISvcCtx *svchp; OCIError *errhp; OCILobLocator *dest_loc; /* These locators have been already allocated */ OCILobLocator *src_loc; /* This is the BFILE locator. */ { checkerr(errhp, OCILobFileOpen(svchp, errhp, src_loc, (ub1)OCI_FILE_READONLY)); checkerr(errhp, OCILobOpen(svchp, errhp, dest_loc, (ub1)OCI_FILE_READWRITE)); checkerr (errhp, OCILobLoadFromFile(svchp, errhp, dest_loc, src_loc, (ub4)4000, (ub4)0, (ub4)0)); checkerr(errhp, OCILobClose(svchp, errhp, dest_loc)); checkerr(errhp, OCILobFileClose(svchp, errhp, src_loc)); }
IDENTIFICATION DIVISION. PROGRAM-ID. LOAD-BFILE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 "AUDIO_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "washington_audio" 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 PHOTO INTO :SRC-BFILE FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 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 COMMIT 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.
#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 = "AUDIO_DIR", *Name = "Washington_audio"; 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 Sound INTO :Dest_loc FROM Multimedia_tab WHERE Clip_ID = 3 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; }
'Note that this code fragment assumes a ORABFILE object as the result of a
'dynaset operation. This object could have been an OUT parameter of a PL/SQL
'procedure. For more information please refer to chapter 1.
Dim OraDyn as OraDynaset, OraDyn2 as OraDynaset, OraPhoto as OraBFile
Dim OraImage as OraLob
chunksize = 32768
Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT)
Set OraDyn2 = OraDb.CreateDynaset("select * from Images", ORADYN_DEFAULT)
Set OraPhoto = OraDyn.Fields("Photo").value
Set OraImage = OraDyn2.Fields("Image").value
OraDyn2.Edit
'Load LOB with data from BFILE:
OraImage.CopyFromBFile (OraPhoto)
OraDyn2.Update
// 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 Ex2_45 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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; BLOB dest_lob = null; InputStream in = null; OutputStream out = null; byte buf[] = new byte[1000]; ResultSet rset = null; OracleCallableStatement cstmt = null; // Prepare a CallableStatement to OPEN the LOB for READWRITE: cstmt = (OracleCallableStatement) conn.prepareCall ( "BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READWRITE); END;"); rset = stmt.executeQuery ( "SELECT BFILENAME('AUDIO_DIR', 'Washington_audio') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); src_lob.openFile(); in = src_lob.getBinaryStream(); } rset = stmt.executeQuery ( "SELECT sound FROM multimedia_tab WHERE clip_id = 2 FOR UPDATE"); if (rset.next()) { dest_lob = ((OracleResultSet)rset).getBLOB (1); // Bind the dest_lob to the prepared statement and execute it: cstmt.setBLOB(1, dest_lob); cstmt.execute(); // Fetch the output stream for dest_lob: out = dest_lob.getBinaryOutputStream(); } int length = 0; int pos = 0; while ((in != null) && (out != null) && ((length = in.read(buf)) != -1)) { System.out.println("Pos = " + Integer.toString(pos) + ". Length = " + Integer.toString(length)); pos += length; out.write(buf, pos, length); } // Close all streams and file handles: in.close(); out.flush(); out.close(); src_lob.closeFile(); // All OPENed LOBS must be CLOSEd: cstmt = (OracleCallableStatement) conn.prepareCall ( "BEGIN DBMS_LOB.CLOSE(?); END;"); cstmt.setBLOB(1, dest_lob); cstmt.execute(); // Commit the transaction: conn.commit(); } catch (SQLException e) { e.printStackTrace(); } } }
As you can see by comparing the code, these alternative methods are very similar. However, while you can continue to use the older FILEOPEN form, we strongly recommend that you switch to using OPEN because this facilitates future extensibility.
A limited number of BFILEs can be open simultaneously per session. The maximum number is specified by using the initialization parameter SESSION_MAX_OPEN_FILES.
SESSION_MAX_OPEN_FILES defines an upper limit on the number of simultaneously open files in a session. The default value for this parameter is 10. That is, a maximum of 10 files can be opened simultaneously per session if the default value is utilized. The database administrator can change the value of this parameter in the init.ora file. For example:
SESSION_MAX_OPEN_FILES=20
If the number of unclosed files exceeds the SESSION_MAX_OPEN_FILES value then you will not be able to open any more files in the session. To close all open files, use the FILECLOSEALL call.
While you can continue to use the older FILEOPEN form, we strongly recommend that you switch to using OPEN, because this facilitates future extensibility. This example opens a Lincoln_photo in operating system file PHOTO_DIR.
/* Note that the example procedure openBFILE_procOne is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE openBFILE_procOne IS Lob_loc BFILE := BFILENAME('PHOTO_DIR', 'Lincoln_photo'); BEGIN /* Open the BFILE: */ DBMS_LOB.FILEOPEN (Lob_loc, DBMS_LOB.FILE_READONLY) /* ... Do some processing. */ DBMS_LOB.FILECLOSE(Lob_loc); END;
/* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Define the column being selected */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } void BfileOpen(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been allocated and initialized. */ OCILobLocator *bfile_loc; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Set the bfile locator information */ checkerr(errhp, (OCILobFileSetName(envhp, errhp, &bfile_loc, (OraText *)"PHOTO_DIR", (ub2)strlen("PHOTO_DIR"), (OraText *)"Lincoln_photo", (ub2)strlen("Lincoln_photo")))); checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc, (ub1)OCI_FILE_READONLY)); /* ... Do some processing. */ checkerr(errhp, OCILobFileClose(svchp, errhp, bfile_loc)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); } void BfileOpen(envhp, errhp, svchp, stmthp, bfile_loc) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCILobLocator *bfile_loc; /* This is the BFILE locator that is already allocated and initialized. */ { checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc, (ub1)OCI_FILE_READONLY)); /* ... Do some processing. */ checkerr(errhp, OCILobFileClose(svchp, errhp, bfile_loc)); }
|
Note:
At the present time, OO4O only offers |
// 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_38 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("oracle.jdbc.driver.OracleDriver"); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BFILE src_lob = null; ResultSet rset = null; rset = stmt.executeQuery ( "SELECT BFILENAME('PHOTO_DIR', 'Lincoln_photo') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); // plsql_fileOpen() wraps a call to dbms_lob.fileopen(): src_lob.plsql_fileOpen(); System.out.println("The file is now open"); } // Close the BFILE, statement and connection: src_lob.plsql_fileClose(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This example opens a Lincoln_photo in operating system file PHOTO_DIR.
/* Note that the example procedure openBFILE_procTwo is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE openBFILE_procTwo IS Lob_loc BFILE := BFILENAME('PHOTO_DIR', 'Lincoln_photo'); BEGIN /* Open the BFILE: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY) /* ... Do some processing: */ DBMS_LOB.CLOSE(Lob_loc); END;
/* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } void BfileFileOpen(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been allocated and initialized. */ OCILobLocator *bfile_loc; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Set the Bfile Locator Information */ checkerr(errhp, (OCILobFileSetName(envhp, errhp, &bfile_loc, (OraText *)"PHOTO_DIR", (ub2)strlen("PHOTO_DIR"), (OraText *)"Lincoln_photo", (ub2)strlen("Lincoln_photo")))); checkerr(errhp, OCILobOpen(svchp, errhp, bfile_loc, (ub1)OCI_FILE_READONLY)); /* ... Do some processing. */ checkerr(errhp, OCILobClose(svchp, errhp, bfile_loc)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); }
IDENTIFICATION DIVISION. PROGRAM-ID. OPEN-BFILE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 01 SRC-BFILE SQL-BFILE. 01 DIR-ALIAS PIC X(30) VARYING. 01 FNAME PIC X(20) VARYING. 01 ORASLNRD PIC 9(4). EXEC SQL INCLUDE SQLCA END-EXEC. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL INCLUDE ORACA END-EXEC. PROCEDURE DIVISION. OPEN-BFILE. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the BFILE locator: EXEC SQL ALLOCATE :SRC-BFILE END-EXEC. * Set up the directory and file information: MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "washington_audio" TO FNAME-ARR. MOVE 16 TO FNAME-LEN. * Assign directory alias and file name to BFILE: EXEC SQL LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS, FILENAME = :FNAME END-EXEC. * Open the BFILE read only: EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC. * Close the LOB: EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC. * And free the LOB locator: EXEC SQL FREE :SRC-BFILE END-EXEC. EXEC SQL COMMIT 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.
/* In Pro*C/C++ there is only one form of OPEN that is used for OPENing BFILEs. There is no FILE OPEN, only a simple OPEN statement: */ #include <oci.h> #include <stdio.h> #include <sqlca.h> void Sample_Error() { EXEC SQL WHENEVER SQLERROR CONTINUE; printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); } void openBFILE_proc() { OCIBFileLocator *Lob_loc; char *Dir = "PHOTO_DIR", *Name = "Lincoln_photo"; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Initialize the Locator: */ EXEC SQL ALLOCATE :Lob_loc; EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name; /* Open the BFILE: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; /* ... Do some processing: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; openBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
Dim OraDyn as OraDynaset, OraPhoto as OraBFile, OraMusic as OraBFile Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab",ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("Music").Value Set OraPhoto = OraDyn.Fields("Photo").Value 'Go to the last rowand open Bfile for reading: OraDyn.MoveLast OraPhoto.Open 'Open Bfile for reading 'Do some processing: OraPhoto.Close
// Java 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_41 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("oracle.jdbc.driver.OracleDriver"); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BFILE src_lob = null; ResultSet rset = null; rset = stmt.executeQuery ( "SELECT BFILENAME('PHOTO_DIR', 'Lincoln_photo') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); // openFile() delegates to oracle.jdbc.dbaccess.DBAccess.fileOpen(): 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(); } } }
As you can see by comparing the code, these alternative methods are very similar. However, while you can continue to use the older FILEISOPEN form, we strongly recommend that you switch to using ISOPEN, because this facilitates future extensibility.
A limited number of BFILEs can be open simultaneously per session. The maximum number is specified by using the SESSION_MAX_OPEN_FILES initialization parameter.
SESSION_MAX_OPEN_FILES defines an upper limit on the number of simultaneously open files in a session. The default value for this parameter is 10. That is, a maximum of 10 files can be opened simultaneously per session if the default value is utilized. The database administrator can change the value of this parameter in the init.ora file. For example:
SESSION_MAX_OPEN_FILES=20
If the number of unclosed files exceeds the SESSION_MAX_OPEN_FILES value then you will not be able to open any more files in the session. To close all open files, use the FILECLOSEALL call.
While you can continue to use the older FILEISOPEN form, we strongly recommend that you switch to using ISOPEN, because this facilitates future extensibility. his example queries whether the a BFILE associated with Music is open that is.
/* Note that the example procedure seeIfOpenBFILE_procOne is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE seeIfOpenBFILE_procOne IS Lob_loc BFILE; RetVal INTEGER; BEGIN /* Select the LOB, initializing the BFILE locator: */ SELECT Music INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3; RetVal := DBMS_LOB.FILEISOPEN(Lob_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;
/* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } boolean BfileIsOpen(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been allocated and initialized. */ OCILobLocator *bfile_loc; boolean flag; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Select the bfile */ selectLob(svchp, stmthp, errhp, dfnhp, bfile_loc, "SELECT Music FROM Multimedia_tab WHERE Clip_ID=3"); boolean flag; checkerr(errhp, OCILobFileIsOpen(svchp, errhp, bfile_loc, &flag)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); return(flag); }
|
Note: At the present time, OO4O only offers ISOPEN to test whether or not a BFILE is open (see "Example: See If the BFILE is Open with FILEISOPEN Using Visual Basic (OO4O)"). |
// 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_45 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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('PHOTO_DIR', 'Lincoln_photo') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } result = new Boolean(src_lob.plsql_fileIsOpen()); System.out.println( "result of fileIsOpen() before opening file : " + result.toString()); src_lob.plsql_fileOpen(); result = new Boolean(src_lob.plsql_fileIsOpen()); System.out.println( "result of fileIsOpen() after opening file : " + result.toString()); // Close the BFILE, statement and connection: src_lob.plsql_fileClose(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This example queries whether the a BFILE is open that is associated with Music.
/* Note that the example procedure seeIfOpenBFILE_procTwo is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE seeIfOpenBFILE_procTwo IS Lob_loc BFILE; RetVal INTEGER; BEGIN /* Select the LOB, initializing the BFILE locator: */ SELECT Music INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3; RetVal := DBMS_LOB.ISOPEN(Lob_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;
/* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } boolean BfileIsOpen(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been allocated and initialized. */ OCILobLocator *bfile_loc; boolean flag; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Select the bfile */ selectLob(svchp, stmthp, errhp, dfnhp, bfile_loc, "SELECT Music FROM Multimedia_tab WHERE Clip_ID=3"); boolean flag; checkerr(errhp, OCILobFileIsOpen(svchp, errhp, bfile_loc, &flag)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); return(flag); }
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-IS-OPEN. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 01 BFILE1 SQL-BFILE. 01 DIR-ALIAS PIC X(30) VARYING. 01 FNAME PIC X(20) VARYING. 01 IS-OPEN 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-IS-OPEN. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the BFILE locator: EXEC SQL ALLOCATE :BFILE1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC SQL SELECT PHOTO INTO :BFILE1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 END-EXEC. * Use the LOB DESCRIBE to see if lob is open: EXEC SQL LOB DESCRIBE :BFILE1 GET ISOPEN INTO :IS-OPEN END-EXEC. IF IS-OPEN = 1 * Logic for an open BFILE goes here DISPLAY "BFILE is open." ELSE * Logic for a closed BFILE goes here DISPLAY "BFILE is closed." END-IF. * And free the LOB locator: END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 END-EXEC. EXEC SQL COMMIT 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.
/* In Pro*C/C++, there is only one form of ISOPEN used to determine whether or not a BFILE is OPEN. There is no FILE IS OPEN, 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 Music INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3; /* 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; }
'Note that this code fragment assumes a ORABFILE object as the result of a 'dynaset operation. This object could have been an OUT parameter of a PL/SQL 'procedure. For more information please refer to chapter 1: Dim OraDyn as OraDynaset, OraMusic as OraBFile, amount_read%, chunksize%, chunk chunksize = 32768 Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("Music") If OraMusic.IsOpen then 'Processing given that the file is already open: Else 'Processing given that the file is not open, or return an error: End If
// 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_48 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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('PHOTO_DIR', 'Lincoln_photo') 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(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This example opens and displays a BFILE is open that is associated with Music.
/* Note that the example procedure displayBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE displayBFILE_proc IS Lob_loc BFILE; Buffer RAW(1024); Amount BINARY_INTEGER := 1024; Position INTEGER := 1; BEGIN /* Select the LOB: */ SELECT Music INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* Opening the BFILE: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); LOOP DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer); /* Display the buffer contents: */ DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer)); Position := Position + Amount; END LOOP; /* Closing the BFILE: */ DBMS_LOB.CLOSE (Lob_loc); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('End of data'); END;
/* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } #define MAXBUFLEN 32767 void BfileDisplay(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been allocated and initialized. */ OCILobLocator *bfile_loc; ub1 bufp[MAXBUFLEN]; ub4 buflen, amt, offset; boolean done; ub4 retval; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Select the bfile */ selectLob(svchp, stmthp, errhp, dfnhp, bfile_loc, "SELECT Music FROM Multimedia_tab WHERE Clip_ID=3"); ub1 bufp[MAXBUFLEN]; ub4 buflen, amt, offset; boolean done; ub4 retval; 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(bufp, '\0', MAXBUFLEN); done = FALSE; while (!done) { retval = OCILobRead(svchp, errhp, bfile_loc, &amt, offset, (dvoid *) bufp, buflen, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); switch (retval) { case 0: /* Only one piece or last piece*/ /* process the data in bufp. amt will give the amount of data just read in bufp. This is in bytes for BLOBs and in characters for fixed width CLOBS and in bytes for variable width CLOBs*/ done = TRUE; break; case -1: /* 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. This is in bytes for BFILEs and i characters for fixed width CLOBS and in bytes for variable width CLOBs */ 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)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); }
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 PHOTO INTO :SRC-BFILE FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 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 example will READ the entire contents of a BFILE piecewise into a buffer using a streaming mechanism via 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 Music INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3; /* 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; }
'Note that this code fragment assumes a ORABFILE object as the result of a 'dynaset operation. This object could have been an OUT parameter of a PL/SQL 'procedure. For more information please refer to chapter 1: Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraMusic As OraBfile, amount_read%, chunksize%, chunk As Variant Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&) chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("Music").Value OraMusic.offset = 1 OraMusic.PollingAmount = OraMusic.Size 'Read entire BFILE contents 'Open the Bfile for reading: OraMusic.Open amount_read = OraMusic.Read(chunk, chunksize) While OraMusic.Status = ORALOB_NEED_DATA amount_read = OraMusic.Read(chunk, chunksize) Wend OraMusic.Close
// 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_53 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 music FROM multimedia_tab WHERE clip_id = 2"); 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(); } } }
When reading the LOB value, it is not an error to try to read beyond the end of the LOB. This means that you can always specify an input amount of 4 gigabytes regardless of the starting offset and the amount of data in the LOB. You do not need to incur a round-trip to the server to call OCILobGetLength() to find out the length of the LOB value in order to determine the amount to read.
For example, assume that the length of a LOB is 5,000 bytes and you want to read the entire LOB value starting at offset 1,000. Also assume that you do not know the current length of the LOB value. Here is the OCI read call, excluding the initialization of all parameters:
#define MAX_LOB_SIZE 4294967295 ub4 amount = MAX_LOB_SIZE; ub4 offset = 1000; OCILobRead(svchp, errhp, locp, &amount, offset, bufp, bufl, 0, 0, 0, 0)
The following example considers reading a photograph into PHOTO from a BFILE 'PHOTO_DIR'.
/* Note that the example procedure readBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE readBFILE_proc IS Lob_loc BFILE := BFILENAME('PHOTO_DIR', 'Jefferson_photo'); Amount INTEGER := 32767; Position INTEGER := 1; Buffer RAW(32767); BEGIN /* Select the LOB: */ SELECT Photo INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3; /* Open the BFILE: */ DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY); /* Read data: */ DBMS_LOB.READ(Lob_loc, Amount, Position, Buffer); /* Close the BFILE: */ DBMS_LOB.CLOSE(Lob_loc); END;
/* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } #define MAXBUFLEN 32767 void BfileRead(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been allocated and initialized. */ OCILobLocator *bfile_loc; ub1 bufp[MAXBUFLEN]; ub4 buflen, amt, offset; boolean done; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Select the bfile */ selectLob(svchp, stmthp, errhp, dfnhp, bfile_loc, "SELECT Photo FROM Multimedia_tab WHERE Clip_ID=3"); ub1 bufp[MAXBUFLEN]; ub4 buflen, amt, offset; boolean done; checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc, OCI_FILE_READONLY)); amt = MAXBUFLEN; buflen = sizeof(bufp); /* Process the data in pieces */ offset = 1; memset(bufp, '\0', MAXBUFLEN); done = FALSE; checkerr(errhp, OCILobRead(svchp, errhp, bfile_loc, &amt, offset, (dvoid *) bufp, buflen, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT)); /* Closing the BFILE is mandatory if you have opened it */ checkerr (errhp, OCILobFileClose(svchp, errhp, bfile_loc)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); }
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 MUSIC INTO :BFILE1 FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 3 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.
#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 Photo INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3; /* 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; }
'Example: Read the Data from a BFILE Using Visual Basic (OO4O) 'Note that this code fragment assumes a ORABFILE object as the result of a 'dynaset operation. This object could have been an OUT parameter of a PL/SQL 'procedure. For more information please refer to chapter 1: Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraMusic As OraBfile, amount_read%, chunksize%, chunk As Variant Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&) chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("Music").Value OraMusic.offset = 1 OraMusic.PollingAmount = OraMusic.Size 'Read entire BFILE contents 'Open the Bfile for reading: OraMusic.Open amount_read = OraMusic.Read(chunk, chunksize) While OraMusic.Status = ORALOB_NEED_DATA amount_read = OraMusic.Read(chunk, chunksize) Wend OraMusic.Close
// 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_53 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 music FROM multimedia_tab WHERE clip_id = 2"); 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(); } } }
The following example considers reading an audio recording into RECORDING from a BFILE 'AUDIO_DIR'.
/* Note that the example procedure substringBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE substringBFILE_proc IS Lob_loc BFILE; Position INTEGER := 1; Buffer RAW(32767); BEGIN /* Select the LOB: */ SELECT Mtab.Voiced_ref.Recording INTO Lob_loc FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3; /* Open the BFILE: */ DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY); Buffer := DBMS_LOB.SUBSTR(Lob_loc, 255, Position); /* Close the BFILE: */ DBMS_LOB.CLOSE(Lob_loc); END;
IDENTIFICATION DIVISION. PROGRAM-ID. 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 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 MTAB.VOICED_REF.RECORDING INTO :BFILE1 FROM MULTIMEDIA_TAB MTAB WHERE MTAB.CLIP_ID = 3 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. EXC SQL FREE :BFILE1 END-EXEC.
/* 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 Mtab.Voiced_ref.Recording INTO :Lob_loc FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3; /* 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; }
'Note that this code fragment assumes a ORABFILE object as the result of a 'dynaset operation. This object could have been an OUT parameter of a PL/SQL 'procedure. For more information please refer to chapter 1: Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraMusic As OraBfile, amount_read%, chunksize%, chunk Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&) chunk_size = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("Music").Value OraMusic.PollingAmount = OraMusic.Size 'Read entire BFILE contents OraMusic.offset = 255 'Read from the 255th position 'Open the Bfile for reading: OraMusic.Open amount_read = OraMusic.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
// 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_62 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 music FROM multimedia_tab WHERE clip_id = 2"); 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(); } } }
The following example deals with the problem of determining whether a photograph in the file 'PHOTO_DIR' has already been used as a specific PHOTO by comparing each data entity bit by bit. Note that LOBMAXSIZE is set at 4 gigabytes so that you do not have to find out the length of each BFILE before beginning the comparison.
/* Note that the example procedure compareBFILEs_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE compareBFILEs_proc IS /* Initialize the BFILE locator: */ Lob_loc1 BFILE := BFILENAME('PHOTO_DIR', 'RooseveltFDR_photo'); Lob_loc2 BFILE; Retval INTEGER; BEGIN /* Select the LOB: */ SELECT Photo INTO Lob_loc2 FROM Multimedia_tab WHERE Clip_ID = 3; /* Open the BFILEs: */ DBMS_LOB.OPEN(Lob_loc1, DBMS_LOB.LOB_READONLY); DBMS_LOB.OPEN(Lob_loc2, DBMS_LOB.LOB_READONLY); Retval := DBMS_LOB.COMPARE(Lob_loc2, Lob_loc1, DBMS_LOB.LOBMAXSIZE, 1, 1); /* Close the BFILEs: */ DBMS_LOB.CLOSE(Lob_loc1); DBMS_LOB.CLOSE(Lob_loc2); END;
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-COMPARE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 "PHOTO_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "fdroosevelt_photo" 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 PHOTO INTO :BFILE2 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 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.
/* 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 is 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 = "PHOTO_DIR", *Name1 = "RooseveltFDR_photo"; 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 Multimedia_tab WHERE Clip_ID = 3; /* 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; }
'Note that 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, OraMusic As OraBfile, OraMyMusic As OraBfile, OraSql As OraSqlStmt Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&) OraDb.Connection.BeginTrans Set OraParameters = OraDb.Parameters OraParameters.Add "id", 1001, ORAPARM_INPUT 'Define out parameter of BFILE type: OraParameters.Add "MyMusic", Empty, ORAPARM_OUTPUT OraParameters("MyMusic").ServerType = ORATYPE_BFILE Set OraSql = OraDb.CreateSql( "BEGIN SELECT music INTO :MyMusic FROM multimedia_tab WHERE clip_id = :id; END;", ORASQL_FAILEXEC) Set OraMyMusic = OraParameters("MyMusic").Value 'Create dynaset: Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab WHERE Clip_Id = 1001", ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("Music").Value 'Open the Bfile for reading: OraMusic.Open OraMyMusic.Open If OraMusic.Compare(OraMyMusic) Then 'Process the data Else 'Do error processing End If OraDb.Connection.CommitTrans
// 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.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: Class.forName ("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 photo FROM multimedia_tab WHERE clip_id = 2"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBFILE (1); } rset = stmt.executeQuery ( "SELECT BFILENAME('PHOTO_DIR', 'music') FROM DUAL"); if (rset.next()) { lob_loc2 = ((OracleResultSet)rset).getBFILE (1); } if (lob_loc1.length() > lob_loc2.length()) System.out.println("Looking for LOB2 inside LOB1. result = " + Long.toString(lob_loc1.position(lob_loc2, 0))); else System.out.println("Looking for LOB1 inside LOB2. result = " + Long.toString(lob_loc2.position(lob_loc1, 0))); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
The following example searches for the occurrence of a pattern of audio data within an interview Recording. This assumes that an audio signature is represented by an identifiable bit pattern.
/* Note that the example procedure instringBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE instringBFILE_proc IS Lob_loc BFILE; Pattern RAW(32767); Position INTEGER; BEGIN /* Select the LOB: */ SELECT Intab.Recording INTO Lob_loc FROM THE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab WHERE Clip_ID = 3) Intab WHERE Segment = 1; /* Open the BFILE: */ DBMS_LOB.OPEN(Lob_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(Lob_loc, Pattern, 1, 2); /* Close the BFILE: */ DBMS_LOB.CLOSE(Lob_loc); END;
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-INSTR. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 01 BFILE1 SQL-BFILE. * The length of pattern was chosen arbitrarily: 01 PATTERN PIC X(4) VALUE "2424". EXEC SQL VAR PATTERN IS RAW(4) END-EXEC. 01 POS PIC S9(9) COMP. 01 ORASLNRD PIC 9(4). EXEC SQL INCLUDE SQLCA END-EXEC. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL INCLUDE ORACA END-EXEC. PROCEDURE DIVISION. BFILE-INSTR. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the BFILE locator: EXEC SQL ALLOCATE :BFILE1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC SQL SELECT PHOTO INTO :BFILE1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 END-EXEC. * Open the CLOB for READ ONLY: EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC. * Execute PL/SQL to get INSTR functionality: EXEC SQL EXECUTE BEGIN :POS := DBMS_LOB.INSTR(:BFILE1,:PATTERN, 1, 2); END; END-EXEC. IF POS = 0 * Logic for pattern not found here DISPLAY "Pattern is not found." ELSE * Pos contains position where pattern is found DISPLAY "Pattern is found." END-IF. * Close and free the LOB: EXEC SQL LOB CLOSE :BFILE1 END-EXEC. END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 END-EXEC. EXEC SQL COMMIT 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.
/* Pro*C lacks an equivalent embedded SQL form of the DBMS_LOB.INSTR() function. However, like SUBSTR() and COMPARE(), Pro*C/C++ can call DBMS_LOB.INSTR() from within an anonymous PL/SQL block as shown here: */ #include <sql2oci.h> #include <stdio.h> #include <string.h> #include <sqlca.h> void Sample_Error() { EXEC SQL WHENEVER SQLERROR CONTINUE; printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); } #define PatternSize 5 void instringBFILE_proc() { OCIBFileLocator *Lob_loc; unsigned int Position = 0; int Clip_ID = 3, Segment = 1; char Pattern[PatternSize]; /* Datatype Equivalencing is Mandatory for this Datatype: */ EXEC SQL VAR Pattern IS RAW(PatternSize); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; /* Use Dynamic SQL to retrieve the BFILE Locator: */ EXEC SQL PREPARE S FROM 'SELECT Intab.Recording \ FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab \ WHERE Clip_ID = :cid) Intab \ WHERE Intab.Segment = :seg'; EXEC SQL DECLARE C CURSOR FOR S; EXEC SQL OPEN C USING :Clip_ID, :Segment; EXEC SQL FETCH C INTO :Lob_loc; EXEC SQL CLOSE C; /* Open the BFILE: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; memset((void *)Pattern, 0, PatternSize); /* Find the first occurrance of the pattern starting from the beginning of the BFILE using PL/SQL: */ EXEC SQL EXECUTE BEGIN :Position := DBMS_LOB.INSTR(:Lob_loc, :Pattern, 1, 1); END; END-EXEC; /* Close the BFILE: */ EXEC SQL LOB CLOSE :Lob_loc; if (0 == Position) printf("Pattern not found\n"); else printf("The pattern occurs at %d\n", Position); EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; instringBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
// Java IO classes: 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_70 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("oracle.jdbc.driver.OracleDriver"); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BFILE lob_loc = null; // Pattern to look for within the BFILE: String pattern = new String("children"); ResultSet rset = stmt.executeQuery ( "SELECT photo FROM multimedia_tab WHERE clip_id = 3"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBFILE (1); } // Open the LOB: lob_loc.openFile(); // Search for the location of pattern string in the BFILE, // starting at offset 1: long result = lob_loc.position(pattern.getBytes(), 1); System.out.println( "Results of Pattern Comparison : " + Long.toString(result)); // Close the LOB: lob_loc.closeFile(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This example queries whether a BFILE that is associated with Recording.
/* Note that the example procedure seeIfExistsBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE seeIfExistsBFILE_proc IS Lob_loc BFILE; BEGIN /* Select the LOB: */ SELECT Intab.Recording INTO Lob_loc FROM THE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3) Intab WHERE Intab.Segment = 1; /* See If the BFILE exists: */ IF (DBMS_LOB.FILEEXISTS(Lob_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;
/* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } boolean BfileExists(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been allocated and initialized. */ OCILobLocator *bfile_loc; boolean is_exists; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Select the bfile */ selectLob(svchp, stmthp, errhp, dfnhp, bfile_loc, "SELECT Intab.Recording FROM THE( SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID=3) Intab WHERE Intab.Segment = 1"); boolean is_exists; checkerr(errhp, OCILobFileExists(svchp, errhp, bfile_loc, &is_exists)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); return(is_exists); }
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-EXISTS. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 PHOTO INTO :BFILE1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 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 COMMIT 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.
#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 Mtab.Voiced_ref.Recording INTO :Lob_loc FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3; /* 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; }
'Note that 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 OraMusic As OraBfile, OraSql As OraSqlStmt Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&) OraDb.Connection.BeginTrans Set OraParameters = OraDb.Parameters OraParameters.Add "id", 1001, ORAPARM_INPUT 'Define out parameter of BFILE type: OraParameters.Add "MyMusic", Empty, ORAPARM_OUTPUT OraParameters("MyMusic").ServerType = ORATYPE_BFILE Set OraSql = OraDb.CreateSql( "BEGIN SELECT music INTO :MyMusic FROM multimedia_tab WHERE clip_id = :id; END;", ORASQL_FAILEXEC) Set OraMusic = OraParameters("MyMusic").Value If OraMusic.Exists Then 'Process the data Else 'Do error processing End If OraDb.Connection.CommitTrans
// 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.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: Class.forName ("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 photo FROM multimedia_tab WHERE clip_id = 3"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBFILE (1); } // See if the BFILE exists: Boolean exists = new Boolean(lob_loc.fileExists()); System.out.println("Result from fileExists(): " + exists.toString()); // Return the length of the BFILE: long length = lob_loc.length(); System.out.println("Length of BFILE: " + Long.toString(length)); // Get the directory alias for this BFILE: System.out.println("Directory alias: " + 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 example gets the length of a BFILE that is associated with Recording.
/* Note that the example procedure getLengthBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE getLengthBFILE_proc IS Lob_loc BFILE; Length INTEGER; BEGIN /* Initialize the BFILE locator by selecting the LOB: */ SELECT Mtab.Voiced_ref.Recording INTO Lob_loc FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3; /* Open the BFILE: */ DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY); /* Get the length of the LOB: */ Length := DBMS_LOB.GETLENGTH(Lob_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(Lob_loc); END;
/* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } ub4 BfileLength(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been * allocated and initialized. */ OCILobLocator *bfile_loc; ub4 len; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Select the bfile */ selectLob(svchp, stmthp, errhp, dfnhp, bfile_loc, "SELECT Mtab.Voiced_ref.Recording FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3"); ub4 len; checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc, (ub1)OCI_FILE_READONLY)); checkerr(errhp, OCILobGetLength(svchp, errhp, bfile_loc, &len)); /* ... Do some processing. */ checkerr(errhp, OCILobFileClose(svchp, errhp, bfile_loc)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); return(len); }
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-LENGTH. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 PHOTO INTO :BFILE1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 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.
#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 Mtab.Voiced_ref.Recording INTO :Lob_loc FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3; /* 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; }
'Note that 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("exampledb", "scott/tiger", 0&) OraDb.Connection.BeginTrans Set OraParameters = OraDb.Parameters OraParameters.Add "id", 1001, ORAPARM_INPUT 'Define out parameter of BFILE type: OraParameters.Add "MyMusic", Empty, ORAPARM_OUTPUT OraParameters("MyMusic").ServerType = ORATYPE_BFILE Set OraSql = OraDb.CreateSql( "BEGIN SELECT music INTO :MyMusic FROM multimedia_tab WHERE clip_id = :id; END;", ORASQL_FAILEXEC) Set OraMusic = OraParameters("MyMusic").Value If OraMusic.Size = 0 Then MsgBox "BFile size is 0" Else MsgBox "BFile size is " & OraMusic.Size End If OraDb.Connection.CommitTrans
// 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.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: Class.forName ("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 photo FROM multimedia_tab WHERE clip_id = 3"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBFILE (1); } // See if the BFILE exists: Boolean exists = new Boolean(lob_loc.fileExists()); System.out.println("Result from fileExists(): " + exists.toString()); // Return the length of the BFILE: long length = lob_loc.length(); System.out.println("Length of BFILE: " + Long.toString(length)); // Get the directory alias for this BFILE: System.out.println("Directory alias: " + 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 example assigns one BFILE locator to another related to Photo.
|
Note: Assigning one BFILE to another using PL/SQL entails using the "=" sign. This is an advanced topic that is discussed in more detail above with regard to "Read-Consistent Locators". |
/* Note that the example procedure BFILEAssign_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE BFILEAssign_proc IS Lob_loc1 BFILE; Lob_loc2 BFILE; BEGIN SELECT Photo INTO Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 3 FOR UPDATE; /* Assign Lob_loc1 to Lob_loc2 so that they both refer to the same operating system file: */ Lob_loc2 := Lob_loc1; /* Now you can read the bfile from either Lob_loc1 or Lob_loc2. */ END;
/* Select the lob/bfile from the Multimedia table: */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement: */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column: */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement: */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } sword BfileAssign(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been * allocated and initialized: */ OCILobLocator *src_loc; OCILobLocator *dest_loc; /* Allocate the locator descriptors: */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &src_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &dest_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Select the bfile: */ selectLob(svchp, stmthp, errhp, dfnhp, src_loc, "SELECT Photo FROM Multimedia_tab WHERE Clip_ID=3"); /* Free the locator descriptors: */ OCIDescriptorFree((dvoid *)src_loc, (ub4)OCI_DTYPE_FILE); OCIDescriptorFree((dvoid *)dest_loc, (ub4)OCI_DTYPE_FILE); return (OCILobLocatorAssign(svchp, errhp, src_loc, &dst_loc)); /* Note: it is the caller's responsibilit to free the source and destination locator descriptors once the caller is done using them. */ }
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-COPY-LOCATOR. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 PHOTO INTO :BFILE1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 END-EXEC. EXEC SQL LOB 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.
#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 Photo INTO :Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 3; /* 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; }
// 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_81 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 photo FROM multimedia_tab WHERE clip_id = 3"); 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) { e.printStackTrace(); } } }
Before you call any of the OCILob* interfaces (such as OCILobWrite), or any of the programmatic environments that make use of the OCILob* interfaces, you must first initialize the LOB locator, via a SELECT, for example. So, if your application requires for a locator to be passed from one function to another, you may want to verify that the locator has already been initialized. If it turns out the locator is not initialized, you could design your application either to return an error or to perform the SELECT before calling the OCILob* interface.
boolean BfileIsInit(envhp, svchp, errhp, bfile_loc) OCIEnv *envhp; OCISvcCtx *svchp; OCIError *errhp; OCILobLocator *bfile_loc; /* This is the BFILE locator that is already allocated and initialized. */ { boolean is_init; checkerr(errhp, OCILobLocatorIsInit(envhp, errhp, bfile_loc, &is_init)); return(is_init); }
/* Pro*C/C++ has no form of embedded SQL statement to determine if a BFILE locator is initialized. Locators in Pro*C/C++ are initialized when they are allocated via the EXEC SQL ALLOCATE statement. However, an example can be written that uses embedded SQL and the OCI as is shown here: */ #include <sql2oci.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 BFILELocatorIsInit_proc() { OCIBFileLocator *Lob_loc; OCIEnv *oeh; OCIError *err; boolean isInitialized = 0; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT Mtab.Voiced_ref.Recording INTO :Lob_loc FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3; /* Get the OCI Environment Handle using a SQLLIB Routine: */ (void) SQLEnvGet(SQL_SINGLE_RCTX, &oeh); /* Allocate the OCI Error Handle: */ (void) OCIHandleAlloc((dvoid *)oeh, (dvoid **)&err, (ub4)OCI_HTYPE_ERROR, (ub4)0, (dvoid **)0); /* Use the OCI to determine if the locator is Initialized: */ (void) OCILobLocatorIsInit(oeh, err, Lob_loc, &isInitialized); if (isInitialized) printf("Locator is initialized\n"); else printf("Locator is not initialized\n"); /* Note that in this example, the locator is initialized: */ /* Deallocate the OCI Error Handle: */ (void) OCIHandleFree(err, OCI_HTYPE_ERROR); /* Release resources held by the locator: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; BFILELocatorIsInit_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
If two locators are equal, this means that they refer to the same version of the LOB data (see "Read-Consistent Locators").
boolean BfileIsEqual(envhp, errhp, bfile_loc1, bfile_loc2) OCIEnv *envhp; OCIError *errhp; OCILobLocator *bfile_loc1; /* BFILE Locator 1 that is already allocated */ OCILobLocator *bfile_loc2; /* BFILE Locator 2 that is already allocated */ { boolean is_equal; OCILobIsEqual(envhp, bfile_loc1, bfile_loc2, &is_equal); return(is_equal); }
/* Pro*C/C++ does not provide a mechanism to test the equality of two locators However, by using the OCI directly, two locators can be compared to determine whether or not they are equal as this example demonstrates: */ #include <sql2oci.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 BFILELocatorIsEqual_proc() { OCIBFileLocator *Lob_loc1, *Lob_loc2; OCIEnv *oeh; boolean isEqual = 0; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc1; EXEC SQL ALLOCATE :Lob_loc2; EXEC SQL SELECT Photo INTO :Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 3; EXEC SQL LOB ASSIGN :Lob_loc1 TO :Lob_loc2; /* Now you can read the BFILE from either Lob_loc1 or Lob_loc2 */ /* Get the OCI Environment Handle using a SQLLIB Routine: */ (void) SQLEnvGet(SQL_SINGLE_RCTX, &oeh); /* Call OCI to see if the two locators are Equal: */ (void) OCILobIsEqual(oeh, Lob_loc1, Lob_loc2, &isEqual); if (isEqual) printf("Locators are equal\n"); else printf("Locators are not equal\n"); /* Note that in this example, the LOB locators will be Equal: */ EXEC SQL FREE :Lob_loc1; EXEC SQL FREE :Lob_loc2; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; BFILELocatorIsEqual_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
// 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_89 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 photo FROM multimedia_tab WHERE clip_id = 3"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBFILE (1); } // Set both LOBS to reference the same BFILE: lob_loc2 = lob_loc1; // Note that in this example, the Locators will be equal: if (lob_loc1.equals(lob_loc2)) { // The Locators are equal: System.out.println("The BFILEs are equal"); } else { // The Locators are different: System.out.println("The BFILEs are NOT equal"); } stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This example retrieves the directory alias and filename related to the BFILE, Music.
CREATE OR REPLACE PROCEDURE getNameBFILE_proc IS Lob_loc BFILE; DirAlias_name VARCHAR2(30); File_name VARCHAR2(40); BEGIN SELECT Music INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3; DBMS_LOB.FILEGETNAME(Lob_loc, DirAlias_name, File_name); /* do some processing based on the directory alias and file names */ END;
/* Select the lob/bfile from the Multimedia table: */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement: */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column: */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement: */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } void BfileGetDirFile(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been allocated and initialized. */ OCILobLocator *bfile_loc; OraText dir_alias[32] = NULL; ub2 d_length = 32; OraText filename[256] = NULL; ub2 f_length = 256; /* Allocate the locator descriptor: */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Select the bfile: */ selectLob(svchp, stmthp, errhp, dfnhp, bfile_loc, "SELECT Music FROM Multimedia_tab WHERE Clip_ID=3"); OCILobFileGetName(envhp, errhp, bfile_loc, dir_alias, &d_length, filename, &f_length); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); }
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-DIR-ALIAS. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 PHOTO INTO :BFILE1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 END-EXEC. * Use the LOB DESCRIBE functionality to get * the directory alias 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.
#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 Photo INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3; /* 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; }
'Note that 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 OraMusic1 As OraBfile, OraSql As OraSqlStmt Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&) OraDb.Connection.BeginTrans Set OraParameters = OraDb.Parameters OraParameters.Add "id", 1001, ORAPARM_INPUT 'Define out parameter of BFILE type: OraParameters.Add "MyMusic", Empty, ORAPARM_OUTPUT OraParameters("MyMusic").ServerType = ORATYPE_BFILE Set OraSql = OraDb.CreateSql( "BEGIN SELECT music INTO :MyMusic FROM multimedia_tab WHERE clip_id = :id; END;", ORASQL_FAILEXEC) Set OraMusic1 = OraParameters("MyMusic").Value 'Get Directory alias and filename: MsgBox " Directory alias is " & OraMusic1.DirectoryName & " Filename is " & OraMusic1.filename OraDb.Connection.CommitTrans
// 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.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: Class.forName ("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 photo FROM multimedia_tab WHERE clip_id = 3"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBFILE (1); } // See if the BFILE exists: Boolean exists = new Boolean(lob_loc.fileExists()); System.out.println("Result from fileExists(): " + exists.toString()); // Return the length of the BFILE: long length = lob_loc.length(); System.out.println("Length of BFILE: " + Long.toString(length)); // Get the directory alias for this BFILE: System.out.println("Directory alias: " + 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(); } } }
Note that you must initialize the BFILE either to NULL or to a directory alias and filename.
The BFILENAME() function can be called as part of SQL INSERT or UPDATE to initialize a BFILE column or attribute for a particular row by associating it with a physical file in the server's filesystem.
The DIRECTORY object represented by the directory_alias parameter to this function need not already be defined using SQL DDL before the BFILENAME() function is called in SQL DML or a PL/SQL program. However, the directory object and operating system file must exist by the time you actually use the BFILE locator (for example, as having been used as a parameter to an operation such as OCILobFileOpen(), DBMS_LOB.FILEOPEN(), OCILobOpen(), or DBMS_LOB.OPEN()).
Note that BFILENAME() does not validate privileges on this DIRECTORY object, or check if the physical directory that the DIRECTORY object represents actually exists. These checks are performed only during file access using the BFILE locator that was initialized by the BFILENAME() function.
You can use BFILENAME() as part of a SQL INSERT and UPDATE statement to initialize a BFILE column. You can also use it to initialize a BFILE locator variable in a PL/SQL program, and use that locator for file operations. However, if the corresponding directory alias and/or filename does not exist, then PL/SQL DBMS_LOB routines that use this variable will generate errors.
The directory_alias parameter in the BFILENAME() function must be specified taking case-sensitivity of the directory name into consideration.
Syntax
FUNCTION BFILENAME(directory_alias IN VARCHAR2, filename IN VARCHAR2) RETURN BFILE;
|
See Also:
"DIRECTORY Name Specification" for information about the use of uppercase letters in the directory name, and |
This example updates Multimedia_tab by means of the BFILENAME function.
UPDATE Multimedia_tab SET Photo = BFILENAME('PHOTO_DIR', 'Nixon_photo') where Clip_ID = 3;
There is no copy function for BFILEs, so you have to use UPDATE as SELECT if you want to copy a BFILE from one location to another. Because BFILEs use reference semantics instead of copy semantics, only the BFILE locator is copied from one row to another row. This means that you cannot make a copy of an external LOB value without issuing an operating system command to copy the operating system file.
This example updates the table, Voiceover_tab by selecting from the archival storage table, VoiceoverLib_tab
UPDATE Voiceover_tab SET (originator,script,actor,take,recording) = (SELECT * FROM VoiceoverLib_tab VLtab WHERE VLtab.Take = 101);
Note that you must initialize the BFILE locator bind variable to a directory alias and filename before issuing the update statement.
/* Note that the example procedure updateUseBindVariable_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE updateUseBindVariable_proc (Lob_loc BFILE) IS BEGIN UPDATE Multimedia_tab SET Photo = Lob_loc WHERE Clip_ID = 3; END; DECLARE Lob_loc BFILE; BEGIN SELECT Photo INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; updateUseBindVariable_proc (Lob_loc); COMMIT; END;
void BfileUpdate(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *Lob_loc; OCIBind *bndhp; text *updstmt = (text *) "UPDATE Multimedia_tab SET Photo = :Lob_loc WHERE Clip_ID = 1"; OraText *Dir = (OraText *)"PHOTO_DIR", *Name = (OraText *)"Washington_photo"; /* Prepare the SQL statement: */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, updstmt, (ub4) strlen((char *) updstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Allocate Locator resources: */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0); checkerr (errhp, OCILobFileSetName(envhp, errhp, &Lob_loc, Dir, (ub2)strlen((char *)Dir), Name,(ub2)strlen((char *)Name))); checkerr (errhp, OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (dvoid *) &Lob_loc, (sb4) 0, SQLT_BFILE, (dvoid *) 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)); /* Free LOB resources: */ OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_FILE); }
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-UPDATE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 PHOTO INTO :BFILE1:BFILE-IND FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 END-EXEC. * Make photo associated with clip_id=3 same as clip_id=1: EXEC SQL UPDATE MULTIMEDIA_TAB SET PHOTO = :BFILE1:BFILE-IND WHERE CLIP_ID = 3 END-EXEC. * Free the BFILE: END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 END-EXEC. EXEC SQL COMMIT 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.
#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 Multimedia_tab SET Photo = :Lob_loc WHERE Clip_ID = 3; } void updateBFILE_proc() { OCIBFileLocator *Lob_loc; EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT Photo INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; 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; }
Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraParameters As OraParameters, OraPhoto As OraBfile Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&) OraDb.Connection.BeginTrans Set OraParameters = OraDb.Parameters 'Define in out parameter of BFILE type: OraParameters.Add "MyPhoto", Empty, ORAPARM_BOTH, ORATYPE_BFILE 'Define out parameter of BFILE type: OraDb.ExecuteSQL ( "BEGIN SELECT Photo INTO :MyPhoto FROM Multimedia_tab WHERE Clip_ID = 1; END;") 'Update the photo BFile for clip_id=1 to clip_id=1001: OraDb.ExecuteSQL ( "UPDATE Multimedia_tab SET Photo = :MyPhoto WHERE Clip_ID = 1001") 'Get Directory alias and filename 'MsgBox " Directory alias is " & OraMusic1.DirectoryName & " Filename is " & OraMusic1.filename OraDb.Connection.CommitTrans
// 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_100 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 photo FROM multimedia_tab WHERE clip_id = 3"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } // Prepare a CallableStatement to OPEN the LOB for READWRITE: cstmt = (OracleCallableStatement) conn.prepareCall ( "UPDATE multimedia_tab SET photo = ? WHERE clip_id = 1"); 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(); } } }
As you can see by comparing the code, these alternative methods are very similar. However, while you can continue to use the older FILECLOSE form, we strongly recommend that you switch to using CLOSE, because this facilitates future extensibility.
While you can continue to use the older FILECLOSE form, we strongly recommend that you switch to using CLOSE, because this facilitate future extensibility. This example can be read in conjunction with the example of opening a BFILE.
/* Note that the example procedure closeBFILE_procOne is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE closeBFILE_procOne IS Lob_loc BFILE := BFILENAME('PHOTO_DIR', 'Lincoln_photo'); BEGIN DBMS_LOB.FILEOPEN(Lob_loc, DBMS_LOB.FILE_READONLY); /* ...Do some processing. */ DBMS_LOB.FILECLOSE(Lob_loc); END;
/* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } void BfileClose(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been * allocated and initialized. */ OCILobLocator *bfile_loc; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Set the bfile locator information */ checkerr(errhp, (OCILobFileSetName(envhp, errhp, &bfile_loc, (OraText *)"PHOTO_DIR", (ub2)strlen("PHOTO_DIR"), (OraText *)"Lincoln_photo", (ub2)strlen("Lincoln_photo")))); checkerr(errhp, OCILobFileClose(svchp, errhp, bfile_loc)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); }
// 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_45 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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('PHOTO_DIR', 'Lincoln_photo') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } result = new Boolean(src_lob.plsql_fileIsOpen()); System.out.println( "result of fileIsOpen() before opening file : " + result.toString()); src_lob.plsql_fileOpen(); result = new Boolean(src_lob.plsql_fileIsOpen()); System.out.println( "result of fileIsOpen() after opening file : " + result.toString()); // Close the BFILE, statement and connection: src_lob.plsql_fileClose(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This example should be read in conjunction with the example of opening a BFILE -- in this case, closing the BFILE associated with Lincoln_photo.
/* Note that the example procedure closeBFILE_procTwo is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE closeBFILE_procTwo IS Lob_loc BFILE := BFILENAME('PHOTO_DIR', 'Lincoln_photo'); BEGIN DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY); /* ...Do some processing. */ DBMS_LOB.CLOSE(Lob_loc); END;
/* Select the lob/bfile from the Multimedia table */ void selectLob(svchp, stmthp, errhp, dfnhp, Lob_loc, selstmt) OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; OCILobLocator *Lob_loc; text *selstmt; { /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column */ checkerr (errhp, OCIDefineByPos(stmhp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } void BfileClose(envhp, svchp, stmthp, errhp, dfnhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStatement *stmthp; OCIError *errhp; OCIDefine *dfnhp; { /* Assume all handles passed as input to this routine have been allocated and initialized. */ OCILobLocator *bfile_loc; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0) /* Set the bfile locator information */ checkerr(errhp, (OCILobFileSetName(envhp, errhp, &bfile_loc, (OraText *)"PHOTO_DIR", (ub2)strlen("PHOTO_DIR"), (OraText *)"Lincoln_photo", (ub2)strlen("Lincoln_photo")))); checkerr(errhp, OCILobClose(svchp, errhp, bfile_loc)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); }
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-CLOSE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 "PHOTO_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "lincoln_photo" 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.
/* Pro*C/C++ has only one form of CLOSE for BFILEs. Pro*C/C++ has no FILE CLOSE 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 = "PHOTO_DIR", *Name = "Lincoln_photo"; 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; }
'Note that this code fragment assumes a ORABFILE object as the result of a 'dynaset operation. This object could have been an OUT parameter of a PL/SQL 'procedure. For more information please refer to chapter 1: Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraMusic As OraBfile, amount_read%, chunksize%, chunk Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&) chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("Music").Value If OraMusic.IsOpen Then 'Processing given that the file is already open OraMusic.Close End If
// 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_48 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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('PHOTO_DIR', 'Lincoln_photo') 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(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
It is the user's responsibility to close any opened file(s) after normal or abnormal termination of a PL/SQL program block or OCI program. So, for instance, for every DBMS_LOB.FILEOPEN() or DBMS_LOB.OPEN() call on a BFILE, there must be a matching DBMS_LOB.FILECLOSE() or DBMS_LOB.CLOSE() call. You should close open files before the termination of a PL/SQL block or OCI program, and also in situations which have raised errors. The exception handler should make provisions to close any files that were opened before the occurrence of the exception or abnormal termination.
If this is not done, Oracle will consider these files unclosed.
/* Note that the example procedure closeAllOpenFilesBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE closeAllOpenFilesBFILE_proc IS BEGIN /* Close all open BFILEs: */ DBMS_LOB.FILECLOSEALL; END;
void BfileCloseAll(svchp, errhp) OCISvcCtx *svchp; OCIError *errhp; { /* Close all open files on the service context */ checkerr(errhp, OCILobFileCloseAll(svchp, errhp)); }
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-CLOSE-ALL. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 01 BFILE1 SQL-BFILE. 01 BFILE2 SQL-BFILE. 01 DIR-ALIAS1 PIC X(30) VARYING. 01 FNAME1 PIC X(20) VARYING. 01 DIR-ALIAS2 PIC X(30) VARYING. 01 FNAME2 PIC X(20) VARYING. 01 ORASLNRD PIC 9(4). EXEC SQL INCLUDE SQLCA END-EXEC. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL INCLUDE ORACA END-EXEC. PROCEDURE DIVISION. BFILE-CLOSE-ALL. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate the BFILEs: EXEC SQL ALLOCATE :BFILE1 END-EXEC. EXEC SQL ALLOCATE :BFILE2 END-EXEC. * Set up the directory and file information: MOVE "AUDIO_DIR" TO DIR-ALIAS1-ARR. MOVE 9 TO DIR-ALIAS1-LEN. MOVE "washington_audio" TO FNAME1-ARR. MOVE 16 TO FNAME1-LEN. EXEC SQL LOB FILE SET :BFILE1 DIRECTORY = :DIR-ALIAS1, FILENAME = :FNAME1 END-EXEC. EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC. * Set up the directory and file information: MOVE "PHOTO_DIR" TO DIR-ALIAS2-ARR. MOVE 9 TO DIR-ALIAS2-LEN. MOVE "lincoln_photo" TO FNAME2-ARR. MOVE 13 TO FNAME2-LEN. EXEC SQL LOB FILE SET :BFILE2 DIRECTORY = :DIR-ALIAS2, FILENAME = :FNAME2 END-EXEC. EXEC SQL LOB OPEN :BFILE2 READ ONLY END-EXEC. * Close both BFILE1 and BFILE2: EXEC SQL LOB FILE CLOSE ALL END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. MOVE ORASLNR TO ORASLNRD. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
#include <oci.h> #include <stdio.h> #include <sqlca.h> void Sample_Error() { EXEC SQL WHENEVER SQLERROR CONTINUE; printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); } void closeAllOpenBFILEs_proc() { OCIBFileLocator *Lob_loc1, *Lob_loc2; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc1; EXEC SQL ALLOCATE :Lob_loc2; /* Populate the Locators: */ EXEC SQL SELECT Music INTO :Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 3; EXEC SQL SELECT Mtab.Voiced_ref.Recording INTO Lob_loc2 FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3; /* Open both BFILEs: */ EXEC SQL LOB OPEN :Lob_loc1 READ ONLY; EXEC SQL LOB OPEN :Lob_loc2 READ ONLY; /* Close all open BFILEs: */ EXEC SQL LOB FILE CLOSE ALL; /* Free resources held by the Locators: */ EXEC SQL FREE :Lob_loc1; EXEC SQL FREE :Lob_loc2; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; closeAllOpenBFILEs_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
Dim OraParameters as OraParameters, OraPhoto as OraBFile OraConnection.BeginTrans Set OraParameters = OraDatabase.Parameters 'Define in out parameter of BFILE type: OraParameters.Add "MyPhoto", Empty,ORAPARAM_BOTH,ORATYPE_BFILE 'Select the photo BFile for clip_id 1: OraDatabase.ExecuteSQL("Begin SELECT Photo INTO :MyPhoto FROM Multimedia_tab WHERE Clip_ID = 1; END " ) 'Get the BFile photo column: set OraPhoto = OraParameters("MyPhoto").Value 'Open the OraPhoto: OraPhoto.Open 'Do some processing on OraPhoto 'Close all the BFILEs associated with OraPhoto: OraPhoto.CloseAll
// Java IO classes: 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: Class.forName ("oracle.jdbc.driver.OracleDriver"); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BFILE lob_loc1 = null; BFILE lob_loc2 = null; ResultSet rset = null; OracleCallableStatement cstmt = null; rset = stmt.executeQuery ( "SELECT photo FROM multimedia_tab WHERE clip_id = 3"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBFILE (1); } rset = stmt.executeQuery ( "SELECT BFILENAME('PHOTO_DIR', 'RooseveltFDR_photo') FROM DUAL"); if (rset.next()) { lob_loc2 = ((OracleResultSet)rset).getBFILE (1); } cstmt = (OracleCallableStatement) conn.prepareCall ( "BEGIN DBMS_LOB.FILEOPEN(?,DBMS_LOB.LOB_READONLY); END;"); // Open the first LOB: cstmt.setBFILE(1, lob_loc1); cstmt.execute(); cstmt = (OracleCallableStatement) conn.prepareCall ( "BEGIN DBMS_LOB.FILEOPEN(?,DBMS_LOB.LOB_READONLY); END;"); // Use the same CallableStatement to open the second LOB: cstmt.setBFILE(1, lob_loc2); cstmt.execute(); // 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(); } } }
Unlike internal persistent LOBs, the LOB value in a BFILE does not get deleted by using SQL DDL or SQL DML commands -- only the BFILE locator is deleted. Deletion of a record containing a BFILE column amounts to de-linking that record from an existing file, not deleting the physical operating system file itself. An SQL DELETE statement on a particular row deletes the BFILE locator for the particular row, thereby removing the reference to the operating system file.
The following DELETE, DROP TABLE, or TRUNCATE TABLE statements delete the row, and hence the BFILE locator that refers to Image1.gif, but leave the operating system file undeleted in the filesystem.
DELETE FROM Multimedia_tab WHERE Clip_ID = 3; DROP TABLE Multimedia_tab; TRUNCATE TABLE Multimedia_tab;
