BLOB and CLOB Data Types

ODAC components support Oracle 8 BLOB and CLOB data types. You can retrieve values of LOB fields using TOraQuery component the same way as you do for LONG or LONG RAW fields. The difference with usage of LOB data type becomes evident when you need to access these fields in SQL DML and PL/SQL statements.

For BLOB and CLOB data types only LOB locators (pointers to data) are stored in table columns; actual BLOB and CLOB data is stored in separate tablespace. This is the difference to the way that data of LONG or LONG RAW types is stored in database – tables hold their immediate values.

When accessing LOB column, it is the locator which is returned, not the value itself as in the case with LONG or LONG RAW data types.

For example consider this table definition.

CREATE TABLE ClobTable (
  Id NUMBER,
  Name VARCHAR2(30),
  Desc CLOB
)

To update Desc column of this table we need to create CLOB, get its locator, write CLOB data using this locator and excecute UPDATE statement to write the locator into the table field.

The following SQL statement can be used to update ClobTable:

UPDATE ClobTable
SET
  Name = :Name,
  Desc = EMPTY_CLOB()
WHERE
  Id = :Id
RETURNING
  Desc
INTO
  :Desc

You can use EMPTY_BLOB or EMPTY_CLOB Oracle function to create empty LOB. After executing this statement LOB locator is returned into Desc parameter. Then ODAC writes LOB data into database using this locator. You must set ParamType of Desc parameter to ptInput.

It is important for ODAC to use ParamType property of parameters in LOB operations. If ParamType is ptInput ODAC writes data to a server, if ParamType is ptOutput it reads data.

Another way to update ClobTable is using temporary LOB. Set TemporaryLobUpdate in TOraDataSet.Options to True. Then ODAC will create temporary LOB and write data to it before executing SQL statement.

When TemporaryLobUpdate option is True following statement can be used to update ClobTable:

UPDATE ClobTable
SET
  Name = :Name,
  Desc = :Desc
WHERE
  Id = :Id

ODAC will initialize Desc parameter with locator of temporary LOB before executing this statement.

TemporaryLobUpdate option should be set to True when calling stored procedure with IN or IN OUT LOB parameter. To call procedure:

CREATE OR REPLACE
PROCEDURE ClobTableUpdate (p_Id IN NUMBER, p_Name IN VARCHAR2,
                           p_Desc IN CLOB)
IS
BEGIN0
  UPDATE ClobTable
  SET 
    Name = p_Name,
    Desc = p_Desc
  WHERE
    Id = p_Id;
END;

the following code can be used:

OraStroredProc.Options.TemporaryLobUpdate := True;
OraStroredProc.StoredProcName := 'ClobTableUpdate';
OraStroredProc.Prepare;
OraStroredProc.ParamByName('p_Id').AsInteger := Id;
OraStroredProc.ParamByName('p_Name').AsString := Name;
OraStroredProc.ParamByName('Desc').ParamType := ptInput;
OraStroredProc.ParamByName('Desc').AsOraClob.LoadFromFile(FileName);
OraStroredProc.Execute;

Note that LOB parameter can have ptInputOutput type only when TemporaryLobUpdate option is set toTrue. Otherwise the type of LOB parameter must be ptInput or ptOutput.

You can also use dtBlob and dtMemo datatypes with LOB parameters to write ordinary DML statements. In this case Oracle automatically converts LONG and LONG ROW values to CLOB or BLOB data.

It is possible to control the way LOB objects are handled while the application fetches records from the database. LOBs can be fetched either with other fields to the application or on demand.This is determined by DeferredLobRead option in TOraDataSet component. Setting TOraDataSet.Options.DeferredLobRead to false allows to reduce traffic over the network since LOBs are only transferred on demand and to use less memory on the client side because returned record sets do not hold contents of LOB fields.

For managing LOB compression, use TCustomDADataSet.Options.CompressBlobMode. LOBs can be stored compressed on the client side, on the server side (in database) or on both sides. By default it has cbNone value, that means no compression is provided. Use cbClient value to store compressed LOBs on client side. This saves client memory. LOB data is stored unchanged in a database, other applications can read these LOBs as usual. If cbServer value is used, LOB data is stored compressed in the database. It's decompressed on the client side. This saves server disk space and network traffic. Other application can't process compressed LOB data as usual. To use compressed LOB data both on the client and server sides use cbClientServer value. To use cbClient, cbServer, cbClientServer and cbNone constants you should add the MemData unit to the uses clause.

Set TOraDataSet.Options.CacheLobs to False to access streamed LOB values on the server side without caching LOBs on the client side. Only requested portions of data are fetched in that case. Setting CacheBlobs to False may bring up the following benefits for time-critical applications: reduced traffic over the network since only required data are fetched, less memory is needed on the client side because LOB data is not cached on client side. This option doesn't make sense if DeferredLobRead is set to False because in that case all LOB values are fetched to the dataset.

Note: Internal compression functions are available in Borland Developer Studio 2006, Delphi 2005 and Delphi 7. To use BLOB compression under Delphi 6 and C++Builder you should use your own compression functions. To use them set CompressProc and UncompressProc variables declared in MemUtils unit.

type
  TCompressProc = function(dest: IntPtr; destLen: IntPtr; 
  const source: IntPtr; sourceLen: longint): longint;
  TUncompressProc = function(dest: IntPtr; destlen: IntPtr; 
  source: IntPtr; sourceLne: longint): longint;
 
var
  CompressProc: TCompressProc;
  UncompressProc: TUncompressProc;

You can compress and decompress a single LOB. To do it set the TOraLob.Compressed property. Set it to True to compress LOB data and to False to decompress LOB data.

Note that using compression and decompression operations will raise CPU usage and can reduce application performance.

BLOB and CLOB Data Types

See Also