21 LOB APIs for BFILE Operations

This chapter describes APIs for operations that use BFILEs. APIs covered in this chapter are listed in Table 21-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 data type.

  • 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 into BFILEs. See "Using SQL*Loader to Load LOBs" for details on techniques for loading data into BFILEs.

This chapter contains these topics:

Supported Environments for BFILE APIs

Table 21-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++, COM, and JDBC.

Table 21-1 Environments Supported for BFILE APIs

Operation PL/SQL OCI COBOL Pro*C/C++ COM JDBC

Inserting a Row Containing a BFILE

Yes

Yes

Yes

Yes

Yes

Yes

Loading a LOB with BFILE Data

Yes

Yes

Yes

Yes

Yes

Yes

Opening a BFILE with FILEOPEN

Yes

Yes

No

No

No

Yes

Opening a BFILE with OPEN

Yes

Yes

Yes

Yes

Yes

Yes

Determining Whether a BFILE Is Open Using ISOPEN

Yes

Yes

Yes

Yes

Yes

Yes

Determining Whether a BFILE Is Open with FILEISOPEN

Yes

Yes

No

No

No

Yes

Displaying BFILE Data

Yes

Yes

Yes

Yes

Yes

Yes

Reading Data from a BFILE

Yes

Yes

Yes

Yes

Yes

Yes

Reading a Portion of BFILE Data Using SUBSTR

Yes

No

Yes

Yes

Yes

Yes

Comparing All or Parts of Two BFILES

Yes

No

Yes

Yes

Yes

Yes

Checking If a Pattern Exists in a BFILE Using INSTR

Yes

No

Yes

Yes

No

Yes

Determining Whether a BFILE Exists

Yes

Yes

Yes

Yes

Yes

Yes

Getting the Length of a BFILE

Yes

Yes

Yes

Yes

Yes

Yes

Assigning a BFILE Locator

Yes

Yes

Yes

Yes

No

Yes

Getting Directory Object Name and File Name of a BFILE

Yes

Yes

Yes

Yes

Yes

Yes

Updating a BFILE by Initializing a BFILE Locator

Yes

Yes

Yes

Yes

Yes

Yes

Closing a BFILE with FILECLOSE

Yes

Yes

No

No

Yes

Yes

Closing a BFILE with CLOSE

Yes

Yes

Yes

Yes

Yes

Yes

Closing All Open BFILEs with FILECLOSEALL

Yes

Yes

Yes

Yes

Yes

Yes

Accessing BFILEs

To access BFILEs 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 13, "Overview of Supplied LOB APIs" for information about supported environments for accessing BFILEs.

Directory Objects

The DIRECTORY object facilitates administering access and usage of BFILE data types. 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:

Initializing a BFILE Locator

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 requirement to specify a terminating slash (for example, /tmp/ is not necessary, simply use /tmp).

Directory specifications cannot contain ".." anywhere in the path (for example, /abc/def/hij..).

How to Associate Operating System Files with a BFILE

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.

  • Use UPDATE to change the reference target of the BFILE.

  • 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 be required 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 BFILE locator. For example, specifying:

BFILENAME('WORK_DIR', '$MY_FILE')

where MY_FILE, an environment variable defined in the operating system, is not valid.

BFILENAME and Initialization

BFILENAME is a built-in function that you use to initialize a BFILE column to point to an external file.

Once physical files are associated with records using SQL DML, subsequent read operations on the BFILE can be performed using PL/SQL DBMS_LOB package and OCI. However, these files are read-only when accessed through BFILEs, and so they cannot be updated or deleted through BFILEs.

As a consequence of the reference-based semantics for BFILEs, it is possible to have multiple BFILE columns in the same record or different records referring to the same file. For example, the following UPDATE statements set the BFILE column of the row with key_value = 21 in lob_table to point to the same file as the row with key_value = 22.

UPDATE lob_table 
   SET f_lob = (SELECT f_lob FROM lob_table WHERE key_value = 22)
      WHERE key_value = 21;

Think of BFILENAME in terms of initialization — it can initialize the value for the following:

  • BFILE column

  • BFILE (automatic) variable declared inside a PL/SQL module

Characteristics of the BFILE Data Type

Using the BFILE data type 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.

DIRECTORY Name Specification

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')

On Windows Platforms

On Windows platforms the directory names are case-insensitive. Therefore the following two statements refer to the same directory:

CREATE DIRECTORY "big_cap_dir" AS "g:\data\source";

CREATE DIRECTORY "small_cap_dir" AS "G:\DATA\SOURCE";

