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.
