LOB Enhancements In Oracle9i
A number of enhancements have been made to LOBs including the following.
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
-- 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
|| can all
be performed without reference to the
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.
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.
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...