18.9.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 .