Using the lld_lob Type

The lld_lob is a user-defined type that you can use to specify the location of a smart large object and to specify whether the object contains binary or character data. The following subsections show how to use the lld_lob data type.

Using Implicit lld_lob Casts

This section shows how to insert binary and character data into an lld_lob type column of a table. The example in Figure 1 makes use of implicit casts from BLOB and CLOB types to the lld_lob type.

create table slobs (key int primary key, slo lld_lob);

--Insert binary and text large objects into an lld_lob field
--Implicitly cast from blob/clob to lld_lob
insert into slobs values (1, filetoblob ('logo.gif', 'client'));

insert into slobs values (2, filetoclob ('quote1.txt', 'client'));

select * from slobs;


key  1
slo  blob:00608460a6b7c8d900000002000000030000000200000018000000000001000000608
     460736c6f000010029a2a6c92070000000000006c000af0cdd900000080006082500af0c9d
     e 

key  2
slo  clob:00608460a6b7c8d900000002000000030000000300000019000000000001000000608
     460736c6f000010029a2a6c930d0000000000006c000af0cdd900000016000000010af0c9d
     e 

The slobs table, created in this example, contains the slo column, which is of type lld_lob. The first INSERT statement uses the filetoblob function to copy a binary large object to a smart large object. There exists an implicit cast from a BLOB type to an lld_lob type, so the INSERT statement can insert the BLOB type large object into an lld_lob type column.

Likewise, there is an implicit cast from a CLOB type to an lld_lob type, so the second INSERT statement can insert a CLOB type large object into the slo column of the slobs table.

The SELECT statement returns the lld_lob types that identify the two smart large objects stored in the slobs table.

The slo column for key 1 contains an instance of an lld_lob type that identifies the data as BLOB data and contains a hexadecimal number that points to the location of the data.

The slo column for key 2 identifies the data as CLOB data and contains a hexadecimal number that points to the location of the data.

Using Explicit lld_lob Casts

The example in Figure 2 shows how to select large objects of type BLOB and CLOB from a table and how to copy them to a file.

This example uses the slobs table created in Figure 1.

--Explicitly cast from lld_lob to blob/clob
select slo::blob from slobs where key = 1;


(expression)  <SBlob Data>


select slo::clob from slobs where key = 2;


(expression)  
Ask not what your country can do for you,
but what you can do for your country.

The first SELECT statement retrieves the data in the slo column associated with key 1 and casts it as BLOB type data. The second SELECT statement retrieves the data in the slo column associated with key 2 and casts it as CLOB type data.

Using the LLD_LobType Function

The example in this section, Figure 3, shows how to use the LLD_LobType function to obtain the type of data—BLOB or CLOB—that an lld_lob column contains.

The slobs table in this example is the same one created in Figure 1. That example created the table and inserted a BLOB type large object for key 1 and a CLOB type large object for key 2.

-- LLD_LobType UDR
select key, lld_lobtype(slo) from slobs;

        key (expression) 

          1 blob
          2 clob


select slo::clob from slobs where lld_lobtype(slo) = 'clob';


(expression)  
Ask not what your country can do for you,
but what you can do for your country.

The first SELECT statement returns:

1 blob
2 clob

indicating that the data associated with key 1 is of type BLOB and the data associated with key 2 is of type CLOB.

The second SELECT statement uses LLD_LobType to retrieve the columns containing CLOB type data. The second SELECT statement casts the slo column (which is of type lld_lob) to retrieve CLOB type data.