|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Migrating From LONGs to LOBs, 3 of 14
The following are guidelines for using LONG-to-LOB API.
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" .
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:
The following OCI functions are part of the LONG-to-LOB API:
They accept the following datatypes for inserting or updating LOB columns:
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:
When you do this, the LOB data (and not the locator) is selected into your buffer.
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.
When the Client's characterset is multibyte, these functions behave the same as for LONGs.
In prior releases, in PL/SQL, you could INSERT or UPDATE the following:
See "PL/SQL Interface" .
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.
PL/SQL allows implicit type conversion and assignment of the following:
The same holds for parameter passing. Hence PL/SQL allows the passing of the following:
PL/SQL built-in functions and operators which accept VARCHAR2 arguments also accept CLOB arguments now. For example, INSTR, SUBSTR, comparison operators,...
"VARCHAR2 and CLOB in PL/SQL Built-In Functions" , for a complete list.