|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Modeling and Design, 13 of 21
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.
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.
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.
SQL operators/functions that previously returned VARCHAR2s, now either return a CLOB or a VARCHAR2, depending on the input parameter type.
Operators/functions continue to return VARCHAR2s when only VARCHAR2s are passed in as arguments. A function with only VARCHAR2 parameters never returns a CLOB.
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 ...;
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:
DBMS_LOB.FREETEMPORARY()call to release the resources taken by the local temporary LOBs.
Alternatively, if any of the following transpire:
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.
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
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.
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:
This can be misleading! Note of this semantic discrepancy.