SQL Semantics Support for LOBs

This section describes the following topics:

• How SQL VARCHAR2/RAW Semantics Apply to CLOBs/BLOBs

• SQL RAW Type and BLOBs

• SQL DML Changes For LOBs

• SQL DML Changes For LOBs

• SQL Functions/Operators for VARCHAR2s/RAWs and CLOBs/BLOBs

• PL/SQL Statements and Variables: New Semantics Changes

• PL/SQL CLOB Comparison Rules

In prior releases, you could only access LOBs stored in the database using LOB locators through a set of APIs in various language interfaces (C, C++, OO4O, Java, COBOL, PL/SQL). LOBs could not be used in SQL character functions.

Improved LOB Usability: You can Now Access LOBs Using SQL "Character" Functions

In Oracle9i, for the first time, you can access LOBs using SQL VARCHAR2 semantics, such as SQL string operators and functions.

By providing you with an SQL interface, which you are familiar with, accessing LOB data can be greatly facilitated. You can benefit from this added functionality in the following two cases:

• When using small-sized LOBs (~ 10-100K) to store data through the APIs and you need SQL support on LOBs.

• When you have just migrated your LONG columns to LOBs. In this release, you can take advantage of an easier migration process using the LONG-to-LOB migration API described in Chapter 8, "Migrating From LONGs to LOBs".

Advanced LOB users who need to take advantage of features such as random access and piecewise fetch, should continue using existing LOB API interfaces.

For users of medium-to-large sized (> 1M) LOBs, this SQL interface is not advised due to possible performance issues.

This description is limited to internal persistent LOBs only. This release, does not offer SQL support on BFILEs.

Note: SQL Semantics Support has no impact on current usage of LOBs. Existing LOB applications, using LOB APIs, do not need to be changed.

SQL and PL/SQL VARCHAR2 Functions/Operators Now Allowed for CLOBs

The following SQL VARCHAR2 functions and operators are now allowed for CLOBs, as indicated in Table 7-6:

• INSTR related operators/functions
  • INSTR() and variants (See Table 7-7)

  • LIKE

  • REPLACE()

• CONCAT and ||

• LENGTH() and variants (See Table 7-7)

• SUBSTR() and variants (See Table 7-7)

• TRIM(), LTRIM() and RTRIM()

• LOWER(), UPPER(), NLS_LOWER(), NLS_UPPER()

• LPAD() and RPAD()

PL/SQL Relational Operators Now Allowed for LOBs

For LONG to LOB migration, the following relational operators in PL/SQL now work on LONGs and LOBs:

• Operators: >, <, =, !=,

• IN, BETWEEN

• GREATEST and LEAST

• NLSSORT

These operators are also listed in Table 7-6.

SQL and PL/SQL CHAR to CLOB Conversion Functions

The following CHAR to CLOB conversion functions are now allowed for LOBs:

• TO_CHAR() and TO_NCHAR() converts CLOB or NCLOB to CHAR or NCHAR

• TO_CLOB() and TO_NCLOB() converts CHAR or NCHAR to CLOB or NCLOB

Non-Supported SQL Functionality for LOBs

The following SQL functionality is not supported for LOBs because the functions are either infrequently used or have easy workarounds.

• INDEX on LOB column: Workaround, use Oracle9i Text (interMedia Text)

• SQL Conversion functions: TO_DATE, TO_NUMBER, TO_TIMESTAMP, CHARTOROWID, TO_MULTI_BYTE, TO_SINGLE_BYTE - not frequently used

• Other SQL functions: GROUPING - not frequently used.

• Comparison functions/operators, including the following:
  • Operators: >, <, =, !=

  • IN, SOME, ANY, ALL, BETWEEN

  • MAX, MIN, GREATEST and LEAST

  • SELECT DISTINCT, GROUP BY, ORDER BY (SORT), UNION, INTERSECT, MINUS

  • JOIN

  • Miscellaneous SQL functions: INITCAP, NLS_INITCAP, DUMP, TRANSLATE, VSIZE, DECODE

Using SQL Functions and Operators for VARCHAR2s on CLOBs

Table 7-6, lists all SQL operators and functions that take a VARCHAR2 as operands/arguments, or return a VARCHAR2 value. With the only exception of the "IS [NOT] NULL" operator, none of the operators/functions in prior releases work on CLOBs.

In Table 7-6, the SQL operators/functions supported on CLOBs in Oracle9i, are indicated in the 4th "SQL" column.

Most functions listed in Table 7-6 also apply to PL/SQL built-in functions (supplied packages). The 5th "PL/SQL" column indicates the availability of the operator/function on CLOBs in PL/SQL.

Implicit conversions between CLOBs and CHAR types are enabled in Oracle9i. Therefore, functions not yet enabled for CLOBs can still accept CLOBs through implicit conversion. In this case, CLOBs are converted to a CHAR or a VARCHAR2 before the function is invoked. If the CLOB is greater than 4K bytes in size, only 4000 bytes will be converted into CHARs or VARCHAR2s.

In Table 7-6, the functions which take CLOB parameters through implicit conversions, are denoted as "CNV".

Table 7-6 SQL VARCHAR2 Functions/Operators  