BFILE Security

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

Ownership and Privileges

The DIRECTORY object is a system owned object. For more information on system owned objects, see Oracle Database SQL Language 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 a 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.

Note:

The READ permission is defined only on the DIRECTORY object, not on individual files. Hence there is no way to assign different privileges to files in the same directory.

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 CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges potentially expose the server file system to all database users, the DBA should be prudent in granting these privileges to normal database users to prevent security breach.

SQL DDL for BFILE Security

Refer to the Oracle Database SQL Language Reference for information about the following SQL DDL statements that create, replace, and drop DIRECTORY objects:

  • CREATE DIRECTORY

  • DROP DIRECTORY

SQL DML for BFILE Security

Refer to the Oracle Database SQL Language Reference for information about the following SQL DML statements that provide security for BFILEs:

  • GRANT (system privilege)

  • GRANT (object privilege)

  • REVOKE (system privilege)

  • REVOKE (object privilege)

  • AUDIT (new statements)

  • AUDIT (schema objects)

Catalog Views on Directories

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.

Guidelines for DIRECTORY Usage

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 fail. Further, if a DROP or REPLACE command is executed before these files could be successfully closed, then the references to these files are lost in the programs, and system resources associated with these files are not be released until the session(s) is shut down.

    The only recourse left to PL/SQL users, for example, is 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.

BFILEs in Shared Server (Multithreaded Server) Mode

The database does not support session migration for BFILE data types 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 are bound to one shared server, they cannot migrate from one server to another.

External LOB (BFILE) Locators

For BFILEs, the value is stored in a server-side operating system file; in other words, external to the database. The BFILE locator that refers to that file is stored in the row.

When Two Rows in a BFILE Table Refer to the Same File

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.

BFILE Locator Variable

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.

Guidelines for BFILEs

Note the following guidelines when working with BFILEs:

  • 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

    Note:

    OCISetAttr() does not allow the user to set a BFILE locator to NULL.

  • 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.

Loading a LOB with BFILE Data

This section describes how to load a LOB with data from a BFILE.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Oracle Database JDBC Developer's Guide and Reference for details of working with BFILE functions in this chapter.

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:

The LOADBLOBFROMFILE 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.

Character Set Conversion

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).

See Also:

Oracle Database Globalization Support Guide for character set conversion issues.

Amount Parameter

Note the following with respect to the amount parameter:

  • DBMS_LOB.LOADFROMFILE

    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.

  • OCILobLoadFromFile()

    If you want to load the entire BFILE, then you can pass the constant UB4MAXVAL. If you pass any other value, then it must be less than or equal to the size of the BFILE.

  • OCILobLoadFromFile2()

    If you want to load the entire BFILE, then you can pass the constant UB8MAXVAL. If you pass any other value, then it must be less than or equal to the size of the BFILE.

    See Also:

    Table 22-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:

Examples

Examples are provided in the following programmatic environments:

  • PL/SQL (DBMS_LOB): floaddat.sql

  • OCI: floaddat.c

  • COM (OO4O): floaddat.bas

  • Java (JDBC): No example.

Opening a BFILE with OPEN

This section describes how to open a BFILE using the OPEN function.

Note:

You can also open a BFILE using the FILEOPEN function; however, using the OPEN function is recommended for new development. Using the FILEOPEN function is described in Opening a BFILE with FILEOPEN.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

Scenario

These examples open an image in operating system file ADPHOTO_DIR.

Examples

Examples are provided in the following programmatic environments:

  • PL/SQL(DBMS_LOB): fopen.sql

  • OCI: fopen.c

  • COM (OO4O): fopen.bas

  • Java (JDBC): fopen.java

Opening a BFILE with FILEOPEN

This section describes how to open a BFILE using the FILEOPEN function.

Note:

The FILEOPEN function is not recommended for new application development. The OPEN function is recommended for new development. See "Opening a BFILE with OPEN"

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs 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:

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

Determining Whether a BFILE Is Open Using ISOPEN

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 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in the following programmatic environments:

  • PL/SQL (DBMS_LOB): fisopen.sql

  • OCI: fisopen.c

  • COM (OO4O): fisopen.bas

  • Java (JDBC): fisopen.java

Determining Whether a BFILE Is Open with FILEISOPEN

This section describes how to determine whether a BFILE is OPEN using the FILEISOPEN function.

Note:

The FILEISOPEN function is not recommended for new application development. The ISOPEN function is recommended for new development. See Determining Whether a BFILE Is Open Using ISOPEN

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs 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:

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

