How SQL VARCHAR2/RAW Semantics Apply to CLOBs/BLOBs

Defining CHAR Buffer on CLOB

With Oracle9i, you can retrieve data from LOBs directly using SQL without using any special LOB API.

In PL/SQL, you can define a VARCHAR2 for a CLOB and RAW for a BLOB column. You can also define CLOBs/BLOBs for VARCHAR2/RAW columns.

Selecting a CLOB Column into a CHAR Buffer or CLOB

In PL/SQL, if a CLOB column is selected into a local VARCHAR2 variable, data stored in the CLOB column is retrieved and put into the CHAR buffer. If the buffer is not large enough to contain all the CLOB data a truncation error is raised and no data is written to the buffer. After the SELECT, the VARCHAR2 variable behaves the same as a regular character buffer.

In contrast, when a CLOB column is selected into a local CLOB variable, the CLOB locator is fetched. PL/SQL built-in functions that previously took only VARCHAR2s are now enabled to also take CLOB locators as arguments. The return type of the functions is CLOB if the primary argument is a CLOB. At the same time, the CLOB local variable can behave as a LOB locator when passed to DBMS_LOB APIs.

The above statement also applies to RAWs and BLOBs.

Accepting CLOBs in VARCHAR2 Operators/Functions

SQL operators/functions that currently take VARCHAR2 columns as operands or arguments are now enabled to accept CLOB columns. Previously, in Oracle8i, comparison of LOBs was not allowed, except for comparing LOB functions and the `IS [NOT] NULL' operator on LOBs. In this release, comparison of LOBs themselves in PL/SQL is allowed, while comparison in SQL queries is not yet available for performance concerns.

Returning CLOB Values from SQL Functions/Operators

SQL operators/functions that previously returned VARCHAR2s, now either return a CLOB or a VARCHAR2, depending on the input parameter type.

Returning VARCHAR2s

Operators/functions continue to return VARCHAR2s when only VARCHAR2s are passed in as arguments. A function with only VARCHAR2 parameters never returns a CLOB.

Returning CLOBs

Operators/functions return CLOBs when the primary argument, usually the first parameter is passed in as CLOBs. For example, the following SQL statements select out results as CLOB types:

SELECT SUBSTR(clobCol, 1,4) FROM .... WHERE LENGTH(clobCol)>4;
SELECT clobCol1 || charCol1 FROM ...;

Note: For functions like CONCAT(),||, which do not have a well-defined primary argument, if any parameter is a LOB, the function returns a LOB.

Returned LOB is a Temporary LOB Locator

When a LOB is returned, the result from the select list is in the form of a temporary LOB locator. Your application should view the temporary LOB as local storage for the CHAR string returned from the SELECT. In PL/SQL, the temporary LOB has the same lifetime (duration) as other local PL/SQL program variables. It can be passed to subsequent SQL or PL/SQL VARCHAR2 functions or queries:

• As a PL/SQL local variable, the temporary LOB will go out of scope at the end of the residing program block and then the LOB data will automatically be freed. This is the same behavior as other PL/SQL VARCHAR2 variables. At any time, nonetheless, you can issue a DBMS_LOB.FREETEMPORARY() call to release the resources taken by the local temporary LOBs.

• In OCI, the temporary LOBs returned from SQL queries are always in 'session' duration, unless a user-defined duration is present, in which case, the temporary LOBs will be in the user-defined duration.

  ALERT: Ensure that your temporary tablespace is large enough to store all temporary LOB results returned from queries in your program(s).

Alternatively, if any of the following transpire:

• You select out a CLOB column into a VARCHAR2

• A function that returns CLOB is put into a VARCHAR2 buffer

the returned result is a regular CHAR buffer with the declared size. If the VARCHAR2 buffer is not large enough to fit the data from the LOB, a truncation error is raised.

SQL Query Example 1: Using SQL to SELECT out a CLOB into a VARCHAR2

The following example illustrates selecting out a CLOB column into a VARCHAR2 and returning the result as a CHAR buffer of declared size:

DECLARE
  vc1 VARCHAR2(32000);
  lb1 CLOB;
  lb2 CLOB;
BEGIN
  SELECT clobCol1 INTO vc1 FROM tab WHERE colID=1;
  -- lb1 is a temporary LOB
  SELECT clobCol2 || clobCol3 INTO lb1 FROM tab WHERE colID=2;

  lb2 := vc1|| lb1;
  -- lb2 is a still temporary LOB, so the persistent data in the database 
  -- is not modified. An update is necessary to modify the table data.
  UPDATE tab SET clobCol1 = lb2 WHERE colID = 1;
  
DBMS_LOB.FREETEMPORARY(lb2); -- Free up the space taken by lb2


<... some more queries ...>



END; -- at the end of the block, lb1 is automatically freed 

IS [NOT] NULL in VARCHAR2s and CLOBs

For LOB columns, operator "IS [NOT] NULL" has been allowed since Oracle8. It checks if there is a LOB locator stored in the table row.

For VARCHAR2 columns, operator "IS NULL" indicates an empty string, or a null string.

Note: IS NULL Semantic Discrepancy In the SQL 92 standard, a character string of length zero is distinct from a null string. For an initialized LOB of length 0, you should expect `IS NULL' to return zero (FALSE), since it is the correct and standard compliant behavior. In contrast, a VARCHAR2 of length 0 returns TRUE on 'IS NULL`. In addition, for the LENGTH() function: If the input is a character string of zero length, LENGTH() returns NULL. For a CLOB of zero length, an EMPTY_CLOB(), zero is returned by LENGTH and DBMS_LOB.GETLENGTH() in SQL and PL/SQL. This can be misleading! Note of this semantic discrepancy.