Reading and Writing a LOB to/from a Buffer
This section discusses two kinds of LOBs, CLOBs and BLOBs. CLOBs are ‘character large object binary’ and BLOBs are ‘binary large object binary’. From a Smalltalk perspective, they operate almost the same way. A CLOB table is created in SQL Plus as follows:
SQL> create table clob_table (id number, data clob);
Table created.
A BLOB table is created by doing the following:
SQL> create table blob_table (id number, data blob);
Table created.
Before a LOB is used, one must insert an empty BLOB or CLOB. In Smalltalk, to insert an empty BLOB row, create an instance of AbtEmptyBlob. To insert an empty CLOB, create an instance of AbtEmptyClob. Using SQL Plus, one would do the following to define and write CLOB information:
SQL> create table clob_table (id number, data clob);
Table created.
SQL> declare
l_clob clob;
begin
insert into clob_table values ( 1, empty_clob() ) returning data into l_clob;
for i in 1 .. 10
loop
dbms_lob.writeAppend( l_clob, length('Hello World'), 'Hello World' );
end loop;
end;
SQL> /
PL/SQL procedure successfully completed.
At this point, ‘hello world’ is stored in the data column of the CLOB table. If I select this row, I get the following:
SQL> select id, data from clob_table where id = 1;
ID
----------
DATA
--------------------------------------------------------------------------------
1
Hello WorldHello WorldHello WorldHello WorldHello WorldHello WorldHello WorldHel
It is important to note that even though SQL Plus prints the data column when a select is done, in fact, a pointer is stored in this column and not the data itself. The data is stored in a file which is controlled by Oracle. To do the same as above using Smalltalk 7.5, assuming the table is created, one must first create a row and insert an empty clob into the table:
"Insert an empty blob in a table"
| connection table oc clob newRow |
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName:
'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
connection autoCommit: true.
table := (connection openTableNamed: 'clob_table').
clob := AbtOracleEmptyClob new.
oc := OrderedCollection new.
newRow := table emptyRow.
newRow
at: #ID put: 5;
at: #DATA put: clob.
oc add: newRow.
table addRows: oc.
Now that the row is created with an empty CLOB, we must get the lob locator and
use it to insert data:
| result fromFile querySpec resultCollection sqlString dict connection
lobLocator buffer |
"get locator address"
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName:
'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
sqlString := 'select * from clob_table where id = 5 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.
buffer := 'Hello World! Hello World! Hello World! Hello World! Hello World!
Hello World! Hello World! Hello World! Hello World! Hello World!' asByteArray.
AbtOracleLobBuffer new
writeBufferToLob: lobLocator buffer: buffer connection: connection.
connection commitUnitOfWork
Note above that we first had to select the LOB pointer and then use it to write data to the LOB. In the select statement, ‘for update’ was added, if this is not done, then you might get a ‘row not locked’ error.
Selecting the row in SQL Plus:
SQL> select id, data from clob_table where id = 5
SQL> /
ID
----------
DATA
--------------------------------------------------------------------------------
5
Hello World! Hello World! Hello World! Hello World! Hello World! Hello World!
To read the row using Smalltalk:
| result fromFile querySpec resultCollection sqlString dict connection
lobLocator buffer |
"get locator address"
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName:
'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
connection autoCommit: true.
sqlString := 'select * from clob_table where id = 5'.
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.
buffer := ByteArray new: 200.
buffer := AbtOracleLobBuffer new
writeLobToBuffer: buffer lob: lobLocator connection: connection.
^buffer
BLOBs work the exact same way as above except where CLOB is used, use BLOB.