Migrating From LONGs to LOBs, 3 of 14

Guidelines for Using LONG-to-LOB API

The following are guidelines for using LONG-to-LOB API.

Using ALTER TABLE

Use ALTER TABLE to convert LONG columns in existing tables to LOBs. See "Migrating LONGs to LOBs: Using ALTER TABLE to Change LONG Column to LOB Types" .

LONG-to-LOB API and OCI

Binds in OCI

Previously, a VARCHAR2 buffer of more than 4000 bytes of data could only be bound to a LONG column. The LONG-to-LOB API now allows this functionality for LOBs. It works for the following:

Regular, piecewise, and callback binds for INSERTs and UPDATEs
Array binds for INSERTs and UPDATEs
Parameter passing or across PL/SQL and OCI boundaries

The following OCI functions are part of the LONG-to-LOB API:

OCIBindByPos()
OCIBindByName()
OCIBindDynamic()
OCIStmtSetPieceInfo()
OCIStmtGetPieceInfo()

They accept the following datatypes for inserting or updating LOB columns:

Defines in OCI

The LONG-to-LOB API allows the following OCI functions to accept VARCHAR2 buffer and SQLT_CHR, SQLT_LNG, SQLT_LBI, and SQLT_BIN datatypes as LOB column outputs:

OCIDefineByPos()
OCIDefineDynamic()
OCIStmtSetPieceInfo()
OCIStmtGetPieceInfo()

When you do this, the LOB data (and not the locator) is selected into your buffer.

Note:

In the OCI LONG-to-LOB API, you cannot specify the amount you want to read. You can only specify the buffer length of your buffer. So Oracle just reads whatever amount fits into your buffer.

OCI Functions Allow Piecewise and Array INSERT, UPDATE, or Fetch on LOBs

The above mentioned OCI functions allow piecewise INSERT, UPDATE, or fetch, and array INSERT, UPDATE, or fetch on LOBs. They allow you to provide data dynamically at run-time for INSERTs and UPDATEs into LOBs.

The bind (INSERT and UPDATE) functions worked for LOBs in prior releases in the same way as they do for LONGs.

Defines (SELECT) now work for LOBs in regular, piecewise, callback, and array mode.

Multibyte Charactersets (OCI)

When the Client's characterset is multibyte, these functions behave the same as for LONGs.

For a piecewise fetch in a multibyte characterset, a multibyte character could be cut in middle, with some bytes in one buffer and remaining bytes in the next buffer.
For regular fetch, if the buffer cannot hold all bytes of the last character, then Oracle returns as many bytes as fit into the buffer, hence returning partial characters.

LONG-to-LOB API and PL/SQL

INSERT and UPDATE of LOB Columns (PL/SQL)

In prior releases, in PL/SQL, you could INSERT or UPDATE the following:

Character data, such as VARCHAR2, CHAR, or LONG, into a CLOB column
Binary data, such as RAW or LONG RAW, into a BLOB column

See "PL/SQL Interface" .

SELECT on a LOB Column (PL/SQL)

PL/SQL accepts SELECT statements on a CLOB column, where, a character variable, such as VARCHAR2, CHAR, or LONG, is provided in the INTO clause. See "Using SQL and PL/SQL to Access LOBs" . The same holds for selecting a BLOB column into a binary variable, such as RAW or LONG RAW.

Note:

In the PL/SQL LONG-to-LOB API, you cannot specify the amount you want to read. You can only specify the buffer length of your buffer. If your buffer length is smaller than the LOB data length, Oracle throws an exception.

Assignment and Parameters Passing (PL/SQL)

PL/SQL allows implicit type conversion and assignment of the following:

CLOB variables to VARCHAR2, CHAR, or LONG variables and vice-versa.
BLOB variables to RAW and LONG RAW variables and vice versa.

The same holds for parameter passing. Hence PL/SQL allows the passing of the following:

A CLOB as an actual parameter to a function whose formal parameter is a character type, such as VARCHAR2, CHAR, or LONG, or vice versa

A BLOB as an actual parameter to a procedure or function whose formal parameter is a binary type, such as RAW or LONG RAW, or vice versa.

See Also:

PL/SQL built-in functions and operators which accept VARCHAR2 arguments also accept CLOB arguments now. For example, INSTR, SUBSTR, comparison operators,...

See Also:

"VARCHAR2 and CLOB in PL/SQL Built-In Functions" , for a complete list.