8
Migrating From LONGs to LOBs

This chapter contains the following topics:

Introducing LONG-to-LOB Migration

To assist you in migrating to LOBs, Oracle supports the LONG API for LOBs. This API ensures that when you change your LONG columns to LOBs, your existing applications will require few changes, if any.

The term, "LONG API", refers to DML and querying of LONG datatypes. Examples of the LONG API are:

For OCI: OCIBindByName(), OCIBindDynamic(), OCIDefineByPos(), OCIDefineDynamic(),....
For PL/SQL: substr, instr,... and parameter passing

Note:
The "LONG API" applies to other datatypes besides LONGs. In this chapter, however, we are specifically interested in this API for LOBs. We refer to it here as the "LONG-to-LOB API".

Oracle9i supports LONG as well as LOB datatypes. When possible, change your existing applications to use LOBs instead of LONGs because of the added benefits that LOBs provide. See Chapter 7, "Modeling and Design", "LOBs Compared to LONG and LONG RAW Types".

This chapter describes how the "LONG API" referred to here as "LONG-to-LOB API", is used for LOBs.

Note:

The performance of some LOB operations improves with the LONG-to-LOB API. See "Performance" for details.

Using the LONG-to-LOB API Results in an Easy Migration

LONG-to-LOB migration allows you to easily migrate your existing applications that access LONG columns, to use LOB columns. The migration has two parts:

Data migration. This consists of the procedure to move existing tables containing LONG columns to use LOBs. This is described in "Migrating Existing Tables from LONG to LOB" .

Application migration. This specifies how existing LONG applications will change for using LOBs. Your application will only need to change in very rare cases. The LONG-to-LOB API that is implemented for LOBs is described in "Using LONG-to-LOB API with OCI" and "Using SQL and PL/SQL to Access LONGs and LOBs" .

Note:

LONGs have various restrictions, such as, there can be at most, only one LONG column in a table,....

On the other hand, LOBs do not have such restrictions. After you migrate your LONG tables to use LOBs, you will no longer have these LONG restrictions. You can have multiple LOB columns in a table, and do multiple >4k binds in a single INSERT/UPDATE,...

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:

SQLT_CHR and SQLT_LNG for CLOB columns
SQLT_BIN and SQLT_LBI for BLOB columns
See Also:

"Using OCI Functions to Perform INSERT or UPDATE on LOBs"

"PL/SQL and C Binds from OCI"

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 OCI functions mentioned earlier, 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.

Migrating Existing Tables from LONG to LOB

This section describes techniques for migrating existing tables from LONG to LOB datatypes.

Migrating LONGs to LOBs: Using ALTER TABLE to Change
LONG Column to LOB Types

ALTER TABLE now allows a LONG column to be modified to CLOB or NCLOB and a LONG_RAW column to be modified to BLOB. The syntax is as follows:

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

For example, if you had a table with the following definition:

CREATE TABLE Long_tab (id NUMBER, long_col LONG);

you can change the column long_col in table Long_tab to datatype CLOB as follows:

ALTER TABLE Long_tab MODIFY ( long_col CLOB );

Note:

The new ALTER TABLE statement only modifies either of the following:

A LONG column to a CLOB or an NCLOB column
A LONG RAW column to a BLOB column

It will not modify a VARCHAR or a RAW column.

Note:

In the new ALTER TABLE statement to change a LONG column to a LOB, the only other operations allowed are:

Specifying the default value for the LOB column
Specifying the LOB storage clause for the column being changed from LONG to LOB

Any other ALTER TABLE operation is not allowed with this operation.

Note:

Migrating LONGs to LOBs: Method Used in Oracle8i

This method of migrating LONGs to LOBs replaces the following method used in Oracle8i. Oracle8i added a new operator on LONGs called TO_LOB(). TO_LOB() copies the LONG to a LOB. You can use CREATE TABLE AS SELECT or INSERT AS SELECT statements with the TO_LOB operator to copy data from the LONG to the LOB. For example, if you have a table with the following definition:

CREATE TABLE Long_tab (id NUMBER, long_col LONG);

Do the following:

CREATE TABLE Lob_tab (id NUMBER, clob_col CLOB);

INSERT INTO Lob_tab SELECT id, TO_LOB(long_col) FROM long_tab;

DROP TABLE Long_tab;

CREATE VIEW Long_tab (id, long_col) AS SELECT * from Lob_tab;

This series of operations is equivalent to changing the datatype of the column Long_col of table Long_tab from LONG to CLOB. With this method (the method of choice prior to this release) you have to create all the constraints, triggers, and indexes on the new table again.

All Constraints of LONG Column are Maintained

All constraints of your previous LONG columns are maintained for the new LOB columns. The only constraint allowed on LONG columns are NULL and NOT-NULL. To alter the constraints for these columns, or alter any other columns or properties of this table, you have to do so in a subsequent ALTER TABLE statement.

Default Values for LONG are Copied to LOB

If you do not specify a default value, the default value for the LONG column is copied to the new LOB column.

Most Triggers Remain Valid

Most of the existing triggers on your table are still usable, however two types of triggers can cause issues.

See:

"LONG-to-LOB Migration Limitations" for more details.

Indexes Must be Rebuilt -- Use ALTER INDEX...REBUILD

Domain indexes on the LONG column must be dropped before ALTERing the LONG column to LOB.

All other indexes, including domain and functional indexes on all columns of the table, will be unusable and must be rebuilt using the ALTER INDEX <index name> REBUILD statement.

Rebuilding Indexes After a LONG to LOB Migration

To rebuild your indexes on a given table, after a LONG to LOB migration, use the following steps:

Drop the domain indexes on the LONG column, if any
ALTER TABLE Long_tab MODIFY ( long_col CLOB...)...;
SELECT index_name FROM user_indexes WHERE table_name='LONG_TAB';

Note:
The table name has to be capitalized in this query.
For all indexes <index> listed in step 3, issue the command:
```
ALTER INDEX <index> REBUILD
```
Create the domain index on the LOB column, if desired.

Space Requirements are Temporarily Doubled

The ALTER TABLE MODIFY LONG->LOB statement copies the contents of the table into a new space, and frees the old space at the end of the operation. This temporarily doubles the space requirements. But the advantage is that after the transformation, the table will not have any embedded NULLs, so the performance of subsequent DMLs or queries is good.

LOGGING

During migration, the redo changes for the table are logged only if the table has LOGGING on. Redo changes for the column being converted from LONG to LOB are logged only if the storage characteristics of the LOB indicate LOGGING. The default value for LOGGING|NOLOGGING for the LOB is inherited from the tablespace in which the LOB is being created.

To prevent generation of redo space during migration, do the following to migrate smoothly:

ALTER TABLE Long_tab NOLOGGING;
ALTER TABLE Long_tab MODIFY ( long_col CLOB [default <default_val>]) LOB (long_col) STORE AS (... NOLOGGING...);
ALTER TABLE Long_tab MODIFY LOB long_col STORE AS (...LOGGING...);
ALTER TABLE Long_tab LOGGING;
Take a backup of the tablespaces containing the table and the LOB.

LONG-to-LOB Migration Limitations

Before migrating from LONGs to LOBs, note the following issues:

Clustered Tables

LOBs are not allowed in clustered tables, whereas LONGs are allowed. So if a table is a part of a cluster, its LONG or LONG RAW column cannot be changed to LOB.

Replication

Oracle does not support the replication of columns that use the LONG and LONG RAW datatypes. Oracle simply omits columns containing these datatypes from replicated tables. You must convert LONG datatypes to LOBs in Oracle8i and then replicate.