Category	Operator / Function	SQL Example for CLOB Columns	SQL	PL/SQL
OPERATORS
Concat	||, CONCAT()	Select clobCol || clobCol2 from tab;	Yes	Yes
Comparison	= , !=, >, >=, <, <=, <>, ^=	...where clobCol=clobCol2	No	Yes
	IN, NOT IN	...where clobCol NOT IN (clob1, clob2, clob3);	No	Yes
	SOME, ANY, ALL	...where clobCol < SOME (select clobCol2 from...)	No	N/A
	BETWEEN	...where clobCol BETWEEN clobCol2 and clobCol3	No	Yes
	LIKE [ESCAPE] and its variants. See Table 7-7.	...where clobCol LIKE `%pattern%'	Yes	Yes
	IS [NOT] NULL	...where clobCol IS NOT NULL	Yes	Yes
FUNCTIONS
Character functions	INITCAP, NLS_INITCAP	select INITCAP(clobCol) from...	CNV	CNV
	LOWER, NLS_LOWER, UPPER, NLS_UPPER	...where LOWER(clobCol1) = LOWER(clobCol2)	Yes	Yes
	LPAD, RPAD	select RPAD(clobCol, 20, ' La') from...	Yes	Yes
	TRIM, LTRIM, RTRIM	...where RTRIM(LTRIM(clobCol,'ab'), `xy') = `cd'	Yes	Yes
	REPLACE	select REPLACE(clobCol, `orig','new') from...	Yes	Yes
	SOUNDEX	...where SOUNDEX(clobCOl) = SOUNDEX(`SMYTHE')	CNV	CNV
	SUBSTR and its variants (Table 7-7)	...where substr(clobCol, 1,4) = `THIS'	Yes	Yes
	TRANSLATE	select TRANSLATE(clobCol, `123abc','NC') from...	CNV	CNV
	ASCII	select ASCII(clobCol) from...	CNV	CNV
	INSTR and its variants (Table 7-7)	...where instr(clobCol, `book') = 11	Yes	Yes
	LENGTH and its variants (Table 2-2)	...where length(clobCol) != 7;	Yes	Yes
	NLSSORT	...where NLSSORT (clobCol,'NLS_SORT = German') > NLSSORT ('S','NLS_SORT = German')	CNV	CNV
Conversion functions	CHARTOROWID	CHARTOROWID(clobCol)	CNV	CNV
	HEXTORAW	HEXTORAW(CLOB)	No	CNV
	CONVERT	select CONVERT(clobCol,'WE8DEC','WE8HP') from...	Yes	CNV
	TO_DATE	TO_DATE(clobCol)	CNV	CNV
	TO_NUMBER	TO_NUMBER(clobCol)	CNV	CNV
	TO_TIMESTAMP	TO_TIMESTAMP(clobCol)	No	CNV
	TO_MULTI_BYTE TO_SINGLE_BYTE	TO_MULTI_BYTE(clobCol) TO_SINGLE_BYTE(clobCol)	CNV	CNV
	TO_CHAR	TO_CHAR(clobCol)	Yes	Yes
	TO_NCHAR	TO_NCHAR(clobCol)	Yes	Yes
	TO_LOB	INSERT INTO... SELECT TO_LOB(longCol)... Note: TO_LOB can only be used to create or insert into a table with LOB columns as SELECT FROM a table with a LONG column.	N/A	N/A
	TO_CLOB	TO_CLOB(varchar2Col)	Yes	Yes
	TO_NCLOB	TO_NCLOB(varchar2Clob)	Yes	Yes
Aggreate Functions	COUNT	select count(clobCol) from...	No	N/A
	MAX, MIN	select MAX(clobCol) from...	No	N/A
	GROUPING	select grouping(clobCol) from... group by cube (clobCol);	No	N/A
OTHER FUNCTIONS	GREATEST, LEAST	select GREATEST (clobCol1, clobCol2) from...	No	Yes
	DECODE	select DECODE(clobCol, condition1, value1, defaultValue) from...	CNV	CNV
	NVL	select NVL(clobCol,'NULL') from...	Yes	Yes
	DUMP	select DUMP(clobCol) from...	No	N/A
	VSIZE	select VSIZE(clobCol) from...	No	N/A

See Also: Oracle9i SQL Reference, Chapter 6, "Functions".

UNICODE Support for VARCHAR2 and CLOB

In this release, database UNICODE support for VARCHAR2s [unicode] provides a few UNICODE variants on functions INSTR, SUBSTR, LENGTH, and LIKE. Table 7-7 summarizes them.

See Also: Oracle9i Supplied PL/SQL Packages Reference Oracle9i Application Developer's Guide - Fundamentals Oracle9i SQL Reference Oracle9i Globalization Support Guide for a detailed description on the usage of new UNICODE functions.

Table 7-7 Unicode Related SQL Functions (CLOB=CLOB Support)

SQL Functions	Comments	CLOB
INSTRB, SUBSTRB, LENGTHB	Byte-based functions, existed in prior in Oracle9i releases	Yes
INSTR2, SUBSTR2, LENGTH2, LIKE2	UCS2 character set based, provided in this release [NEW]	Yes
INSTR4, SUBSTR4, LENGTH4, LIKE4	UCS4 character set based, provided in this release [NEW]	Yes
INSTRC, SUBSTRC, LENGTHC, LIKEC	Character based, provided in this release [NEW]	Yes

SQL Features Where LOBs Cannot be Used

Table 7-8 lists other SQL features where LOBs cannot be used. Refer to the "LOB Restrictions" section in Chapter 4, "Managing LOBs", for further details.

Table 7-8 SQL Features Where LOBs Cannot be Used  

SQL Feature	Example for CLOB Columns
SELECT DISTINCT	SELECT DISTINCT clobCol from...
SELECT clause
ORDER BY	SELECT... ORDER BY clobCol
GROUP BY	SELECT avg(num) FROM... GROUP BY clobCol
UNION, INTERSECT, MINUS Note: UNION ALL works for LOBs	SELECT clobCol1 from tab1 UNION SELECT clobCol2 from tab2;
JOIN	SELECT... FROM... WHERE tab1.clobCol = tab2.clobCol
INDEX	CREATE INDEX clobIndx ON tab(clobCol)...