BFiles
Bfiles are files that are controlled by the operating system but a pointer to the file, a locator, is stored in an Oracle table. Bfiles, in general, are not recommended because if the file pointed to is moved or deleted, the pointer in the Oracle table is no longer valid. A bfile table is created in SQL Plus as follows:
SQL> create table test3 (id int, theBfile bfile)
Next, setup the directory:
SQL> create or replace directory my_files as 'd:\temp';
Finally, store a pointer to a file in Oracle:
SQL> insert into test3 values(1,bfilename('MY_FILES','car.jpg'));
To show that the bfile pointer is valid, do the following:
SQL> select id,dbms_lob.getlength(theBfile) from test3;
ID DBMS_LOB.GETLENGTH(THEBFILE)
---------- ----------------------------
1 182409
At this point, if the file ‘car.jpg’ is moved or deleted then the pointer in Oracle is no longer valid. Inserting a bfile from Smalltalk is done as follows:
"Insert a row in a table"
| connection newRow table conSpecString oc bfile |
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName:
'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
connection autoCommit: true.
table := (connection openTableNamed: 'TEST3').
bfile := AbtOracleBFileReference new
directory: 'MY_FILES';
filename: 'car.jpg';
yourself.
oc := OrderedCollection new.
newRow := table emptyRow.
newRow
at: #ID put: 5 * n;
at: #THEBFILE put: bfile.
oc add: newRow.
table addRows: oc
Bfiles are read from an Oracle table as follows:
"read a bfile row from a table."
| conSpecString connection sqlString querySpec resultCollection result dict |
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName:
'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
sqlString := 'select * from test3'.
querySpec := (AbtQuerySpec new) statement: sqlString.
resultCollection := OrderedCollection new.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: eachRow asDictionary ].
resultCollection.
dict := resultCollection at: 2.
connection getLobFilename: (dict at: 'THEBFILE').
The last line returns the filename. To get the directory name, replace the last line with:
connection getLobDirectoryName: (dict at: 'THEBFILE').
Once you have the directory and filename, you can use a file stream to open and read and/or modify the file since it is an operating system file.
Last modified date: 01/29/2015