This is not a restriction imposed by LONG-to-LOB, but instead, the LONG-to-LOB migration enables the replication of these columns.

If a table is replicated or has materialized views, and its LONG column is changed to LOB, you may have to manually fix the replicas.

Triggers

Triggers are a problem in the following case:

You cannot have LOB columns in the UPDATE OF list in the UPDATE OF trigger. LONG columns are allowed. For example, you cannot say:

create table t(lobcol CLOB);
create trigger trig before/after update of lobcol on t ...;

Hence, in the following case the trigger becomes invalidated and cannot be recompiled:

create table t(lobcol LONG);
create or replace trigger trig before (or after) update of lobcol on t
for each row
begin
  dbms_output.put_line('lmn');
end;
/
insert into t values('abc');
UPDATE t SET lobcol = 'xyz';

ALTER TABLE t MODIFY (lobcol CLOB); -- invalidates the trigger
UPDATE t SET lobcol = 'xyz'; -- doesn't execute because trigger
                             -- can't be revalidated

This restriction may be removed in a future release. All other triggers work without a problem.

Indexes

Indexes on any column of the table being migrated must be manually rebuilt. This includes functional and domain indexes, must be manually rebuilt.

Domain indexes for the LONG column must be dropped before ALTERing a LONG column to LOB.
Functional indexes on LONG columns converted to LOBs should work without any changes to your code.

LONGs, LOBs, and NULLs

There is a difference in how NULL and zero-length LONGs and LOBs behave. Applications migrating from LONG-to-LOB are not affected by this behavior, as follows:

Consider these two tables, long_tab and lob_tab:

CREATE TABLE long_tab(id NUMBER, long_col LONG);
CREATE TABLE lob_tab(id NUMBER, lob_col LOB);

NULL LONGs Versus Zero Length LONGs

Zero length LONGs and NULL LONGs are the same. So the following two statements each produce the same result, each one inserting a NULL in the LONG column:

INSERT INTO long_tab values(1, NULL);
INSERT INTO long_tab values(1, ''); -- Zero length string inserts NULL into the 
LONG column

NULL LOBs Versus Zero Length LOBs

For LOBs, the following two statements also insert a NULL in the LOB column:

INSERT INTO lob_tab values(1, NULL);
INSERT INTO lob_tab values(1, '');  -- A zero length string inserts NULL into 
LOB column

However, if we truly insert a zero-length LOB using the empty_clob() constructor, the LOB column will be non-NULL.

INSERT INTO lob_tab values(1, empty_clob());  -- A zero length LOB is not the 
same as NULL

Using LONG-to-LOB API with OCI

Prior to this release, OCI provided interface calls for performing piecewise INSERTS, UPDATES, and fetches of LONG data. These APIs also allow you to provide data dynamically in case of array INSERTs or UPDATEs, instead of providing a static array of bind values. These piecewise operations can be performed by polling or by providing a callback.

The following functions are now supported for LOBs for you to directly INSERT, UPDATE, and fetch LOB data without your having to deal with the LOB locator:

OCIBindByName() or OCIBindByPos(). These functions create an association between a program variable and a placeholder in the SQL statement or a PL/SQL block for INSERT/UPDATE.
OCIBindDynamic(). This call is used to register user callbacks for dynamic data allocation for INSERT/UPDATE.
OCIDefineByPos(). This call associates an item in a SELECT-list with the type and output data buffer.
OCIDefineDynamic(). This call registers user callbacks for SELECTs if the OCI_DYNAMIC_FETCH mode was selected in OCIDefineByPos().
OCIStmtGetPieceInfo() and OCIStmtSetPieceInfo(). These calls are used to get or set piece information for piecewise operations.

See Also:
"Runtime data allocation and piecewise operations" in the Oracle Call Interface Programmer's Guide, for details on the API.

Guidelines for Using LONG-to-LOB API for LOBs with OCI

The aforementioned OCI functions work in this release for LOBs in exactly the same way as they do for LONGs. Using these, you can perform INSERTs, UPDATEs, and fetches of data as described here.

Note:

When you use the aforementioned functions for CLOBs, BLOBs, LONGs, and LONG RAWs, specify the datatype (dty) as:

SQLT_LNG and SQLT_CHR for CLOBs and LONGs
SQLT_LBI and SQLT_BIN for BLOBs and LONG RAWs

Using OCI Functions to Perform INSERT or UPDATE on LOBs

There are various ways to perform INSERT or UPDATE of LOB data.

Note:

These are in addition to the ways to insert LOB locators, which are documented in Chapter 10, "Internal Persistent LOBs".

In all the ways described in the following, it is assumed that you have initialized the OCI environment and allocated all necessary handles.

Simple INSERTs or UPDATEs in One Piece

To perform simple INSERTs and UPDATEs in one piece, the steps are:

OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.
OCIBindByName() or OCIBindbyPos() to bind a placeholder in OCI_DEFAULT mode to bind a LOB as CHAR or BIN.
OCIStmtExecute() to do the actual INSERT/UPDATE.

Using Piecewise INSERTs and UPDATEs with Polling

To perform piecewise INSERTs and UPDATEs with polling, the steps are:

OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.
OCIBindByName() or OCIBindbyPos() to bind a placeholder in OCI_DATA_AT_EXEC mode to bind a LOB as CHAR or BIN.
OCIStmtExecute() in default mode. This should return OCI_NEED_DATA.
While (returned value is OCI_NEED_DATA), do the following:
- OCIStmtGetPieceInfo() to retrieve information about piece to be inserted
- OCIStmtSetPieceInfo() to set information about piece to be inserted
- OCIStmtExecute. You are done when the return value is OCI_SUCCESS.

Piecewise INSERTs and UPDATEs with Callback

To perform piecewise INSERTs and UPDATEs with callback, the steps are:

OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.
OCIBindByName() or OCIBindbyPos() to bind a placeholder in OCI_DATA_AT_EXEC mode to bind a LOB as CHAR or BIN.
OCIBindDynamic() to specify the callback.
OCIStmtExecute() in default mode.

Array INSERTs and UPDATEs

Use any of the preceding modes in conjunction with OCIBindArrayOfStruct(), or by specifying the number of iterations (iter) value > 1 in the OCIStmtExecute() call.

Using OCI Functions to Perform FETCH on LOBs

There are three ways to fetch the LOB data.

Note:

These are in addition to the ways to fetch the LOB locator, which are documented in Chapter 10, "Internal Persistent LOBs".

Simple fetch in one piece
Piecewise fetch
Array fetch

Simple Fetch in One Piece

To perform a simple fetch on LOBs in one piece, the steps involved are:

OCIStmtPrepare() to prepare the SELECT statement in OCI_DEFAULT mode.
OCIDefineByPos() to define a select list position in OCI_DEFAULT mode to define a LOB as CHAR or BIN.
OCIStmtExecute() to execute the SELECT statement.
OCIStmtFetch() to do the actual fetch.

Piecewise Fetch with Polling

To perform a piecewise fetch on LOBs with polling, the steps are:

OCIStmtPrepare() to prepare the SELECT statement in OCI_DEFAULT mode.
OCIDefinebyPos() to define a select list position in OCI_DYNAMIC_FETCH mode to define a LOB as CHAR or BIN.
OCIStmtExecute() to execute the SELECT statement.
OCIStmtFetch() in default mode. This should return OCI_NEED_DATA.
While (returned value is OCI_NEED_DATA), do the following:
- OCIStmtGetPieceInfo() to retrieve information about piece to be fetched.
- OCIStmtSetPieceInfo() to set information about piece to be fetched.
- OCIStmtFetch. You are done when the return value is OCI_SUCCESS.

