This chapter describes APIs for operations that use BFILE
s. APIs covered in this chapter are listed in Table 15-1.
The following information is given for each operation described in this chapter:
Usage Notes provide implementation guidelines such as information specific to a given programmatic environment or datatype.
Syntax refers you to the syntax reference documentation for each supported programmatic environment.
Examples describe any setup tasks necessary to run the examples given. Demonstration files listed are available in subdirectories under $ORACLE_HOME/rdbms/demo/lobs/
named plsql
, oci
, vb
, and java
. The driver program lobdemo.sql
is in /plsql
and the driver program lobdemo.c
is in /oci
.
Note: LOB APIs do not support loading data intoBFILE s. See "Using SQL*Loader to Load LOBs" for details on techniques for loading data into BFILE s. |
This chapter contains these topics:
Table 15-1, "Environments Supported for BFILE APIs" indicates which programmatic environments are supported for the APIs discussed in this chapter. The first column describes the operation that the API performs. The remaining columns indicate with "Yes" or "No" whether the API is supported in PL/SQL, OCI, COBOL, Pro*C/C++, Visual Basic (VB), and JDBC.
Table 15-1 Environments Supported for BFILE APIs
To access BFILE
s use one of the following interfaces:
OCI (Oracle Call Interface)
PL/SQL (DBMS_LOB package)
Precompilers, such as Pro*C/C++ and Pro*COBOL
Oracle Objects for OLE (OO4O)
Java (JDBC)
See Also: Chapter 6, "Overview of Supplied LOB APIs" for information about supported environments for accessingBFILE s. |
The DIRECTORY
object facilitates administering access and usage of BFILE
datatypes. A DIRECTORY
object specifies a logical alias name for a physical directory on the database server file system under which the file to be accessed is located. You can access a file in the server file system only if granted the required access privilege on DIRECTORY
object. You can also use Enterprise Manager Web console to manage DIRECTORY
objects.
See Also:
|
The DIRECTORY
object also provides the flexibility to manage the locations of the files, instead of forcing you to hard-code the absolute path names of physical files in your applications. A directory object name is used in conjunction with the BFILENAME
function, in SQL and PL/SQL, or the OCILobFileSetName()
in OCI, for initializing a BFILE
locator.
Note: The database does not verify that the directory and path name you specify actually exist. You should take care to specify a valid directory in your operating system. If your operating system uses case-sensitive path names, then be sure you specify the directory in the correct format. There is no need to specify a terminating slash (for example, Directory specifications cannot contain ".." anywhere in the path (for example, |
To associate an operating system file to a BFILE
, first create a DIRECTORY
object which is an alias for the full path name to the operating system file.
To associate existing operating system files with relevant database records of a particular table use Oracle SQL DML (Data Manipulation Language). For example:
Use INSERT
to initialize a BFILE
column to point to an existing file in the server file system
Initialize a BFILE
to NULL
and then update it later to refer to an operating system file using 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.
Directory 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.gif
and image2.gif
are stored.
You may need to set up data structures similar to the following for certain examples to work:
CREATE TABLE Lob_table (
Key_value NUMBER NOT NULL,
F_lob BFILE)
INSERT INTO Lob_table VALUES
(21, BFILENAME('IMG', 'Image1.gif'));
INSERT INTO Lob_table VALUES
(22, BFILENAME('IMG', 'image2.gif'));
The following UPDATE
statement changes the target file to image3.gif
for the row with key_value
of 22.
UPDATE Lob_table SET f_lob = BFILENAME('IMG', 'image3.gif')
WHERE Key_value = 22;
Note: The database does not expand environment variables specified in the DIRECTORY object or file name of a BFILENAME('WORK_DIR', '$MY_FILE') where |
BFILENAME
is a built-in function that you use to initialize a BFILE
column to point to an external file.
Once physical files are associated with records using SQL DML, subsequent read operations on the BFILE
can be performed using PL/SQL DBMS_LOB
package and OCI. However, these files are read-only when accessed through BFILE
s, and so they cannot be updated or deleted through BFILE
s.
As a consequence of the reference-based semantics for BFILEs
, it is possible to have multiple BFILE
columns in the same record or different records referring to the same file. For example, the following UPDATE
statements set the BFILE
column of the row with key_value
=1 in lob_table
to point to the same file as the row with key_value
=22.
UPDATE lob_table SET f_lob = (SELECT f_lob FROM lob_table WHERE key_value = 22) WHERE key_value = 21;
Think of BFILENAME
in terms of initialization — it can initialize the value for the following:
BFILE
column
BFILE
(automatic) variable declared inside a PL/SQL module
Using the BFILE
datatype has the following advantages:
If your need for a particular BFILE
is temporary and limited within the module on which you are working, then you can use the BFILE
related APIs on the variable without ever having to associate this with a column in the database.
Because you are not forced to create a BFILE
column in a server side table, initialize this column value, and then retrieve this column value using a SELECT
, you save a round-trip to the server.
For more information, refer to the example given for DBMS_LOB
.LOADFROMFILE
(see "Loading a LOB with BFILE Data").
The OCI counterpart for BFILENAME
is OCILobFileSetName()
, which can be used in a similar fashion.
You must have CREATE ANY DIRECTORY
system privilege to create directories. Path names cannot contain two dots (".."). The naming convention for DIRECTORY
objects is the same as that for tables and indexes. That is, normal identifiers are interpreted in uppercase, but delimited identifiers are interpreted as is. For example, the following statement:
CREATE OR REPLACE DIRECTORY scott_dir AS '/usr/home/scott';
creates or redefines a directory object whose name is 'SCOTT_DIR
' (in uppercase). But if a delimited identifier is used for the DIRECTORY
name, as shown in the following statement
CREATE DIRECTORY "Mary_Dir" AS '/usr/home/mary';
then the directory object name is 'Mary_Dir
'. Use 'SCOTT_DIR
' and 'Mary_Dir
' when calling BFILENAME
. For example:
BFILENAME('SCOTT_DIR', 'afile') BFILENAME('Mary_Dir', 'afile')
This section introduces the BFILE
security model and associated SQL statements. The main SQL statements associated with BFILE
security are:
SQL DDL: CREATE
and REPLACE
or ALTER
a DIRECTORY
object
SQL DML: GRANT
and REVOKE
the READ
system and object privileges on DIRECTORY
objects
The DIRECTORY
object is a system owned object. For more information on system owned objects, see Oracle Database SQL Reference. Oracle Database supports two new system privileges, which are granted only to DBA:
CREATE
ANY
DIRECTORY
— for creating or altering the directory object creation
DROP
ANY
DIRECTORY
— for deleting the directory object
READ
permission on the DIRECTORY
object enables you to read files located under that directory. The creator of the DIRECTORY
object automatically earns the READ
privilege.
If you have been granted the READ
permission with GRANT
option, then you may in turn grant this privilege to other users/roles and add them to your privilege domains.
The physical directory that it represents may or may not have the corresponding operating system privileges (read in this case) for the Oracle Server process.
It is the responsibility of the DBA to ensure the following:
That the physical directory exists
Read permission for the Oracle Server process is enabled on the file, the directory, and the path leading to it
The directory remains available, and 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.
Caution: Because |
Refer to the Oracle Database SQL Reference for information about the following SQL DDL statements that create, replace, and drop directory objects:
CREATE
DIRECTORY
DROP
DIRECTORY
Refer to the Oracle Database SQL Reference for information about the following SQL DML statements that provide security for BFILE
s:
GRANT
(system privilege)
GRANT
(object privilege)
REVOKE
(system privilege)
REVOKE
(object privilege)
AUDIT
(new statements)
AUDIT
(schema objects)
Catalog views are provided for DIRECTORY objects to enable users to view object names and corresponding paths and privileges. Supported views are:
ALL_DIRECTORIES
(OWNER
, DIRECTORY_NAME
, DIRECTORY_PATH
)
This view describes all directories accessible to the user.
DBA_DIRECTORIES
(OWNER
, DIRECTORY_NAME
, DIRECTORY_PATH
)
This view describes all directories specified for the entire database.
The main goal of the DIRECTORY
feature is to enable a simple, flexible, non-intrusive, yet secure mechanism for the DBA to manage access to large files in the server file system. But to realize this goal, it is very important that the DBA follow these guidelines when using DIRECTORY
objects:
Do not map a DIRECTORY
object to a data file directory. A DIRECTORY
object should not be mapped to physical directories that contain Oracle data files, control files, log files, and other system files. Tampering with these files (accidental or otherwise) could corrupt the database or the server operating system.
Only the DBA should have system privileges. The system privileges such as 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.
Use caution when granting the DIRECTORY privilege. Privileges on DIRECTORY
objects should be granted to different users carefully. The same holds for the use of the WITH
GRANT
OPTION
clause when granting privileges to users.
Do not drop or replace DIRECTORY
objects when database is in operation. DIRECTORY
objects should not be arbitrarily dropped or replaced when the database is in operation. If this were to happen, then operations from all sessions on all files associated with this directory object will fail. Further, if a DROP
or REPLACE
command is executed before these files could be successfully closed, then the references to these files will be lost in the programs, and system resources associated with these files will not be released until the session(s) is shut down.
The only recourse left to PL/SQL users, for example, will be to either run a program block that calls DBMS_LOB
.FILECLOSEALL
and restart their file operations, or exit their sessions altogether. Hence, it is imperative that you use these commands with prudence, and preferably during maintenance downtimes.
Use caution when revoking a user's privilege on DIRECTORY
objects. Revoking a user's privilege on a DIRECTORY
object using the REVOKE
statement causes all subsequent operations on dependent files from the user's session to fail. Either you must re-acquire the privileges to close the file, or run a FILECLOSEALL
in the session and restart the file operations.
In general, using DIRECTORY
objects for managing file access is an extension of system administration work at the operating system level. With some planning, files can be logically organized into suitable directories that have READ privileges for the Oracle process.
DIRECTORY
objects can be created with READ
privileges that map to these physical directories, and specific database users granted access to these directories.
The database does not support session migration for BFILE
datatypes in shared server (multithreaded server) mode. This implies that operations on open BFILE
instances can persist beyond the end of a call to a shared server.
In shared server sessions, BFILE
operations will be bound to one shared server, they cannot migrate from one server to another. This restriction will be removed in a forthcoming release.
For BFILE
s, the value is stored in a server-side operating system file; in other words, external to the database. The BFILE
locator that refers to that file is stored in the row.
If a BFILE
locator variable that is used in a DBMS_LOB
.FILEOPEN
(for example L1) is assigned to another locator variable, (for example L2), then both L1 and L2 point to the same file. This means that two rows in a table with a BFILE
column can refer to the same file or to two distinct files — a fact that the canny developer might turn to advantage, but which could well be a pitfall for the unwary.
A BFILE
locator variable operates like any other automatic variable. With respect to file operations, it operates like a file descriptor available as part of the standard input/output library of most conventional programming languages. This implies that once you define and initialize a BFILE
locator, and open the file pointed to by this locator, all subsequent operations until the closure of this file must be done from within the same program block using this locator or local copies of this locator.
Note the following guidelines when working with BFILE
s:
Open and close a file from the same program block at same nesting level. 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.
Set the BFILE
value before flushing the object to the database. If an object contains a BFILE
, then you must set the BFILE
value before flushing the object to the database, thereby inserting a new row. In other words, you must call OCILobFileSetName()
after OCIObjectNew()
and before OCIObjectFlush()
.
Indicate the DIRECTORY
object name and file name before inserting or updating of a BFILE
. It is an error to insert or update a BFILE
without indicating a directory object name and file name.
This rule also applies to users using an OCI bind variable for a BFILE
in an insert or update statement. The OCI bind variable must be initialized with a directory object name and file name before issuing the insert or update statement.
Initialize BFILE
Before insert or update
Before using SQL to insert or update a row with a BFILE
, you must initialize the BFILE
to one of the following:
NULL
(not possible if using an OCI bind variable)
A directory object name and file name
A path name cannot contain two dots ("..") anywhere in its specification. A file name cannot start with two dots.
This section describes how to load a LOB with data from a BFILE
.
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Preconditions
The following preconditions must exist before calling this procedure:
The source BFILE
instance must exist.
The destination LOB instance must exist.
Usage Notes
Note: TheLOADBLOBFROMFILE and LOADCLOBFROMFILE procedures implement the functionality of this procedure and provide improved features for loading binary data and character data. The improved procedures are available in the PL/SQL environment only. When possible, using one of the improved procedures is recommended. See "Loading a BLOB with Data from a BFILE" and "Loading a CLOB or NCLOB with Data from a BFILE" for more information. |
In using OCI, or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another.
BFILE to CLOB or NCLOB: Converting From Binary Data to a Character Set
When you use the DBMS_LOB.LOADFROMFILE
procedure to populate a CLOB
or NCLOB
, you are populating the LOB with binary data from the BFILE
. No implicit translation is performed from binary data to a character set. For this reason, you should use the LOADCLOBFROMFILE
procedure when loading text (see Loading a CLOB or NCLOB with Data from a BFILE).
Note the following with respect to the amount
parameter:
If you want to load the entire BFILE
, then pass the constant DBMS_LOB.LOBMAXSIZE
. If you pass any other value, then it must be less than or equal to the size of the BFILE
.
If you want to load the entire BFILE
, then you can pass the constant UB4MAXVAL
. If you pass any other value, then it must be less than or equal to the size of the BFILE
.
If you want to load the entire BFILE
, then you can pass the constant UB8MAXVAL
. If you pass any other value, then it must be less than or equal to the size of the BFILE
.
See Also: Table 14-2, "Maximum LOB Size for Load from File Operations" for details on the maximum value of the amount parameter. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — LOADFROMFILE
C (OCI): Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations", for usage notes and examples. Chapter 16, "LOB Functions" — OCILobLoadFromFile2()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, embedded SQL, and LOB LOAD precompiler directives.
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements" "Embedded SQL Statements and Directives"— LOB LOAD.
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBLOB.OraCLOB > METHODS > CopyFromBfile
Java (JDBC) Oracle Database JDBC Developer's Guide and Reference): "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB): floaddat.sql
OCI: floaddat.c
Visual Basic (OO4O): floaddat.bas
Java (JDBC): No example.
This section describes how to open a BFILE
using the OPEN function.
Note: You can also open aBFILE using the FILEOPEN function; however, using the OPEN function is recommended for new development. Using the FILEOPEN function is described in Opening a BFILE with FILEOPEN. |
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL(DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — OPEN
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations", for usage notes. Chapter 16, section "LOB Functions" — OCILobOpen()
, OCILobClose()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB OPEN.
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB OPEN.
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > Open, and > OBJECTS > OraDynaset > METHODS > MoveFirst MoveLast MovePrevious MoveNext
Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Scenario
These examples open an image in operating system file ADPHOTO_DIR
.
Examples
Examples are provided in the following six programmatic environments:
PL/SQL(DBMS_LOB): fopen.sql
OCI: fopen.c
Visual Basic (OO4O): fopen.bas
Java (JDBC): fopen.java
This section describes how to open a BFILE
using the FILEOPEN
function.
Note: TheFILEOPEN function is not recommended for new application development. The OPEN function is recommended for new development. See "Opening a BFILE with OPEN" |
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Usage Notes for Opening a BFILE
While you can continue to use the older FILEOPEN
form, Oracle strongly recommends that you switch to using OPEN
, because this facilitates future extensibility.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILEOPEN, FILECLOSE
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations, for usage notes. Chapter 16, section "LOB Functions" — OCILobFileOpen()
, OCILobFileClose()
, OCILobFileSetName()
.
COBOL (Pro*COBOL): A syntax reference is not applicable in this release.
C/C++ (Pro*C/C++): A syntax reference is not applicable in this release.
Visual Basic (OO4O): A syntax reference is not applicable in this release.
Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Scenario for Opening a BFILE
These examples open keyboard_logo.jpg
in directory object MEDIA_DIR
.
Examples
Examples are provided in the following four programmatic environments:
PL/SQL (DBMS_LOB): ffilopen.sql
OCI: ffilopen.c
Java (JDBC): ffilopen.java
This section describes how to determine whether a BFILE
is open using ISOPEN.
Note: This function (ISOPEN ) is recommended for new application development. The older FILEISOPEN function, described in "Determining Whether a BFILE Is Open with FILEISOPEN", is not recommended for new development. |
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — ISOPEN
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — OCILobFileIsOpen()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB DESCRIBE ... ISOPEN.
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB DESCRIBE ... ISOPEN
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > IsOpen and > OBJECTS > OraDynaset
Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples are provided in the following six programmatic environments:
PL/SQL (DBMS_LOB): fisopen.sql
OCI: fisopen.c
Visual Basic (OO4O): fisopen.bas
Java (JDBC): fisopen.java
This section describes how to determine whether a BFILE
is OPEN using the FILEISOPEN function.
Note: TheFILEISOPEN function is not recommended for new application development. The ISOPEN function is recommended for new development. See Determining Whether a BFILE Is Open Using ISOPEN |
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Usage Notes
While you can continue to use the older FILEISOPEN
form, Oracle strongly recommends that you switch to using ISOPEN
, because this facilitates future extensibility.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL(DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILEISOPEN
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — OCILobFileIsOpen()
.
COBOL (Pro*COBOL): A syntax reference is not applicable in this release.
C/C++ (Pro*C/C++): A syntax reference is not applicable in this release.
Visual Basic (OO4O): A syntax reference is not applicable in this release.
Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Scenario
These examples query whether a BFILE
associated with ad_graphic
is open.
Examples
Examples are provided in the following programmatic environments:
PL/SQL(DBMS_LOB): ffisopen.sql
OCI: ffisopen.c
Java (JDBC): ffisopen.java
This section describes how to display BFILE
data.
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — READ. Chapter 29, "DBMS_OUTPUT" - PUT_LINE
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — OCILobFileOpen()
, OCILobRead2()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB READ, DISPLAY.
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements" — READ
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Read, and OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > PollingAmount, Offset, Status. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples
Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
Examples are provided in six programmatic environments:
PL/SQL (DBMS_LOB): fdisplay.sql
OCI: fdisplay.c
Java (JDBC): fdisplay.java
This section describes how to read data from a BFILE
.
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Usage Notes
Note the following when using this operation.
Streaming Read in OCI
The most efficient way to read large amounts of BFILE
data is by OCILobRead2()
with the streaming mechanism enabled, and using polling or callback. To do so, specify the starting point of the read using the offset
parameter as follows:
ub8 char_amt = 0; ub8 byte_amt = 0; ub4 offset = 1000; OCILobRead2(svchp, errhp, locp, &byte_amt, &char_amt, offset, bufp, bufl, OCI_ONE_PIECE, 0, 0, 0, 0);
When using polling mode, be sure to look at the value of the byte_amt
parameter after each OCILobRead2()
call to see how many bytes were read into the buffer, because the buffer may not be entirely full.
When using callbacks, the lenp
parameter, which is input to the callback, will indicate how many bytes are filled in the buffer. Be sure to check the lenp
parameter during your callback processing because the entire buffer may not be filled with data (see the Oracle Call Interface Programmer's Guide.)
When calling DBMS_LOB.READ
, the amount parameter can be larger than the size of the data; however, the amount parameter should be less than or equal to the size of the buffer. In PL/SQL, the buffer size is limited to 32K.
When calling OCILobRead2()
, you can pass a value of 0 (zero) for the byte_amt
parameter to read to the end of the BFILE
.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — READ
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — OCILobRead2()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB READ.
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB READ
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Read, and OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > PollingAmount, Offset, Status. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples
Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB): fread.sql
OCI: fread.c
Visual Basic (OO4O): fread.bas
Java (JDBC): fread.java
This section describes how to read portion of BFILE
data using SUBSTR
.
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — SUBSTR
OCI: A syntax reference is not applicable in this release.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB OPEN, LOB CLOSE. See PL/SQL DBMS_LOB.SUBSTR.
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB OPEN. See also PL/SQL DBMS_LOB.SUBSTR
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Open, and OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > PollingAmount, Offset, Status. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples
Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
Examples are provided in these five programmatic environments:
PL/SQL (DBMS_LOB): freadprt.sql
C (OCI): No example is provided with this release.
Visual Basic (OO4O): freadprt.bas
Java (JDBC): freadprt.java
This section describes how to compare all or parts of two BFILE
s.
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL(DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — COMPARE
C (OCI): A syntax reference is not applicable in this release.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB OPEN. See PL/SQL DBMS_LOB.COMPARE.
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB OPEN. See PL/SQL DBMS_LOB.COMPARE.
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Open, Compare, and OO4O Automation Server > OBJECTS > OraDatabase > PROPERTIES > Parameters. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples
Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
Examples are provided in these five programmatic environments:
PL/SQL(DBMS_LOB): fcompare.sql
OCI: No example is provided with this release.
Visual Basic (OO4O): fcompare.bas
Java (JDBC): fcompare.java
This section describes how to determine whether a pattern exists in a BFILE
using INSTR
.
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — INSTR
C (OCI): A syntax reference is not applicable in this release.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB OPEN. See PL/SQL DBMS_LOB.INSTR.
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB OPEN. See PL/SQL DBMS_LOB.INSTR.
Visual Basic (OO4O): A syntax reference is not applicable in this release.
Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
These examples are provided in the following four programmatic environments:
PL/SQL (DBMS_LOB): fpattern.sql
OCI: No example is provided with this release.
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): fpattern.java
This procedure determines whether a BFILE
locator points to a valid BFILE
instance.
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILEEXISTS
C (OCI) Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — OCILobFileExists()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB DESCRIBE ... FILEEXISTS.
C/C++ (Pro*C/C++) Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB DESCRIBE ...GET FILEEXISTS
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > Exists, and OO4O Automation Server > OBJECTS > OraDatabase > PROPERTIES > Parameters. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples
Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
The examples are provided in the following six programmatic environments:
PL/SQL (DBMS_LOB): fexists.sql
OCI: fexists.c
Visual Basic (OO4O): fexists.bas
Java (JDBC): fexists.java
This section describes how to get the length of a BFILE
.
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — GETLENGTH
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations". Chapter 16, section "LOB Functions" — OCILobGetLength2()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB DESCRIBE ... GET LENGTH INTO ...
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB DESCRIBE ... GET LENGTH INTO ...
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > Size. See also OO4O Automation Server > OBJECTS > OraBfile > Examples
Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
The examples are provided in six programmatic environments:
PL/SQL (DBMS_LOB): flength.sql
OCI: flength.c
Visual Basic (OO4O): flength.bas
Java (JDBC): flength.java
This section describes how to assign one BFILE
locator to another.
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
SQL (Oracle Database SQL Reference): Chapter 7, "SQL Statements" — CREATE PROCEDURE
PL/SQL (DBMS_LOB): Refer to Chapter 5, "Advanced Design Considerations" of this manual for information on assigning one lob locator to another.
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — OCILobLocatorAssign()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB ASSIGN
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB ASSIGN
Visual Basic (OO4O): A syntax reference is not applicable in this release.
Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
The examples are provided in the following five programmatic environments:
PL/SQL (DBMS_LOB): fcopyloc.sql
OCI: fcopyloc.c
Visual Basic: An example is not provided with this release.
Java (JDBC): fcopyloc.java
This section describes how to get the directory object name and file name of a BFILE
.
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILEGETNAME
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — OCILobFileGetName()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB DESCRIBE ...GET DIRECTORY ...
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB DESCRIBE ...GET DIRECTORY ...
Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
Examples of this procedure are provided in the following programmatic environments:
PL/SQL (DBMS_LOB): fgetdir.sql
OCI: fgetdir.c
Visual Basic (OO4O): fgetdir.bas
Java (JDBC): fgetdir.java
This section describes how to update a BFILE
by initializing a BFILE
locator.
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB): See the (Oracle Database SQL Reference), Chapter 7, "SQL Statements" — UPDATE
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — OCILobFileSetName()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — ALLOCATE. See also (Oracle Database SQL Reference), Chapter 7, "SQL Statements" — UPDATE
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives". See also (Oracle Database SQL Reference), Chapter 7, "SQL Statements" — UPDATE
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help
Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > DirectoryName, FileName, and OO4O Automation Server > OBJECTS > OraDatabase > METHODS > ExecuteSQL. See also OO4O Automation Server > OBJECTS > OraBfile > Examples
Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
The examples are provided in six programmatic environments:
PL/SQL (DBMS_LOB): fupdate.sql
OCI: fupdate.c
Visual Basic (OO4O): fupdate.bas
Java (JDBC): fupdate.java
This section describes how to close a BFILE
with FILECLOSE
.
Note: This function (FILECLOSE ) is not recommended for new development. For new development, use the CLOSE function instead. See "Closing a BFILE with CLOSE" for more information. |
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB)(Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILEOPEN, FILECLOSE
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — OCILobFileClose()
.
COBOL (Pro*COBOL): A syntax reference is not applicable in this release.
C/C++ (Pro*C/C++): A syntax reference is not applicable in this release.
Visual Basic (OO4O): A syntax reference is not applicable in this release.
Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
PL/SQL (DBMS_LOB): fclose_f.sql
OCI: fclose_f.c
Visual Basic (OO4O): This operation is not supported in Visual Basic. Instead use "Closing a BFILE with CLOSE" as described .
Java (JDBC): fclose_f.java
This section describes how to close a BFILE
with the CLOSE
function.
Note: This function (CLOSE ) is recommended for new application development. The older FILECLOSE function, is not recommended for new development. |
See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations onBFILE s and APIs provided for each programmatic environment. |
Usage Notes
Opening and closing a BFILE
is mandatory. You must close the instance later in the session.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — CLOSE
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — OCILobClose()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB CLOSE
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB CLOSE
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help
Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > IsOpen. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples.
Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
PL/SQL (DBMS_LOB): fclose_c.sql
OCI: fclose_c.c
Visual Basic (OO4O): fclose_c.bas
Java (JDBC): fclose_c.java
This section describes how to close all open BFILE
s.
You are responsible for closing any BFILE
instances before your program terminates. For example, you must close any open BFILE
instance before the termination of a PL/SQL block or OCI program.
You must close open BFILE instances even in cases where an exception or unexpected termination of your application occurs. In these cases, if a BFILE
instance is not closed, then it is still considered open by the database. Ensure that your exception handling strategy does not allow BFILE instances to remain open in these situations.
See Also:
|
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILECLOSEALL
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — OCILobFileCloseAll()
.
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB FILE CLOSE ALL
C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB FILE CLOSE ALL
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > CloseAll. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples
Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
PL/SQL (DBMS_LOB): fclosea.sql
OCI: fclosea.c
Visual Basic (OO4O): fclosea.bas
Java (JDBC): fclosea.java
This section describes how to insert a row containing a BFILE
by initializing a BFILE
locator.
See Also:
|
Usage Notes
You must initialize the BFILE
locator bind variable to NULL
or a directory object and file name before issuing the INSERT
statement.
Syntax
See the following syntax references for each programmatic environment:
SQL(Oracle Database SQL Reference, Chapter 7 "SQL Statements" — INSERT
C (OCI) Oracle Call Interface Programmer's Guide: Chapter 7, "LOB and File Operations".
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, embedded SQL, and precompiler directives. See also Oracle Database SQL Reference, for related information on the SQL INSERT statement.
C/C++ (Pro*C/C++) Pro*C/C++ Programmer's Guide: "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB FILE SET. See also (Oracle Database SQL Reference), Chapter 7 "SQL Statements" — INSERT
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > DirectoryName, FileName; and > OBJECTS > OraDynaset >
METHODS > Update
Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples
Examples in the following programmatic environments are provided:
PL/SQL (DBMS_LOB): finsert.sql
OCI: finsert.c
Visual Basic (OO4O): finsert.bas
Java (JDBC): finsert.java