The Common SQL Oracle LOB interface allows you to retrieve LOB locators and then perform operations on them. It is also possible to insert new empty LOBs.
This is done by normal select or query calls where the selections list names one or more columns that are of a LOB type. The LOB types are BLOB, CLOB, NCLOB, BFILE and CFILE.
The returned value is a LOB locator: an opaque Lisp object on which the ora-lob-* APIs (that is, those functions with names beginning with "ora-lob-") can be used. This LOB locator contains a pointer to an Oracle descriptor of type OCILobLocator*. Note that there can be multiple LOB locator objects associated with the same LOB in the server, but a LOB locator uniquely identifies a LOB object.
It is possible to specify that the result object should be a stream either for input or output. Then the resulting stream (which will be of type lob-stream) can be used as a normal Lisp stream.
This is done using the ora-lob-* functions. Most of these functions map directly to the underlying OCILob* functions.
Note that when modifying a LOB locator, the corresponding record must be locked. See 23.11.2 Retrieving Lob Locators for details.
To add a new LOB object to the database, you must insert an empty LOB. The preferred way of doing this is to use the Oracle SQL functions EMPTY_BLOB and EMPTY_CLOB, which can called by using the pseudo operator sql-function, like this:
(sql:insert-records :into [mytable]
:values
(list "name" [sql-function 'empty_blob]))
This code inserts a record with "name" and an empty BLOB. It is also possible to make an empty LOB by calling ora-lob-create-empty, and passing the empty LOB as a value to insert-records or update-records.
When the selections list of a query that is used in select, query, do-query, map-query, simple-do-query or loop .... for x being each record contains a column of a LOB type, the results are LOB locator objects. For example, if the table definition is:
create table mytable {
name varchar(200),
image blob
}
Then doing:
(sql:select [image] :from [mytable] :flatp t)
returns a list of LOB locators.
This example lists the size of the images in the table mytable:
(dolist (pair (sql:select [name][image] :from [mytable]))
(format t "~a has an image of size ~a~%"
(first pair) (sql:ora-lob-get-length (second pair)))
(sql:ora-lob-free (second pair)))
or more efficiently:
(sql:do-query ((name lob-locator)
[sql:select [name][image] :from [mytable]])
(format t "~a has an image of size ~a~%"
name (sql:ora-lob-get-length lob-locator)))
Note: The lifetime of the LOB locator objects differs between the functions that return a list of objects (select and query) and the iterative functions and macros (do-query, simple-do-query, loop and map-query). The iteration functions and macros free the LOB locators that they retrieve before proceeding to the next iteration. select and query do not free the LOB locators. Each LOB locator stays alive until the application makes an explicit call to ora-lob-free, or until the database is closed by a call to disconnect.
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 select for details. For example, writing a line in the end of the log file of station number 573:
create table logfiles (stationid integer, logfiles 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
)
To retrieve LOB locators as streams, specify the type of retrieved object as :input-stream or :output-stream in the query. For example:
(sql:select [image :input-stream] :from [mytable] :flatp t)
returns a list of streams.
For example, to print the name of all images that start with some "magic number", that is a sequence of 4 specific bytes (#xf5 #x12 #x4e #x23):
(let ((array (make-array 4 :element-type '(unsigned-byte 8))))
(sql:do-query ((name lob-stream)
[sql:select [name][image :input-stream]
:from [mytable]])
(when (and (eq (read-sequence array lob-stream ) 4)
(eq (aref array 0) #xf5)
(eq (aref array 0) #x12)
(eq (aref array 0) #x4e)
(eq (aref array 0) #x23))
(print name))))
Closing the stream also frees the LOB object.
When using :output-stream, it is important to call force-output before trying to commit the changes, because the stream is buffered.
It is possible to attach a stream to a LOB locator, passing the LOB locator as a :lob-locator argument to (make-instance 'lob-stream ...). The value of the :direction argument must be :input or :output. By default, if the stream is closed the LOB locator is freed, unless the value of the initarg :free-lob-locator-on-close is passed as nil.
Operations via the stream can be mixed with direct operations on the LOB. However, because of the buffering, accessing the LOB contents will give non-obvious results, as other operations may not see something that was written to the stream because it is still in the stream buffer, or the stream may have already read some contents before they were overwritten. Use force-output or clear-input before accessing the LOB in other ways to avoid these problems.
It is possible to attach more than one stream to the same LOB locator, in both directions. Apart from the issue of the buffering described above, the streams can be used independently of each other. Note that if you want to close one of the streams and to continue to use the others or the LOB locator itself, you must pass :free-lob-locator-on-close nil when you make the stream.
The LOB locator to which a stream is attached can be found by using the reader lob-stream-lob-locator (see lob-stream).
You can define your own foreign calls and use them on the underlying OCI descriptors. For this, you need to access the OCI handles using ora-lob-lob-locator, and maybe ora-lob-env-handle and ora-lob-svc-ctx-handle. These accessors return foreign pointers that can be passed to foreign functions in the usual way.
When the foreign functions deal only with the data, rather than with LOB objects, use the functions ora-lob-read-foreign-buffer, ora-lob-write-foreign-buffer and ora-lob-get-buffer.
For example:
;;; You have a C function my_lob_processor
;;; int my_lob_processor(OCILobLocator *lob,
;;; OCISvcCtx *Context,
;;; int other_arg)
(fli:define-foreign-function my-lob-processor
((lob sql:p-oci-lob-locator)
(env sql:p-oci-svc-ctx)
(other-arg :int))
:result-type :int)
Assuming you have the LOB locator in the variable lob, call the foreign function on it:
(my-lob-processor (sql:ora-lob-lob lob)
(sql:ora-lob-svc-ctx-handle lob)
36)
There are three handles in the LOB: the LOB descriptor itself, the environment and the context. The pointer types, the reader and the corresponding C type for each handle are shown in Handles in the LOB locator below.
| OCI handle | Reader | Pointer type | C type |
|---|---|---|---|
LOB | OCILobLocator* | ||
context | OCISvcCtx* | ||
environment | OCIEnv* |
The pointer type p-oci-lob-locator is used for internal LOBs (that is, BLOB, CLOB and NCLOB). The pointer type p-oci-file is used for file LOBs (CFILE and BFILE). For functions that take both, the type p-oci-lob-or-file is defined as the union of these two types.
The function ora-lob-internal-lob-p returns whether it is internal (that is BLOB, CLOB or NCLOB) or not (that is BFILE or CFILE). The function ora-lob-element-type returns the LISP element type that best corresponds to the LOB locator. This will be one of (unsigned-byte 8) for BLOB and BFILE, or base-char or bmp-char for CLOB, NCLOB and CFILE, depending on the charset of the LOB object.
It is possible to distinguish between CLOB and NCLOB by looking at the result of ora-lob-char-set-form. It returns 2 for NCLOB and 1 for CLOB.
One way of reading and writing is to use streams as described in the section 23.11.4 Retrieving LOB Locators as streams. When large amounts of data are written (read) to (from) the LOB the direct interface may be useful. The direct interface is implemented by ora-lob-read-foreign-buffer, ora-lob-read-buffer, ora-lob-write-foreign-buffer, and ora-lob-write-buffer.
All the direct interfaces are more efficient if the buffer that is passed is static. That is always true for the *-foreign-buffer functions, but normally not true for Lisp objects. See the documentation for make-array. See also ora-lob-get-buffer.
The direct reading and writing methods can be used for "random" access, but they can also be used conveniently for efficient linear access, simply by passing nil as the offset parameter.
Most of the LOB functions take an errorp argument, which is a boolean controlling what happens if an error occurs inside an OCI function. If errorp is true, an error is signaled. If errorp is false, the function returns an error object (of type sql-database-error).
All the LOB functions signal an error if the lob-locator argument given is not a LOB locator object as returned by select or query.
Many of the functions basically perform a call to the underlying OCI function. When the match is direct, this is mentioned in the function's manual page.
You can test whether a LOB locator is initialized, open or temporary with ora-lob-locator-is-init, ora-lob-is-open or ora-lob-is-temporary.
The predicate for internal LOBs is ora-lob-internal-lob-p.
ora-lob-element-type returns a Lisp element type corresponding to the LOB locator as described 23.11.7 Determining the type of a LOB.
ora-lob-lob-locator, ora-lob-env-handle and ora-lob-svc-ctx-handle return foreign pointers to the various handles in the LOB mentioned in 23.11.6 Interactions with foreign calls. To determine the best value for the size of a buffer use ora-lob-get-chunk-size.
ora-lob-char-set-form and ora-lob-char-set-id query the charset of a lob-locator.
The querying functions specifically for file LOBs are ora-lob-file-exists, ora-lob-file-is-open and ora-lob-file-get-name.
You can obtain the current length of the LOB with ora-lob-get-length.
You can test two LOB locators for whether they point to the same LOB object with ora-lob-is-equal.
You can create a LOB object with ora-lob-create-empty.
You can assign a LOB to another LOB locator with ora-lob-assign.
You can free a LOB locator with ora-lob-free.
All the functions mentioned in this section are applicable to internal LOBs only, except ora-lob-load-from-file.
Before modifying a LOB, the corresponding record must be locked. See the discussion in 23.11.3 Locking.
If you make several modifications to a LOB which has functional or domain indexes, it is useful to wrap several calls of modifying functions in a pair of ora-lob-open and ora-lob-close. That means that the indexes will be updated once (when ora-lob-close is called), which saves work. Note that after a call to ora-lob-open, ora-lob-close must be called before any call to commit.
To append the contents of one LOB to another, use ora-lob-append.
You can copy all or part of a LOB into another LOB using ora-lob-copy.
ora-lob-load-from-file loads the data from a file LOB into an (internal) LOB.
You can erase (that is, fill with the 0 byte or with Space character) all or part of a LOB using ora-lob-erase.
You can reduce the size of a LOB using ora-lob-trim.
If you need to make multiple updates to a LOB you can optionally create a transaction using ora-lob-open and ora-lob-close call. This may save work on the server side.
These functions are used to modify the properties of file LOBs.
Open and close the file associated with a file LOB using ora-lob-file-open and ora-lob-file-close.
You can close all the files associated with a file LOB locator that have been opened through the database connection with ora-lob-file-close-all.
You can alter the directory and/or the file name for a file LOB locator by calling ora-lob-file-set-name.
The direct I/O functions perform input or output directly on the OCI handle, without the intervening layer of a stream. If you move large amounts of data to or from the LOB, and in particular if you pass the data to or from foreign functions, the direct calls can be more efficient, and in some cases also more convenient to use. Note, however, that if you make many small modifications to the data, the lob-stream interface may be more efficient.
Note also that the difference in efficiency between the direct calls and the lob-stream interface is likely to be quite small compared to the time spent on network traffic.
If you make many modifications to a LOB, you should also consider wrapping the operations in a transaction created by a pair of calls to ora-lob-open and ora-lob-close.
You can read data from the LOB locator into a Lisp buffer or foreign buffer using ora-lob-read-buffer and ora-lob-read-foreign-buffer respectively.
Similarly ora-lob-write-buffer and ora-lob-write-foreign-buffer can be used to write buffer to a LOB.
You can obtain a buffer suitable for efficient I/O with foreign functions via ora-lob-get-buffer.
ora-lob-read-into-plain-file writes the contents of a LOB into a file.
ora-lob-write-from-plain-file writes the contents of a file into a LOB.
You can create a temporary LOB with ora-lob-create-temporary.
You can test whether a LOB is temporary with ora-lob-is-temporary.
You can free a temporary LOB locator if necessary with ora-lob-free-temporary, though temporary LOB locators are freed automatically when the database connection is closed by disconnect.
These functions control the internal buffering by the Oracle client: ora-lob-enable-buffering, ora-lob-disable-buffering, and ora-lob-flush-buffer. They have no interaction with any of the other functions above.
Sometimes it useful to fetch the contents of a LOB directly. You can do that by specifying the type of the requested value as :binary for binary LOBs (BLOB and BFILE) or :string for character LOBs (CLOB, NCLOB, and CFILE). When you specify the type in this way, the fetched values are arrays of type (unsigned-byte 8) for :binary and strings for :string. For example:
(sql:select [blob_column] :from [a_table]) => a list of LOB locators (sql:select [blob_column :binary] :from [a_table]) => a list of arrays
LispWorks® User Guide and Reference Manual - 01 Dec 2021 19:30:23