Summary of New Functionality Associated with the LONG-to-LOB API

OCI Functions

OCIDefineByPos() function now accepts the following types:

• SQLT_CHR and SQLT_LNG for CLOB columns

• SQLT_BIN and SQLT_LBI for BLOB column

So, for a LOB column, you can define a VARCHAR2 buffer and on the subsequent OCIStmtFetch() command, you will get the buffer filled with the CLOB/BLOB data.

OCIBindByPos()and OCIBindByName() functions now accept buffers of up to 4 gigabytes in size.

SQL Statements

The following new syntax has been added:

ALTER TABLE [<schema>.]<table_name>
MODIFY ( <long_column_name> { CLOB | BLOB | NCLOB } [DEFAULT <default_
value> ) [LOB_storage_clause];

See "Migrating LONGs to LOBs: Using ALTER TABLE to Change LONG Column to LOB Types", for details. Changes made to the ALTER TABLE syntax are as follows:

• The "datatype" can now be:
  • (CLOB|NCLOB) if the original datatype of the "long_column_name" was LONG

  • (BLOB) if the original datatype was LONG RAW

• Only during this (LONG->LOB) conversion can the LOB_storage_clause clause be specified in the MODIFY clause.

• During this (LONG->LOB) conversion, you can only specify the [DEFAULT "expr"]. No other ALTER TABLE operation is allowed with this operation.

PL/SQL Interface

You can now use the following PL/SQL SELECT statements:

• SELECT on CLOB columns INTO a character buffer variable, such as CHAR, LONG, or VARCHAR2

• SELECT on BLOB columns INTO a binary buffer variable, such as RAW and LONG RAW

You can also make the following assignments:

• Assign a CLOB (BLOB) to a VARCHAR2 (RAW) variable

• Assign a VARCHAR2(RAW) variable to a CLOB (BLOB)

In addition, a CLOB (BLOB) can be passed as an actual parameter to a function with a formal parameter of VARCHAR2 (RAW) and vice-versa, and can call PL/SQL built-in functions on LOBs.

See: Chapter 7, "Modeling and Design", "SQL Semantics Support for LOBs" .