8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

LOB Enhancements In Oracle9i

A number of enhancements have been made to LOBs including the following.

LOB Migration
SQL And PL/SQL Function Support
Implicit LOB Conversions
OCI Support
Issues

LOB Migration

In Oracle 8i a LONG column could be converted to a LOB using the TO_LOB() function. One of the restrictions on it's use was that it could not be used to convert a column in an existing table, only during the creation of a new table. In Oracle 9i this conversion is possible using the ALTER TABLE .. MODIFY syntax.

-- Column content is currently a LONG
ALTER TABLE documents MODIFY (content CLOB);

-- Column content is currently a LONG RAW
ALTER TABLE executables MODIFY (content BLOB);

This syntax can only be used for the conversion of LONG and LONG RAW columns.

SQL And PL/SQL Function Support

In Oracle9i most SQL functions and operators that accept VARCHAR2 parameters are now be able to accept CLOBS. This means operations such as SUBSTR, INSTR and || can all be performed without reference to the DBMS_LOB package.

As with SQL functions, all PL/SQL functions in the STANDARD package can accept CLOBS and BLOBS where only LONGS and RAWS were possible before. This support has been extended to all built-ins.

Implicit LOB Conversions

Implicit conversions between LONG and CLOB columns and RAW and BLOB columns is now possible. This increases flexibility while programming allowing code such as that shown below.

DECLARE
  l_clob  CLOB;
  l_char  VARCHAR2(32500);
BEGIN
  SELECT content
  INTO   l_char
  FROM   documents
  WHERE  id = 12345;
  v_clob := RTRIM(l_char);
END;
/

This implicit conversion extends to assignments, such that a VARCHAR2 can be assigned to a CLOB column during a DML INSERT, and procedure parameters, such that a VARCHAR2 variable can be passed as a parameter to a procedure defined to accept a CLOB.

OCI Support

Support for LOBs in the OCI has been improved by lifting the the 4K restriction on VARCHAR2 buffers that are bound to LOB columns. This means that up to 4GB buffers can be bound to LOB columns for INSERT, UPDATE or SELECT using combinations of buffers including LONG, RAW, CLOB, BLOB and VARCHAR2.

Issues

These enhancements are primarily aimed at easing the conversion of LONG and RAW columns to LOBs. The implicit conversions mean that the majority of database code should function correctly after the table columns have been migrated, although it should be revisited to optimize performance.

There are some restrictions on the use of LOBs that should be considered before you start to convert all LONG columns:

Unlike LONGs, LOBs are not allowed in clustered tables.
Migration of replicated tables requires the replicated objects to be manually migrated.
LOBs are not allowed in the UPDATE OF list in update triggers.
Implicit conversions are not allowed in INSTEAD OF triggers.
The ALTER TABLE .. MODIFY syntax required all indexes to be rebuilt manually as it acts like a MOVE.
Domain indexes on LONG columns must be dropped before conversion to LOBs.

Hope this helps. Regards Tim...

Back to the Top.