Piecewise with Callback

To perform a piecewise fetch on LOBs with callback, the steps are:

OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.
OCIDefinebyPos() to define a select list position in OCI_DYNAMIC_FETCH mode to define a LOB as CHAR or BIN.
OCIStmtExecute() to execute the SELECT statement.
OCIDefineDynamic() to specify the callback.
OCIStmtFetch() in default mode.

Array Fetch

Use any of the preceding modes in conjunction with OCIDefineArrayOfStruct(), or by specifying the number of iterations (iter) value >1 in the OCIStmtExecute() call.

Using SQL and PL/SQL to Access LONGs and LOBs

This section describes the following topics:

Using SQL and PL/SQL to Access LOBs

Data from CLOB and BLOB columns can be referenced by regular SQL statements, such as: INSERT, UPDATE and SELECT.

There is no piecewise INSERT/UPDATE/fetch routine in PL/SQL. Therefore the amount of data that can be accessed from the LOB column is limited by the maximum character buffer size. In Oracle9i, PL/SQL supports character buffer sizes up to 32767 bytes. Hence only LOBs of sizes up to 32767 bytes can be accessed by PL/SQL applications.

If you need to access more than 32k, OCI callouts have to be made from the PL/SQL code to utilize the APIs for piecewise insert and fetch.

The following are guidelines for accessing LOB columns:

INSERTs

Data can be inserted into tables containing LOB columns by regular INSERTs in the VALUES clause. The field of the LOB column can be PL/SQL character or binary buffer variables ( CHAR, VARCHAR2, RAW,...), a string literal, or a LOB locator.

UPDATEs

LOB columns can be updated as a whole by UPDATE... SET statements. There is no random access of data stored in LOB columns. In the SET clause, the new values can also be literals or any PL/SQL character or binary variables, or a LOB locator.

Restriction for LONG RAW and RAW Buffers More Than 4000 Bytes.

There is a restriction for binds which exists for both LONGs and LOBs. You cannot bind a VARCHAR2 buffer to a LONG RAW or a BLOB column if the buffer is of size more than 4000 bytes, because SQL will not do implicit HEXTORAW conversions for buffers larger than 4000 bytes. Similarly, you cannot bind a RAW buffer to a LONG or a CLOB column if the buffer is of size more than 4000 bytes because SQL will not do implicit RAWTOHEX conversions for buffers larger than 4000 bytes.

SELECTs

For fetch, in prior releases, you could not use SELECT INTO to bind a character variable to a LOB column. SELECT INTO used to bind LOB locators to the column. This constraint has been removed.

LOB columns can be selected into character or binary buffers in PL/SQL. If the LOB column is longer than the buffer size, an exception is raised without filling the buffer with any data. LOB columns can also be selected into LOB locators.

Implicit Assignment and Parameter Passing

The LONG-to-LOB migration API supports assigning a CLOB (BLOB) variable to a LONG(LONG RAW) or a VARCHAR2(RAW) variable and vice-versa. This is because of the existence of %type and %rowtype datatypes in PL/SQL. The assignments include parameter passing. These features are explained in detail in the following section.

Variable Assignment Between CLOB/CHAR and BLOB/RAW

The following variable assignment between CLOB and CHAR, and BLOB and RAWs are allowed:

CLOB_VAR := CHAR_VAR;
CHAR_VAR := CLOB_VAR;
BLOB_VAR := RAW_VAR;
RAW_VAR := BLOB_VAR;

This is done because of the presence of %type and %rowtype in existing code. For example:

CREATE TABLE t (long_col LONG); -- Alter this table to change LONG column to LOB
DECLARE
   a VARCHAR2(100);
   b t.long_col%type; -- This variable changes from LONG to CLOB
BEGIN
   SELECT * INTO b FROM t;
   a := b;  -- This changes from "VARCHAR2 := LONG to VARCHAR2 := CLOB
   b := a;  -- This changes from "LONG := VARCHAR2 to CLOB := VARCHAR2 
END;

Function/Procedure Parameter Passing

This allows all the user-defined procedures and functions to use CLOBs and BLOBs as actual parameters where VARCHAR2, LONG, RAW, and LONG RAW are formal parameters and vice-versa. It also allows PL/SQL built-ins like INSTR to accept CLOB data in addition to strings. For example:

CREATE PROCEDURE FOO ( a IN OUT t.long_col%type) IS......
CREATE PROCEDURE BAR (b IN OUT VARCHAR2) IS ...
DECLARE
    a VARCHAR2(100);
    b t.long_col%type  -- This changes to CLOB
BEGIN
    a := 'abc';
    SELECT long_col into b from t;
    FOO(a); -- Actual parameter is VARCHAR2, formal parameter is CLOB
    BAR(b); -- Actual parameter is CLOB, formal parameter is VARCHAR2
END;

Explicit Conversion Functions

In PL/SQL, the following two new explicit conversion functions have been added to convert other data types to CLOB and BLOB as part of LONG-to-LOB migration:

TO_CLOB() converts LONG, VARCHAR2, and CHAR to CLOB
TO_BLOB() converts LONG RAW and RAW to BLOB

TO_CHAR() is enabled to convert a CLOB to a CHAR type.

VARCHAR2 and CLOB in PL/SQL Built-In Functions

PL/SQL VARCHAR2 functions and operators take CLOBs as arguments or operands. A CLOB can be passed to SQL and PL/SQL VARCHAR2 built-in functions, behaving exactly like a VARCHAR2. Or the VARCHAR2 variable can be passed into DBMS_LOB APIs acting like a LOB locator.

The PL/SQL built-in functions which accept CLOB parameters or give CLOB output are:

LENGTH, LENGTHB
INSTR, INSTRB
SUBSTR, SUBSTRB
CONCAT, ||
LPAD, RPAD
LTRIM, RTRIM, TRIM
LIKE
REPLACE
LOWER, UPPER
NLS_LOWER, NLS_UPPER
NVL
Comparison operators (>, =, <,!=)

If a function returns a CLOB and the result is assigned to a VARCHAR2 variable, but the size of the VARCHAR2 variable is not large enough to contain the result, an error is raised and no operation is performed. The same holds if you try to SELECT a CLOB into a VARCHAR2 variable. This is consistent with the current VARCHAR2 behavior.

These functions implicitly create temporary LOBs. Hence, some LOB locators can change from persistent to temporary. As a result, any changes to the data pointed to by the (temporary) LOB locator are not reflected in the persistent LOB which it initially pointed to.

These temporary LOBs are freed automatically at the end of the PL/SQL block.You can choose to free them explicitly to reclaim system resources and temporary tablespace by calling DBMS_LOB.FREE_TEMPORARY() on the CLOB variable.

PL/SQL and C Binds from OCI

When you call a PL/SQL procedure from OCI, and have an in or out or in/out bind, you should be able to:

Bind a variable as SQLT_CHR or SQLT_LNG where the formal parameter of the PL/SQL procedure is SQLT_CLOB, or
Bind a variable as SQLT_BIN or SQLT_LBI where the formal parameter is SQLT_BLOB

The following two cases work:

Calling PL/SQL Outbinds in the "begin foo(:1); end;" Manner.

Here is an example of calling PL/SQL outbinds in the "begin foo(:1);end;" manner:

text *sqlstmt = (text *)"BEGIN get_lob(:c); END; " ;

Calling PL/SQL Outbinds in the "call foo(:1);" Manner.

Here is an example of calling PL/SQL outbinds in the "call foo(:1);" manner:

text *sqlstmt = (text *)"CALL get_lob( :c );" ;

In both these cases, the rest of the program is as follows:

OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
        ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   curlen = 0;
