Reading and Writing a LOB to/from a File
BLOBS are ‘binary large object binary’ and are created in SQL Plus as follows:
SQL> create table blob_table (id number, data blob);
Table created.
Before inserting BLOB information, a row with an empty blob must be inserted in the blob_table:
"Insert an empty blob in a table"
| connection table oc blob newRow |
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
connection autoCommit: true.
table := (connection openTableNamed: 'blob_table').
blob := AbtOracleEmptyBlob new.
oc := OrderedCollection new.
newRow := table emptyRow.
newRow
at: #ID put: 1;
at: #DATA put: blob.
oc add: newRow.
table addRows: oc.
At this point, a row is created with an empty blob. The next step is to get the LOB locator, a pointer which is stored in the BLOB column and then write a file to the BLOB (store the file in the database):
| result fromFile querySpec resultCollection sqlString dict connection lobLocator |
"get locator address"
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
connection autoCommit: true.
sqlString := 'select * from blob_table where id = 1 for update'.
querySpec := (AbtQuerySpec new) statement: sqlString.
resultCollection := OrderedCollection new.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: eachRow asDictionary ].
dict := resultCollection first.
lobLocator := (dict at: 'DATA') locatorAddress.
"open text file and get file size"
fromFile := CfsFileDescriptor
open: CwFileSelectionPrompter new title: 'Input file'; prompt.
oflag: CfsConstants::ORDONLY.
fromFile isCfsError
ifTrue: [^System prompt: fromFile message ].
AbtOracleLobBuffer new
writeFileToLob: lobLocator file: fromFile connection: connection
To do the opposite, write a LOB to a file, we must first get the LOB locator:
"get locator address"
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
connection autoCommit: true.
sqlString := 'select * from blob_table where id = 1'.
querySpec := (AbtQuerySpec new) statement: sqlString.
resultCollection := OrderedCollection new.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: eachRow asDictionary ].
dict := resultCollection first.
lobLocator := (dict at: 'DATA') locatorAddress.
The user chooses a filename and the file is written:
"open text file and get file size"
filename := CwFileSelectionPrompter new title: 'Output file'; prompt.
toFile := CfsFileDescriptor
open: filename
oflag: CfsConstants::OWRONLY | CfsConstants::OCREAT | CfsConstants::OTRUNC.
toFile isCfsError
ifTrue: [^System prompt: toFile message ].
AbtOracleLobBuffer new
writeLobToFile: toFile lob: lobLocator connection: connection
Once again, LOBS are being read and written using a buffer of 1000 bytes (an instance of ByteArray). This can be changed to suit your needs in the class AbtOracleLobBuffer.