Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)

Part Number A88879-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to beginning of chapter Go to next page

Migrating From LONGs to LOBs, 7 of 14

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:


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.


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.


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:


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
   a VARCHAR2(100);
   b t.long_col%type; -- This variable changes from LONG to CLOB
   a := b;  -- This changes from "VARCHAR2 := LONG to VARCHAR2 := CLOB
   b := a;  -- This changes from "LONG := VARCHAR2 to CLOB := VARCHAR2 

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......
    a VARCHAR2(100);
    b t.long_col%type  -- This changes to CLOB
    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

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_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 and/or give CLOB output are:

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.

See Also:

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

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:

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 
  ... /* The procedure body could be in PL/SQL or C*/

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.


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.

Go to previous page Go to beginning of chapter Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index