Working with Oracle BFILEs
The .NET Framework Data Provider for Oracle includes the OracleBFile class, which is used to work with the Oracle BFile data type.
The Oracle BFILE data type is an Oracle LOB data type that contains a reference to binary data with a maximum size of 4 gigabytes. An Oracle BFILE differs from other Oracle LOB data types in that its data is stored in a physical file in the operating system instead of on the server. Note that the BFILE data type provides read-only access to data.
Other characteristics of a BFILE data type that distinguish it from a LOB data type are that it:
Contains unstructured data.
Supports server-side chunking.
Uses reference copy semantics. For example, if you perform a copy operation on a BFILE, only the BFILE locator (which is a reference to the file) is copied. The data in the file is not copied.
The BFILE data type should be used for referencing LOBs that are large in size, and therefore, not practical to store in the database. More client, server, and communication overhead is involved when using a BFILE data type compared with the LOB data type. It is more efficient to access a BFILE if you only need to obtain a small amount of data. It is more efficient to access database-resident LOBs if you need to obtain the entire object.
Each non-NULL OracleBFile object is associated with two entities that define the location of the underlying physical file:
An Oracle DIRECTORY object, which is a database alias for a directory in the file system, and
The file name of the underlying physical file, which is located in the directory associated with the DIRECTORY object.
Example
The following C# example demonstrates how you can create a BFILE in an Oracle table and then retrieve it in the form of an OracleBFile object. The example demonstrates using the OracleDataReader object and the OracleBFile Seek and Read methods. Note that in order to use this sample, you must first create a directory named "c:\\bfiles" and file named "MyFile.jpg" on the Oracle server.
using System;
using System.IO;
using System.Data;
using System.Data.OracleClient;
public class Sample
{
public static void Main(string[] args)
{
OracleConnection connection = new OracleConnection(
"Data Source=Oracle8i;Integrated Security=yes");
connection.Open();
OracleCommand command = connection.CreateCommand();
command.CommandText =
"CREATE or REPLACE DIRECTORY MyDir as 'c:\\bfiles'";
command.ExecuteNonQuery();
command.CommandText =
"DROP TABLE MyBFileTable";
try {
command.ExecuteNonQuery();
}
catch {
}
command.CommandText =
"CREATE TABLE MyBFileTable(col1 number, col2 BFILE)";
command.ExecuteNonQuery();
command.CommandText =
"INSERT INTO MyBFileTable values ('2', BFILENAME('MyDir', " +
"'MyFile.jpg'))";
command.ExecuteNonQuery();
command.CommandText = "SELECT * FROM MyBFileTable";
byte[] buffer = new byte[100];
OracleDataReader reader = command.ExecuteReader();
using (reader) {
if (reader.Read()) {
OracleBFile bFile = reader.GetOracleBFile(1);
using (bFile) {
bFile.Seek(0, SeekOrigin.Begin);
bFile.Read(buffer, 0, 100);
}
}
}
connection.Close();
}
}