clob fields from oracle dbs

[Ralf]

Hello,

I'm looking for a hint or example to read and if applicable to write clob fields from/to Oracle 10 dbs. Can anyone give me a hint how I must handle clob fields? We use vast 7.5

Regards

Ralf

[Ralf]

what I mean is, I get an AbtLobLocator instance, how can I get the value with this instance???

[tc]

Hello,

LOBs point to data, to get the data into a buffer (the table has two columns, data and id, data is a blob/clob, id a number):

Code: Select all

| 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

. . . or into a file:

Code: Select all

"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

. . . the Database Guide has many examples.

--tc

[Ralf]

Hello tc,

you are right, I could have read the documentation. Sorry, therefor. But, in the time I have found the class AbtOracleLobBuffer and have used the methods as example.

Many Thanks

Ralf