OCIBindByName(stmthp, &bndhp[3], errhp,
        (text *) ":c", (sb4) strlen((char *) ":c"),
        (dvoid *) buf5, (sb4) LONGLEN, SQLT_CHR,
        (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
        (ub4) 1, (ub4 *) &curlen, (ub4) OCI_DATA_AT_EXEC);

The PL/SQL procedure, get_lob(), is as follows:

procedure get_lob(c INOUT CLOB) is  -- This might have been column%type 
  begin
  ... /* The procedure body could be in PL/SQL or C*/
  end;

Calling PL/SQL and C Procedures from SQL or PL/SQL

From SQL

When a PL/SQL procedure is called from SQL, LONG parameters are not allowed. So this case is not a part of the LONG-to-LOB conversion process.

From PL/SQL

You can call a PL/SQL or C procedure from PL/SQL. It is possible to pass a CLOB as an actual parameter where CHR is the formal parameter, or vice versa. The same holds for BLOBs and RAWs.

These cases arise when either the formal or the actual parameter is an anchored type, that is, table%type.

Applications Requiring Changes When Converting From LONGs to LOBs

Even with implicit conversions to LOBs, some changes will have to be made to your application. Cases where you will have to make changes to your application, are listed in the following paragraphs.

Overloading with Anchored Types

For applications using anchored types, some overloadings would silently resolve to different targets during the conversion to LOBs. For example:

procedure p(l long) is ...;       -- (1)  
procedure p(c clob) is ...;       -- (2)

Consider the caller:

declare  
     var  longtab.longcol%type;  
   begin  
     ...  
   p(var);  
     ...  
end;

Prior to LOB migration this call would have resolved to overload (1). Once longtab is migrated to LOBs this call will silently resolve to overload (2). A similar issue arises if the parameter to (1) was CHAR, VARCHAR2, RAW, LONG RAW.

When migrating LONG columns to LOB you have to manually examine and fix dependent applications.

Because of the new conversions, some existing applications with procedure overloadings, that include LOB arguments, may still break. This includes applications that DO NOT use LONG anchored types. For example,

procedure p(n number) is ...;       -- (1)  
procedure p(c clob) is ...;         -- (2)  
  
p('abc');

Previously, the only conversion allowed was CHAR to NUMBER, so (1) would be chosen. Now, both conversions are allowed, so the call is ambiguous and raises an overloading error.

Implicit Conversion of NUMBER, DATE, ROW_ID, BINARY_INTEGER,
and PLS_INTEGER to LOB is Not Supported

PL/SQL currently permits conversion of NUMBER, DATE, ROW_ID, BINARY_INTEGER, and PLS_INTEGER to LONG. There are no plans to support implicit conversions from these types to LOB (explicit or implicit). Users relying on these conversions will have to explicitly convert these types TO_CHAR. Hence, if you had an assignment of the form:

number_var := long_var;  -- The RHS becomes a LOB variable after conversion

Then you have to explicitly modify your code to say:

number_var := TO_CHAR(long_var); -- Note that long_var is of type CLOB after 
conversion

No Implicit Conversions of BLOB to VARCHAR2, CHAR, or CLOB to RAW
or LONG RAW

Also, there is no implicit conversion from the following:

BLOB to VARCHAR2, CHAR, or LONG
CLOB to RAW or LONG RAW

Hence if you had the following code:

SELECT <long raw column> INTO <varchar2> VARIABLE FROM <table>

and you changed the LONG RAW column into BLOB, this SELECT statement will not work. You have to add the TO_RAW or a TO_CHAR conversion operator on the selected variable such as:

SELECT TO_RAW(<long raw column>) INTO <varchar2> VARIABLE FROM <table>  
-- note that the long raw column is now a BLOB column

The same holds for selecting a CLOB into a RAW variable, or for assignments of CLOB to RAW and BLOB to VARCHAR2.

Using utldtree.sql to Determine Where Your Application Needs Change

Use the utility, rdbms/admin/utldtree.sql, to determine which parts of your application potentially need rewriting when you ALTER your LONG tables to LOBs.

utldtree.sql allows you to see all objects that are (recursively) dependent on a given object. In addition, you will only see objects for which you have permission.

Instructions on how to use utldtree.sql is documented in the file itself. Hence you can see all objects which depend on the table with the LONG column, and compare that with the cases documented in the section titled "Applications Requiring Changes When Converting From LONGs to LOBs" , to see if your application needs changing.

utldtree.sql is only needed for PL/SQL. For SQL and OCI you do not need to change your applications.

Examples of Converting from LONG to LOB Using Table Multimedia_tab

See Appendix B, "The Multimedia Schema", for a detailed description of the Multimedia_tab schema. The fields used in the following examples are:

CREATE TABLE Multimedia_tab ( 
   Clip_ID         NUMBER NOT NULL, 
   Story           CLOB default EMPTY_CLOB(), 
   FLSub           NCLOB default EMPTY_CLOB(), 
   Photo           BFILE default NULL, 
   Frame           BLOB default EMPTY_BLOB(), 
   Sound           BLOB default EMPTY_BLOB(), 
   Voiced_ref      REF Voiced_typ,
   InSeg_ntab      InSeg_tab, 
   Music           BFILE default NULL, 
   Map_obj         Map_typ 
 ) NESTED TABLE    InSeg_ntab STORE AS InSeg_nestedtab;

Suppose the column, STORY,of table MULTIMEDIA_TAB was of type LONG before, that is, you originally created the table MULTIMEDIA_TAB as follows:

CREATE TABLE MULTIMEDIA_TAB (CLIP_ID NUMBER,
    STORY  LONG, 
    .... );

To Convert LONG to CLOB, Use ALTER TABLE

To convert the LONG column to CLOB just use ALTER TABLE as follows:

ALTER TABLE multimedia_tab MODIFY ( story CLOB );

and you are done!

Any existing application using table MULTIMEDIA_TAB can continue to work with minor modification even after the column STORY has been modified to type CLOB.

Here are examples of all operations (binds and defines) used by LONGs and that will continue to work for LOBs with minor modifications as described in "Applications Requiring Changes When Converting From LONGs to LOBs" .

Converting LONG to LOB Example 1: More than 4K Binds and Simple INSERTs

The following example illustrates converting from LONG to LOBs when using a >4K bind and simple INSERT:

word buflen, buf1 = 0;
text buf2[5000];
text *insstmt = (text *)
"INSERT INTO MULTIMEDIA_TAB(CLIP_ID, STORY) VALUES 
(:CLIP_ID, :STORY)";

if (OCIStmtPrepare(stmthp, errhp, insstmt, 
(ub4)strlen((char *)insstmt), (ub4) OCI_NTV_SYNTAX, 
(ub4) OCI_DEFAULT))
{
    DISCARD printf("FAILED: OCIStmtPrepare()\n");
    report_error(errhp);
    return;
}

if (OCIBindByName(stmthp, &bndhp[0], errhp,
  (text *) ":CLIP_ID", (sb4) strlen((char *) ":CLIP_ID"),
  (dvoid *) &buf1, (sb4) sizeof(buf1), SQLT_INT,
  (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  ||  OCIBindByName(stmthp, &bndhp[1], errhp,
  (text *) ":STORY", (sb4) strlen((char *) ":STORY"),
  (dvoid *) buf2, (sb4) sizeof(buf2), SQLT_CHR,
  (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
{
    DISCARD printf("FAILED: OCIBindByName()\n");
    report_error(errhp);
    return;
}

buf1 = 101;
memset((void *)buf2, (int)'A', (size_t)5000);

if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
           (const OCISnapshot*) 0, (OCISnapshot*) 0,
           (ub4) OCI_DEFAULT))
{
      DISCARD printf("FAILED: OCIStmtExecute()\n");
      report_error(errhp);
      return;
}

Converting LONG to LOB Example 2: Piecewise INSERT with Polling

Continuing the preceding example...

text *sqlstmt  = (text *)"INSERT INTO MULTIMEDIA_TAB VALUES (:1, :2)";
ub2 rcode;
ub1 piece, i;

OCIStmtPrepare(stmthp, errhp, sqlstmt,
                  (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX,
                  (ub4) OCI_DEFAULT);

OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 1,
                  (dvoid *) &buf1, (sb4) sizeof(buf1), SQLT_INT,
                  (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);

OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 2,
                  (dvoid *) 0, (sb4) 15000, SQLT_LNG,
                  (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                  (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC);
buf1 = 101;
i = 0;
while (1)
{
    i++;
    retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                  (ub4) OCI_DEFAULT);

switch(retval)
    {
          case OCI_NEED_DATA:

        memset((void *)buf2, (int)'A'+i, (size_t)5000);
        buflen = 5000;
        if (i == 1) piece = OCI_ONE_PIECE
            else if (i == 3) piece = OCI_LAST_PIECE
            else piece = OCI_NEXT_PIECE;
    
        if (OCIStmtSetPieceInfo((dvoid *)bndhp[1],
                  (ub4)OCI_HTYPE_BIND, errhp, (dvoid *)buf2,
                  &buflen, piece, (dvoid *) 0, &rcode))
        {
          DISCARD printf("ERROR: OCIStmtSetPieceInfo: %d \n", retval);
          break;
        }

        retval = OCI_NEED_DATA;
        break;
      case OCI_SUCCESS:
        break;
      default:
        DISCARD printf( "oci exec returned %d \n", retval);
        report_error(errhp);
        retval = 0;
    }    /* end switch */
    if (!retval) break;
} /* end while(1) */

Converting LONG to LOB Example 3: Piecewise INSERT with Callback

The following example illustrates converting from LONG to LOBs when using a piecewise INSERT with callback:

void insert_data()
{
text *sqlstmt = (text *) "INSERT INTO MULTIMEDIA_TAB VALUES (:1, :2)";
OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)
/* bind input */
if (OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 1,
                      (dvoid *) 0, (sb4) sizeof(buf1), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 2,
                      (dvoid *) 0, (sb4) 3 * sizeof(buf2), SQLT_CHR,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC))
{
    DISCARD printf("FAILED: OCIBindByPos()\n");
    report_error(errhp);
    return OCI_ERROR;
}
for (i = 0; i < MAXCOLS; i++)
    pos[i] = i+1;
if (OCIBindDynamic(bndhp[0], errhp, (dvoid *) (dvoid *) &pos[0],
        cbf_in_data,(dvoid *) 0, (OCICallbackOutBind) 0)
  ||  OCIBindDynamic(bndhp[1], errhp, (dvoid *) (dvoid *) &pos[1],
        cbf_in_data, (dvoid *) 0, (OCICallbackOutBind) 0))
{
    DISCARD printf("FAILED: OCIBindDynamic()\n");
    report_error(errhp);
    return OCI_ERROR;
}
OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (const OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT)
} /* end insert_data() */

/* Inbind callback to specify input data.                            */
STATICF sb4 cbf_in_data(ctxp, bindp, iter, index, bufpp, alenpp,
                                        piecep, indpp)
dvoid *ctxp;
OCIBind *bindp;
ub4 iter;
ub4 index;
dvoid **bufpp;
ub4 *alenpp;
ub1 *piecep;
dvoid **indpp;
{
  static int a = 0;
  word j;
  ub4  inpos = *((ub4 *)ctxp);
    switch(inpos)
    {
    case 1:
    buf1 = 175;
    *bufpp = (dvoid *) &buf1;
    *alenpp = sizeof(buf1);
    break;
    case 2:
    memset((void *)buf2, (int) `A'+a, (size_t) 5000);
    *bufpp = (dvoid *) buf2;
    *alenpp = 5000 ;
     a++;
    break;
    default: printf("ERROR: invalid position number: %d\n", pos);
    }
    *indpp = (dvoid *) 0;
      *piecep = OCI_ONE_PIECE;
      if (inpos == 2)
    {
    if (a<=1)
    {
      *piecep = OCI_FIRST_PIECE;
      printf("Insert callback: 1st piece\n");
    }
    else if (a<3)
    {
      *piecep = OCI_NEXT_PIECE;
      printf("Insert callback: %d'th piece\n", a);
    }
    else {
      *piecep = OCI_LAST_PIECE;
      printf("Insert callback: %d'th piece\n", a);
      a = 0;
    }
    }
    return OCI_CONTINUE;
}

Converting LONG to LOB Example 4: Array insert

The following example illustrates converting from LONG to LOBs when using an array INSERT:

word buflen;
word arrbuf1[5];
text arrbuf2[5][5000];
text *insstmt = (text *)
"INSERT INTO MULTIMEDIA_TAB(CLIP_ID, STORY) VALUES 
(:CLIP_ID, :STORY)";

if (OCIStmtPrepare(stmthp, errhp, insstmt, 
(ub4)strlen((char *)insstmt), (ub4) OCI_NTV_SYNTAX, 
(ub4) OCI_DEFAULT))
{
    DISCARD printf("FAILED: OCIStmtPrepare()\n");
    report_error(errhp);
    return;
}

if (OCIBindByName(stmthp, &bndhp[0], errhp,
  (text *) ":CLIP_ID", (sb4) strlen((char *) ":CLIP_ID"),
  (dvoid *) &arrbuf1[0], (sb4) sizeof(arrbuf1[0]), SQLT_INT,(dvoid *) 0, (ub2 *)
   0, (ub2 *) 0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  ||  OCIBindByName(stmthp, &bndhp[1], errhp,
  (text *) ":STORY", (sb4) strlen((char *) ":STORY"),
  (dvoid *) arrbuf2[0], (sb4) sizeof(arrbuf2[0]), SQLT_CHR,
  (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
{
    DISCARD printf("FAILED: OCIBindByName()\n");
    report_error(errhp);
    return;
}
OCIBindArrayOfStruct(bndhp[0], ERRH, sizeof(arrbuf1[0]), 
                indsk, rlsk, rcsk); 
OCIBindArrayOfStruct(bndhp[1], ERRH, sizeof(arrbuf2[0]), 
                indsk, rlsk, rcsk);
for (i=0; i<5; i++)
{
    arrbuf1[i] = 101+i;
    memset((void *)arrbuf2[i], (int)'A'+i, (size_t)5000);
}

if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 5, (ub4) 0,
                (const OCISnapshot*) 0, (OCISnapshot*) 0,
                (ub4) OCI_DEFAULT))
{
      DISCARD printf("FAILED: OCIStmtExecute()\n");
      report_error(errhp);
      return;
}

Converting LONG to LOB Example 5: Simple Fetch

The following example illustrates converting from LONG to LOBs when using a simple fetch:

word   i, buf1 = 0;
text   buf2[5000];

text *selstmt = (text *) "SELECT CLIP_ID, STORY FROM MULTIMEDIA_TAB 
                      ORDER BY CLIP_ID";
OCIStmtPrepare(stmthp, errhp, selstmt, (ub4)strlen((char *)selstmt),
                      (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0,
                      (const OCISnapshot*) 0, (OCISnapshot*) 0,
                      (ub4) OCI_DEFAULT);

while (retval == OCI_SUCCESS || retval == OCI_SUCCESS_WITH_INFO)
{
    OCIDefineByPos(stmthp, &defhp1, errhp, (ub4) 1, (dvoid *) &buf1,
                      (sb4) sizeof(buf1), (ub2) SQLT_INT, (dvoid *) 0,
                      (ub2 *) 0,(ub2 *) 0, (ub4) OCI_DEFAULT);
    OCIDefineByPos(stmthp, &defhp2, errhp, (ub4) 2, (dvoid *) buf2,
                      (sb4) sizeof(buf2), (ub2) SQLT_CHR, (dvoid *) 0,
                      (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT);
    retval = OCIStmtFetch(stmthp, errhp, (ub4) 1, 
                      (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
    if (retval == OCI_SUCCESS || retval == OCI_SUCCESS_WITH_INFO)
       DISCARD printf("buf1 = %d,  buf2 = %.*s\n", buf1, 30, buf2);
}

Converting LONG to LOB Example 6: Piecewise Fetch with Polling

The following example illustrates converting from LONG to LOBs when using a 
piecewise fetch with polling:
text *selstmt = (text *) "SELECT STORY FROM MULTIMEDIA_TAB 
                            ORDER BY CLIP_ID";
OCIStmtPrepare(stmthp, errhp, sqlstmt,
                  (ub4) strlen((char *)sqlstmt),
                  (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
OCIDefineByPos(stmthp, &dfnhp[1], errhp, (ub4) 1,
                  (dvoid *) NULL, (sb4) 100000, SQLT_LNG,
                  (dvoid *) 0, (ub2 *) 0,
                  (ub2 *) 0, (ub4) OCI_DYNAMIC_FETCH);
retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0,
                                (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                                    (ub4) OCI_DEFAULT);
retval = OCIStmtFetch(stmthp, errhp, (ub4) 1 ,
                                (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);

while (retval != OCI_NO_DATA && retval != OCI_SUCCESS)
{
    ub1    piece;
    ub4    iter, buflen;
    ub4    idx;
     genclr((void *)buf2, 5000);

    switch(retval)
    {
     case OCI_NEED_DATA:
        OCIStmtGetPieceInfo(stmthp, errhp, &hdlptr, &hdltype,
                              &in_out, &iter, &idx, &piece);
        OCIStmtSetPieceInfo(hdlptr, hdltype, errhp,
                                (dvoid *) buf2, &buflen, piece,
                                            (CONST dvoid *) &indp1, (ub2 *) 0));
        retval = OCI_NEED_DATA;
            break;
    default:
      DISCARD printf("ERROR: piece-wise fetching\n");
        return;
    } /* end switch */

    retval = OCIStmtFetch(stmthp, errhp, (ub4) 1 ,
                                (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
    printf("Data : %s\n"; buf2);

} /* end while */

Converting LONG to LOB Example 7: Piecewise Fetch with Callback

The following example illustrates converting from LONG to LOBs when using a piecewise fetch with callback:

select()
{
text *sqlstmt = (text *) "SELECT CLIP_ID, STORY FROM MULTIMEDIA_TAB";

OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
                        (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

OCIDefineByPos(stmthp, &dfnhp[0], errhp, (ub4) 1,
                    (dvoid *) 0, (sb4) sizeof(buf1), SQLT_INT,
                    (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                    (ub4) OCI_DYNAMIC_FETCH);
OCIDefineByPos(stmthp, &dfnhp[1], errhp, (ub4) 2,
                    (dvoid *) 0, (sb4)3 * sizeof(buf2), SQLT_CHR,
                    (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                    (ub4) OCI_DYNAMIC_FETCH);
OCIDefineDynamic(dfnhp[0], errhp, (dvoid *) &outpos,
                      (OCICallbackDefine) cbf_get_data);
OCIDefineDynamic(dfnhp[1], errhp, (dvoid *) &outpos2,
                      (OCICallbackDefine) cbf_get_data);
OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (const OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT);
buf2[ 4999 ] = `\0';
printf("Select callback: Last piece: %s\n", buf2);
}

/* -------------------------------------------------------------- */
/* Fetch callback to specify buffers.                          */
/* -------------------------------------------------------------- */
STATICF sb4 cbf_get_data(ctxp, dfnhp, iter, bufpp, alenpp, piecep,
                                indpp, rcpp)
dvoid *ctxp;
OCIDefine *dfnhp;
ub4 iter;
dvoid **bufpp;
ub4 **alenpp;
ub1 *piecep;
dvoid **indpp;
ub2 **rcpp;
{
  static int a = 0;
  ub4  outpos = *((ub4 *)ctxp);
  len = sizeof(buf1);
  len2 = 5000;

      switch(outpos)
    {
      case 1:
    *bufpp = (dvoid *) &buf1;
    *alenpp = &len;
    break;
      case 2:
    a ++;
    *bufpp = (dvoid *) buf2;
    *alenpp = &len2;
    break;
      default:
    *bufpp = (dvoid *) 0;
    *alenpp = (ub4 *) 0;
    DISCARD printf("ERROR: invalid position number: %d\n", pos);
  }

  *indpp = (dvoid *) 0;
  *rcpp = (ub2 *) 0;

  if (outpos == 1)
    *piecep = (ub1)OCI_ONE_PIECE;
  if (outpos == 2)
  {
    out2[len2] = `\0';
    if (a<=1)
    {
      *piecep = OCI_FIRST_PIECE;
        printf("Select callback: 0th piece\n");
    }
    else if (a<3)
    {
      *piecep = OCI_NEXT_PIECE;
      printf("Select callback: %d'th piece: %s\n", a-1, out2);
    }
    else {
      *piecep = OCI_LAST_PIECE;
      printf("Select callback: %d'th piece: %s\n", a-1, out2);
      a = 0;
    }
  }

  return OCI_CONTINUE;
}

Converting LONG to LOB Example 8: Array Fetch

The following example illustrates converting from LONG to LOBs when using an array fetch:

word   i;
word arrbuf1[5] = 0;
text   arrbuf2[5][5000];

text *selstmt = (text *) "SELECT CLIP_ID, STORY FROM MULTIMEDIA_TAB 
                    ORDER BY CLIP_ID";
OCIStmtPrepare(stmthp, errhp, selstmt, (ub4)strlen((char *)selstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0,
                    (const OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT);

while (retval == OCI_SUCCESS || retval == OCI_SUCCESS_WITH_INFO)
{
    OCIDefineByPos(stmthp, &defhp1, errhp, (ub4) 1, 
                    (dvoid *) &arrbuf1[0], (sb4) sizeof(arrbuf1[0]), 
                    (ub2) SQLT_INT, (dvoid *) 0,
                    (ub2 *) 0,(ub2 *) 0, (ub4) OCI_DEFAULT);
    OCIDefineByPos(stmthp, &defhp2, errhp, (ub4) 2,
                    (dvoid *) arrbuf2[0], (sb4) sizeof(arrbuf2[0]), 
                    (ub2) SQLT_CHR, (dvoid *) 0,
                    (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT);
    OCIDefineArrayOfStruct(dfnhp[0], ERRH, sizeof(arrbuf1[0]), indsk,
                     rlsk, rcsk);
    OCIDefineArrayOfStruct(dfnhp[1], ERRH, sizeof(arrbuf2[0]), indsk,
                     rlsk, rcsk);

    retval = OCIStmtFetch(stmthp, errhp, (ub4) 5, 
                     (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
    if (retval == OCI_SUCCESS || retval == OCI_SUCCESS_WITH_INFO)
    {
        DISCARD printf("%d, %s\n", arrbuf1[0], arrbuf2[0]);
        DISCARD printf("%d, %s\n", arrbuf1[1], arrbuf2[1]);
        DISCARD printf("%d, %s\n", arrbuf1[2], arrbuf2[2]);
        DISCARD printf("%d, %s\n", arrbuf1[3], arrbuf2[3]);
        DISCARD printf("%d, %s\n", arrbuf1[4], arrbuf2[4]);

    }
}

Converting LONG to LOB Example 9: Using PL/SQL in INSERT, UPDATE
and SELECT

INSERT/UPDATE statements on LOBs are used in the same way as on LONGs. For example:

BEGIN
  INSERT INTO Multimedia_tab VALUES (1, `A wonderful story', NULL, EMPTY_BLOB,
      EMPTY_BLOB(), NULL, NULL, NULL, NULL, NULL);
  UPDATE Multimedia_tab SET Story = `A wonderful story';
END;

LONG-to-LOB API enables SELECT statements to bind character variables to LOB columns.

BEGIN
story_buffer VARCHAR2(100);
/* This will get the LOB column if it is upto 100 bytes, otherwise it will 
raise an exception */
SELECT Story INTO story_buffer FROM Multimedia_tab WHERE Clip_ID = 1;
END;

Converting LONG to LOB Example 10: Assignments and Parameter Passing
in PL/SQL

The LONG-to-LOB API enables implicit assignments of LOBs to VARCHAR2s, RAWs,..., including parameter passing. For example:

CREATE TABLE t (clob_col CLOB, blob_col BLOB);
INSERT INTO t VALUES('abcdefg', 'aaaaaa');
DECLARE
    var_buf VARCHAR2(100);
    clob_buf CLOB;
    raw_buf RAW(100);
    blob_buf BLOB;
BEGIN
    SELECT * INTO clob_buf, blob_buf FROM t;
    var_buf := clob_buf;
    clob_buf:= var_buf;
    raw_buf := blob_buf;
    blob_buf := raw_buf;
  END;

CREATE PROCEDURE FOO ( a IN OUT CLOB) IS......

CREATE PROCEDURE BAR (b IN OUT VARCHAR2) IS .....
DECLARE
    a VARCHAR2(100) := '1234567';
    b CLOB;
BEGIN
    FOO(a);
    SELECT clob_col INTO b FROM t;
    BAR(b);
END;

Converting LONG to LOB Example 11: CLOBs in PL/SQL Built-In Functions

This example illustrates the use of CLOBs in PL/SQL built-in functions, when converting LONGs to LOBs:

DECLARE
   myStory CLOB;
   revisedStory CLOB;
   myGist VARCHAR2(100):= 'This is my gist.';
   revisedGist VARCHAR2(100);
BEGIN
   -- select a CLOB column into a CLOB variable
   SELECT Story INTO myStory FROM Multimedia_tab WHERE clip_id=10;

   -- perform VARCHAR2 operations on a CLOB variable
   revisedStory := UPPER(SUBSTR(myStory, 100, 1)); 

   -- revisedStory is a temporary LOB
   -- Concat a VARCHAR2 at the end of a CLOB
   revisedStory := revisedStory || myGist;
   -- The following statement will raise an error since myStory is 
   -- longer than 100 bytes
   myGist := myStory;
END;

Converting LONG to LOB Example 12: Using PL/SQL Binds from OCI on LOBs

The LONG-to-LOB API allows LOB PL/SQL binds from OCI to work as follows:

When you call a PL/SQL procedure from OCI, and have an in or out or inout bind, you should be able to bind a variable as SQLT_CHR, where the formal parameter of the PL/SQL procedure is SQLT_CLOB.

Note:

C procedures are wrapped inside a PL/SQL stub, so the OCI application always invokes the PL/SQL stub.

For the OCI calling program, the following are likely cases:

Calling PL/SQL Outbinds in the "begin foo(:1); end;" Manner

For example:

text *sqlstmt = (text *)"BEGIN PKG1.P5 (:c); END; " ;

Calling PL/SQL Outbinds in the "call foo(:1);" Manner

For example:

text *sqlstmt = (text *)"CALL PKG1.P5( :c );" ;

In both these cases, the rest of the program is as follows:

   OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
            (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   curlen = 0;

   OCIBindByName(stmthp, &bndhp[3], errhp,
            (text *) ":c4", (sb4) strlen((char *) ":c"),
            (dvoid *) buf5, (sb4) LONGLEN, SQLT_CHR,
            (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
            (ub4) 1, (ub4 *) &curlen, (ub4) OCI_DATA_AT_EXEC);

    OCIStmtExecute(svchp, stmthp, errhp,(ub4) 0,(ub4) 0, (const OCISnapshot*) 0,
    (OCISnapshot*) 0,(ub4) OCI_DEFAULT);

The PL/SQL procedure PKG1.P5 is as follows:

   CREATE OR REPLACE PACKAGE BODY pkg1 AS
     ...
   procedure p5 (c OUT CLOB) is
     -- This might have been table%rowtype (so it   is CLOB now)
   BEGIN
     ...
   END p5;

END pkg1;

Converting LONG to LOB Example 13: Calling PL/SQL and C Procedures
from PL/SQL

PL/SQL procedures or functions can accept a CLOB or a VARCHAR2 as a formal parameter. For example the PL/SQL procedure could be one of the following:

When the formal parameter is CLOB:

CREATE OR REPLACE PROCEDURE get_lob(table_name IN VARCHAR2, lob INOUT
CLOB) AS
   ...
BEGIN
  ...
END;
/

When the formal parameter is VARCHAR2:

CREATE OR REPLACE PROCEDURE get_lob(table_name IN VARCHAR2, lob INOUT
VARCHAR2) AS
   ...
BEGIN
  ...
END;
/

The calling function could be of any of the following types:

When the actual parameter is a CHR:

create procedure ...
declare 
c VARCHAR2[200];
begin
  get_lob('table_name', c);
end;

When the actual parameter is a CLOB:

create procedure ...
declare 
c table_name.column_name%type   -- This is a CLOB now
begin
  get_lob('table_name', c);
end;

Both the PL/SQL case stubs works with both cases of the actual parameters.

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" .

Compatibility and Migration

When you ALTER TABLE to change the LONG column to LOB, the table looks as if you never had the LONG column and always had the LOB column. Once you move all LONG data to LOBs, you cannot ALTER the table back to LONG.

Table 8-1 outlines the behavior of various client-server combinations in this release and prior to this release.

Table 8-1 Client - Server Combinations for Oracle9i and Prior to Oracle9i

Client	Oracle9i Release 9.0.0 Server	Servers Prior to Oracle9i
Rel.9.0.0 with CHARs	Server sends data	Client raises error.
Rel.9.0.0 with LOBs	Server sends locator	Server sends locator.
Prior to Rel.9.0.0 with CHARs	Client raises an error	Client raises error.
Prior to Rel.9.0.0 with LOBs	Server sends locator	Server sends locator.

Performance

INSERTS and Fetches have Comparable Performance

A piecewise INSERT or fetch of LOBs using the LONG-to-LOB API has comparable performance to the piecewise INSERT or fetch of LOBs using existing functions like OCILobRead() and OCILobWrite().

Since Oracle allows >4k data to be inserted into LOBs in a single OCI call, a round-trip to the server is saved.

Also, you can now read LOB data in one OCIStmtFetch() call, instead of fetching the LOB locator first and then doing OCILobRead(). This improves performance when you want to read LOB data starting at the beginning (since OCIStmtFetch() returns the data from offset 1). Hence the LONG-to-LOB API improves performance of LOB INSERTs and fetches.

PL/SQL

The performance of assigning a VARCHAR2 buffer to a LOB variable is worse than the performance of the corresponding assignment of VARCHAR2 to the LONG variable because the former involves creating temporary LOBs. Hence PL/SQL users will see a silent deterioration in the performance of their applications.

Frequently Asked Questions (FAQs): LONG to LOB Migration

Moving From LOBs Back to LONGs

Question

Once we ALTER a table to change LONG columns to LOB and consequently move all LONG data to LOBs, we cannot ALTER the column back to LONG. Is there a work around?

Answer

There is a workaround for this. You can add a LONG column and use an OCI application to read the data from the LOB column and insert it into the LONG column. Then you can drop the LOB column.

Is CREATE VIEW Needed?

Question

Is CREATE VIEW still needed when migrating from LONGs to LOBs?

Answer

No, you no longer need to use CREATE VIEW. Use the ALTER TABLE statement.

Are OCI LOB Routines Obsolete?

Question

How does OCIStmtFetch() work for LOB columns? Does it return OCI_NEED_DATA as it previously did for LONG column and must data be completely fetched before the data for other columns is available? Are all OCI routines for LOBs obsolete, such as, OCILobRead(),OCILobWrite(),...?

Answer

OCIStmtFetch() for LOBs works the same way as it did for LONGs previously if the datatype is specified as SQLT_LNG/SQLT_CHR,... in the define. If the datatype is specified as SQLT_CLOB or SQLT_BLOB, then the OCIStmtFetch() call fetches the LOB locator and you can call OCILobRead() to read LOB data. OCI LOB calls will not be obsoleted.

If the datatype is SQLT_LNG/SQLT_CHR,... for a LOB column, then the LOB data needs to be completely fetched before the data for other columns are available. The way SQL*PLUS can get around this problem is to continue using the existing OCI LOB interface.

PL/SQL Issues

Question

Does a fetch of a LOB column (with size > 32K) into a PL/SQL CHAR/RAW/LONG/LONG RAW buffer raise an exception?

Answer

In OCIDefineByPos() and PL/SQL "SELECT INTO" commands, there is no way of specifying the "amount" wanted. You only specify the buffer length. The correct amount is fetched without overflowing the buffer, no matter what the LOB size is. If the whole column is not fetched, then in OCI a truncation error is returned, and in PL/SQL, an exception is raised.

This behavior is consistent with the existing behavior for LONGs and VARCHAR2s.

Retrieving an Entire Image if Less Than 32K

Question

I can now SELECT LOB data without first retrieving the locator. Can I now retrieve an entire image with a single SELECT in PL/SQL if the image is less than 32K?

Answer

Yes.

Triggers in LONGs and LOBs

Question

In Triggers, some functionality is supported for LONGs that is not supported for LOBs. Will this cause a problem?

Answer

There are a couple of limitations on how LOBs work with triggers. See "LONG-to-LOB Migration Limitations" .

8 Migrating From LONGs to LOBs

Introducing LONG-to-LOB Migration

Using the LONG-to-LOB API Results in an Easy Migration

Guidelines for Using LONG-to-LOB API

Using ALTER TABLE

LONG-to-LOB API and OCI

Binds in OCI

Defines in OCI

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

Multibyte Charactersets (OCI)

LONG-to-LOB API and PL/SQL

INSERT and UPDATE of LOB Columns (PL/SQL)

SELECT on a LOB Column (PL/SQL)

Assignment and Parameters Passing (PL/SQL)

Migrating Existing Tables from LONG to LOB

Migrating LONGs to LOBs: Using ALTER TABLE to Change LONG Column to LOB Types

All Constraints of LONG Column are Maintained

Default Values for LONG are Copied to LOB

Most Triggers Remain Valid

Indexes Must be Rebuilt -- Use ALTER INDEX...REBUILD

Rebuilding Indexes After a LONG to LOB Migration

Space Requirements are Temporarily Doubled

LOGGING

LONG-to-LOB Migration Limitations

Clustered Tables

Replication

Triggers

Indexes

LONGs, LOBs, and NULLs

NULL LONGs Versus Zero Length LONGs

NULL LOBs Versus Zero Length LOBs

Using LONG-to-LOB API with OCI

Guidelines for Using LONG-to-LOB API for LOBs with OCI

Using OCI Functions to Perform INSERT or UPDATE on LOBs

Simple INSERTs or UPDATEs in One Piece

Using Piecewise INSERTs and UPDATEs with Polling

Piecewise INSERTs and UPDATEs with Callback

Array INSERTs and UPDATEs

Using OCI Functions to Perform FETCH on LOBs

Simple Fetch in One Piece

Piecewise Fetch with Polling

Piecewise with Callback

Array Fetch

Using SQL and PL/SQL to Access LONGs and LOBs

Using SQL and PL/SQL to Access LOBs

INSERTs

UPDATEs

Restriction for LONG RAW and RAW Buffers More Than 4000 Bytes.

SELECTs

Implicit Assignment and Parameter Passing

Variable Assignment Between CLOB/CHAR and BLOB/RAW

Function/Procedure Parameter Passing

Explicit Conversion Functions

VARCHAR2 and CLOB in PL/SQL Built-In Functions

PL/SQL and C Binds from OCI

Calling PL/SQL Outbinds in the "begin foo(:1); end;" Manner.

Calling PL/SQL Outbinds in the "call foo(:1);" Manner.

Calling PL/SQL and C Procedures from SQL or PL/SQL

From SQL

From PL/SQL

Applications Requiring Changes When Converting From LONGs to LOBs

Overloading with Anchored Types

Implicit Conversion of NUMBER, DATE, ROW_ID, BINARY_INTEGER, and PLS_INTEGER to LOB is Not Supported

No Implicit Conversions of BLOB to VARCHAR2, CHAR, or CLOB to RAW or LONG RAW

Using utldtree.sql to Determine Where Your Application Needs Change

Examples of Converting from LONG to LOB Using Table Multimedia_tab

To Convert LONG to CLOB, Use ALTER TABLE

Converting LONG to LOB Example 1: More than 4K Binds and Simple INSERTs

Converting LONG to LOB Example 2: Piecewise INSERT with Polling

Converting LONG to LOB Example 3: Piecewise INSERT with Callback

Converting LONG to LOB Example 4: Array insert

Converting LONG to LOB Example 5: Simple Fetch

Converting LONG to LOB Example 6: Piecewise Fetch with Polling

Converting LONG to LOB Example 7: Piecewise Fetch with Callback

Converting LONG to LOB Example 8: Array Fetch

Converting LONG to LOB Example 9: Using PL/SQL in INSERT, UPDATE and SELECT

Converting LONG to LOB Example 10: Assignments and Parameter Passing in PL/SQL

Converting LONG to LOB Example 11: CLOBs in PL/SQL Built-In Functions

Converting LONG to LOB Example 12: Using PL/SQL Binds from OCI on LOBs

Calling PL/SQL Outbinds in the "begin foo(:1); end;" Manner

8
Migrating From LONGs to LOBs

Migrating LONGs to LOBs: Using ALTER TABLE to Change
LONG Column to LOB Types

Implicit Conversion of NUMBER, DATE, ROW_ID, BINARY_INTEGER,
and PLS_INTEGER to LOB is Not Supported

No Implicit Conversions of BLOB to VARCHAR2, CHAR, or CLOB to RAW
or LONG RAW

Converting LONG to LOB Example 9: Using PL/SQL in INSERT, UPDATE
and SELECT

Converting LONG to LOB Example 10: Assignments and Parameter Passing
in PL/SQL

Converting LONG to LOB Example 13: Calling PL/SQL and C Procedures
from PL/SQL