16 SQL Semantics and LOBs

UNICODE Support

Variations on the INSTR, SUBSTR, LENGTH, and LIKE functions are provided for Unicode support. (These variations are indicated as "Unicode" in the "Category" column of Table 16-1.)

Codepoint Semantics

Codepoint semantics of the INSTR, SUBSTR, LENGTH, and LIKE functions, described in Table 16-1, differ depending on the data type of the argument passed to the function. These functions use different codepoint semantics depending on whether the argument is a VARCHAR2 or a CLOB type as follows:

• When the argument is a CLOB, UCS2 codepoint semantics are used for all character sets.

• When the argument is a character type, such as VARCHAR2, the default codepoint semantics are used for the given character set:

  • UCS2 codepoint semantics are used for AL16UTF16 and UTF8 character sets.

  • UCS4 codepoint semantics are used for all other character sets, such as AL32UTF8.

• If you are storing character data in a CLOB or NCLOB, then note that the amount and offset parameters for any APIs that read or write data to the CLOB or NCLOB are specified in UCS2 codepoints. In some character sets, a full character consists one or more UCS2 codepoints called a surrogate pair. In this scenario, you must ensure that the amount or offset you specify does not cut into a full character. This avoids reading or writing a partial character.

• Starting from 10g, Oracle Database helps to detect half surrogate pair on read/write boundaries in such scenarios. In the case of read, the offset and amount is adjusted accordingly to avoid returning a half character, in which case the amount returned could be less than what is asked for. In the case of write, an error is raised to prevent from corrupting the existing data caused by overwriting a partial character in the destination CLOB or NCLOB.

Return Values for SQL Semantics on LOBs

The return type of a function or operator that takes a LOB or VARCHAR2 is the same as the data type of the argument passed to the function or operator.

Functions that take more than one argument, such as CONCAT, return a LOB data type if one or more arguments is a LOB. For example, CONCAT(CLOB, VARCHAR2) returns a CLOB.

A LOB instance is always accessed and manipulated through a LOB locator. This is also true for return values: SQL functions and operators return a LOB locator when the return value is a LOB instance.

Any LOB instance returned by a SQL function is a temporary LOB instance. LOB instances in tables (persistent LOBs) are not modified by SQL functions, even when the function is used in the SELECT list of a query.

LENGTH Return Value for LOBs

The return value of the LENGTH function differs depending on whether the argument passed is a LOB or a character string:

• If the input is a character string of length zero, then LENGTH returns NULL.

• For a CLOB of length zero, or an empty locator such as that returned by EMPTY_CLOB(), the LENGTH and DBMS_LOB.GETLENGTH functions return FALSE.

Implicit Conversion Between CLOB and NCLOB Data Types in SQL

The database enables you to perform operations such as cross-type assignment and cross-type parameter passing between CLOB and NCLOB data types. The database performs implicit conversions between these types when necessary to preserve properties such as character set formatting.

Note that, when implicit conversions occur, each character in the source LOB is changed to the character set of the destination LOB, if needed. In this situation, some degradation of performance may occur if the data size is large. When the character set of the destination and the source are the same, there is no degradation of performance.

After an implicit conversion between CLOB and NCLOB types, the destination LOB is implicitly created as a temporary LOB. This new temporary LOB is independent from the source LOB. If the implicit conversion occurs as part of a define operation in a SELECT statement, then any modifications to the destination LOB do not affect the persistent LOB in the table that the LOB was selected from as shown in the following example:

SQL> -- check lob length before update 
SQL> select dbms_lob.getlength(ad_sourcetext) from Print_media 
  2       where product_id=3106 and ad_id = 13001; 

DBMS_LOB.GETLENGTH(AD_SOURCETEXT) 
--------------------------------- 
         205 

SQL> 
SQL> declare 
  2   clob1 clob; 
  3   amt number:=10; 
  4  BEGIN 
  5    -- select a clob column into a clob, no implicit convesion 
  6    SELECT ad_sourcetext INTO clob1 FROM Print_media 
  7      WHERE product_id=3106 and ad_id=13001 FOR UPDATE; 
  8 
  9    dbms_lob.trim(clob1, amt); -- Trim the selected lob to 10 bytes 
 10  END; 
 11  / 

PL/SQL procedure successfully completed. 

SQL> -- Modification is performed on clob1 which points to the 
SQL> -- clob column in the table 
SQL> select dbms_lob.getlength(ad_sourcetext) from Print_media 
  2       where product_id=3106 and ad_id = 13001; 

DBMS_LOB.GETLENGTH(AD_SOURCETEXT) 
--------------------------------- 
          10 

SQL> 
SQL> rollback; 

Rollback complete. 

SQL> -- check lob length before update 
SQL> select dbms_lob.getlength(ad_sourcetext) from Print_media 
  2       where product_id=3106 and ad_id = 13001; 

DBMS_LOB.GETLENGTH(AD_SOURCETEXT) 
--------------------------------- 
         205 

SQL> 
SQL> declare 
  2   nclob1 nclob; 
  3   amt number:=10; 
  4  BEGIN 
  5 
  6    -- select a clob column into a nclob, implicit conversion occurs 
  7    SELECT ad_sourcetext INTO nclob1 FROM Print_media 
  8      WHERE product_id=3106 and ad_id=13001 FOR UPDATE; 
  9 
 10    dbms_lob.trim(nclob1, amt); -- Trim the selected lob to 10 bytes 
 11  END; 
 12  / 

PL/SQL procedure successfully completed. 

SQL> -- Modification to nclob1 does not affect the clob in the table, 
SQL> -- because nclob1 is a independent temporary LOB 

SQL> select dbms_lob.getlength(ad_sourcetext) from Print_media 
  2       where product_id=3106 and ad_id = 13001; 

DBMS_LOB.GETLENGTH(AD_SOURCETEXT) 
--------------------------------- 
         205 
  

LOBs Returned from SQL Functions

When a LOB is returned from a SQL function, the result returned is a temporary LOB. Your application should view the temporary LOB as local storage for the data returned from the SELECT operation as follows:

• 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 goes out of scope at the end of the program block at which time, the LOB is freed. These are the same semantics as those for PL/SQL VARCHAR2 variables. At any time, nonetheless, you can use a DBMS_LOB.FREETEMPORARY() call to release the resources taken by the local temporary LOBs.

  Note:

  If the SQL statement returns a LOB or a LOB is an OUT parameter for a PL/SQL function or procedure, you must test if it is a temporary LOB, and if it is, then free it after you are done with it.

• 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 are in the user-defined duration.

  Caution:

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

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 NULL and IS NOT NULL Usage with VARCHAR2s and CLOBs

You can use the IS NULL and IS NOT NULL operators with LOB columns. When used with LOBs, these operators determine whether a LOB locator is stored in the row.

WHERE Clause Usage with LOBs

SQL functions with LOBs as arguments, except functions that compare LOB values, are allowed in predicates of the WHERE clause. For example, the LENGTH function can be included in the predicate of the WHERE clause:

CREATE TABLE t (n NUMBER, c CLOB);
INSERT INTO t VALUES (1, 'abc');

SELECT * FROM t WHERE c IS NOT NULL;
SELECT * FROM t WHERE LENGTH(c) > 0;
SELECT * FROM t WHERE c LIKE '%a%';
SELECT * FROM t WHERE SUBSTR(c, 1, 2) LIKE '%b%';
SELECT * FROM t WHERE INSTR(c, 'b') = 2;