Using SQL and PL/SQL to Access LONGs and LOBs

This section describes the following topics:

• Using SQL and PL/SQL to Access LOBs

• Implicit Assignment and Parameter Passing

• PL/SQL and C Binds from OCI

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

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 and/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.

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:

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