23.11.1 Introduction

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.

(sql:insert-records :into [mytable]

                    :values 

                    (list "name" [sql-function 'empty_blob]))

23.11.2 Retrieving Lob Locators

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

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.

23.11.3 Locking

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

  )

23.11.4 Retrieving LOB Locators as streams

To retrieve LOB locators as streams, specify the type of retrieved object as :input-stream or :output-stream in the query. For example:

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.

23.11.5 Attaching a stream to a LOB locator

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 .

23.11.6 Interactions with foreign calls

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.

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

23.11.7 Determining the type of a LOB

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.

23.11.8 Reading and writing from and to LOBs

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.

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.

23.11.9 The LOB functions

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.

23.11.10 Fetching the contents of the LOBs directly

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