ODAC application Techniques (ii) Use BLOBs and CLOB data types

The ODAC component supports the BLOB and CLOB data types of Oracle 8. You can use the Toraquery component to get the value of the LOB field, and you can also get a long or long ROW field using the same method. When you need to use SQL DML and PL/SQL statements to access these fields, you will find that the use of LOB data types is significantly different.

Blob and CLOB data types are stored in table columns through LOB locators (the specified data address), and the actual blob and CLOB data are stored in separate tablespaces. In contrast, long or long RAW types are stored in the database, and their actual values are stored in the table.

When the LOB column is accessed, the locator is returned instead of returning its actual value as long or long RAW data type.

For example, analyze the definition of this table:

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

If we do not initialize the LOB locator with the value parameter, Oracle will not allow the following statement to be used to update the datasheet:

UPDATE ClobTable
SET
Name = :Name,
Value = :Value

WHERE
Id = :Id

To initialize the LOB locator, you must use the Empty_blob or Empty_clob Oracle functions. To return the initialized locator, you should use the returning clause in the same statement. For example:

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

Odac writes LOB data to Oracle and returns the initial value field, using: Value parameter.

Stored procedures allow for automatic initialization of LOB values, as follows:

CREATE OR REPLACE
PROCEDURE ClobTableUpdate (p_Id NUMBER, p_Name VARCHAR2,
p_Value OUT CLOB)
is
begin
UPDATE ClobTable
SET　
Name = p_Name,
Value = EMPTY_CLOB()
WHERE
Id = p_Id
RETURNING
Value
INTO
p_Value;
end;