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.
Note that when modifying a LOB locator, the corresponding record must be locked. See 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:
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:
(sql:select [image] :from [mytable] :flatp t)
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:
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:
(sql:select [image :input-stream] :from [mytable] :flatp t)
It is possible to attach a stream to a LOB locator, passing the LOB locator as a
:lob-locator argument to
...). The value of the
:direction argument must be
: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
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
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
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.
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.
The pointer type
is used for internal LOBs (that is, BLOB, CLOB and NCLOB). The pointer type
is used for file LOBs (CFILE and BFILE). For functions that take both, the type
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
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 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.
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).
The predicate for internal LOBs is ora-lob-internal-lob-p.
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 Interactions with foreign calls. To determine the best value for the size of a buffer use ora-lob-get-chunk-size.
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 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.
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.
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.
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:
LispWorks User Guide and Reference Manual - 20 Sep 2017