When the LOB or its contents need to modified, the corresponding record must be locked (Oracle enforces this). The best way to lock a record is to pass
:for-update
when calling
select
. See the documentation for
select
in the
LispWorks Reference Manual
for details. For example, writing a line in the end of the log file of station number 573:
create table logfiles (stationid integer, logiles clob)
.. insert records ..
(sql:do-query ((log-stream)
[select [log :output-stream] :from [logfiles]
:where [= [stationid] 573] :for-update t])
(file-position log-stream :end)
(write-line "Add this line to the log" log-stream)
(close log-stream) ; forces the output
)
(sql:commit)
Note that any call to
commit
or
rollback
on the same connection removes the lock. If you want to modify the LOB later, you must lock it again. An efficient way to achieve this is to use the special token ROWID, which returns the ROWID in the database, because this does not involve searching on the server side. For example:
(let ((lobs-list
(sql:select [lob-field][rowid] ; get pairs of LOB
:from [mytable] ; locators and ROWIDs
:where [some-condition])))
... do something ...
... reach a point when we want to modify one
... of the LOBS above and have bound one of the
... pairs in the variable pair .
(sql:select [1]
:from [mytable] ; retrieve a constant
:where
[= [rowid] (second pair )] ; get the right record
:for-update t) ; lock it
(sql:ora-lob-write-buffer (car pair ) ; modify the lob
offset
amount
buffer )
(sql:commit) ; also unlock everything
)