Displaying BFILE Data

This section describes how to display BFILE data.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in these programmatic environments:

  • PL/SQL (DBMS_LOB): fdisplay.sql

  • OCI: fdisplay.c

  • Java (JDBC): fdisplay.java

Reading Data from a BFILE

This section describes how to read data from a BFILE.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs 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, indicates 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.)

Amount Parameter

  • 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:

Examples

Examples are provided in the following programmatic environments:

  • PL/SQL (DBMS_LOB): fread.sql

  • OCI: fread.c

  • COM (OO4O): fread.bas

  • Java (JDBC): fread.java

Reading a Portion of BFILE Data Using SUBSTR

This section describes how to read portion of BFILE data using SUBSTR.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in these five programmatic environments:

  • PL/SQL (DBMS_LOB): freadprt.sql

  • C (OCI): No example is provided with this release.

  • COM (OO4O): freadprt.bas

  • Java (JDBC): freadprt.java

Comparing All or Parts of Two BFILES

This section describes how to compare all or parts of two BFILEs.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in these five programmatic environments:

  • PL/SQL(DBMS_LOB): fcompare.sql

  • OCI: No example is provided with this release.

  • COM (OO4O): fcompare.bas

  • Java (JDBC): fcompare.java

Checking If a Pattern Exists in a BFILE Using INSTR

This section describes how to determine whether a pattern exists in a BFILE using INSTR.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

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.

  • COM (OO4O): No example is provided with this release.

  • Java (JDBC): fpattern.java

Determining Whether a BFILE Exists

This procedure determines whether a BFILE locator points to a valid BFILE instance.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

Examples

The examples are provided in the following programmatic environments:

  • PL/SQL (DBMS_LOB): fexists.sql

  • OCI: fexists.c

  • COM (OO4O): fexists.bas

  • Java (JDBC): fexists.java

Getting the Length of a BFILE

This section describes how to get the length of a BFILE.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

Examples

The examples are provided in these programmatic environments:

  • PL/SQL (DBMS_LOB): flength.sql

  • OCI: flength.c

  • COM (OO4O): flength.bas

  • Java (JDBC): flength.java

Assigning a BFILE Locator

This section describes how to assign one BFILE locator to another.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

Examples

The examples are provided in the following five programmatic environments:

  • PL/SQL (DBMS_LOB): fcopyloc.sql

  • OCI: fcopyloc.c

  • COM: An example is not provided with this release.

  • Java (JDBC): fcopyloc.java

Getting Directory Object Name and File Name of a BFILE

This section describes how to get the DIRECTORY object name and file name of a BFILE.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples of this procedure are provided in the following programmatic environments:

  • PL/SQL (DBMS_LOB): fgetdir.sql

  • OCI: fgetdir.c

  • COM (OO4O): fgetdir.bas

  • Java (JDBC): fgetdir.java

Updating a BFILE by Initializing a BFILE Locator

This section describes how to update a BFILE by initializing a BFILE locator.

See Also:

Table 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

Examples

The examples are provided in these programmatic environments:

  • PL/SQL (DBMS_LOB): fupdate.sql

  • OCI: fupdate.c

  • COM (OO4O): fupdate.bas

  • Java (JDBC): fupdate.java

Closing a BFILE with FILECLOSE

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 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

Examples

  • PL/SQL (DBMS_LOB): fclose_f.sql

  • OCI: fclose_f.c

  • COM (OO4O): This operation is not supported in COM. Instead use "Closing a BFILE with CLOSE" as described .

  • Java (JDBC): fclose_f.java

Closing a BFILE with CLOSE

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 21-1, "Environments Supported for BFILE APIs", for a list of operations on BFILEs 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.

See Also:

Syntax

Use the following syntax references for each programmatic environment:

Examples

  • PL/SQL (DBMS_LOB): fclose_c.sql

  • OCI: fclose_c.c

  • COM (OO4O): fclose_c.bas

  • Java (JDBC): fclose_c.java

Closing All Open BFILEs with FILECLOSEALL

This section describes how to close all open BFILEs.

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:

Examples

  • PL/SQL (DBMS_LOB): fclosea.sql

  • OCI: fclosea.c

  • COM (OO4O): fclosea.bas

  • Java (JDBC): fclosea.java

Inserting a Row Containing a BFILE

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:

Examples

Examples in the following programmatic environments are provided:

  • PL/SQL (DBMS_LOB): finsert.sql

  • OCI: finsert.c

  • COM (OO4O): finsert.bas

  • Java (JDBC): finsert.java