Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
Modeling and Design, 12 of 21
This section describes the following topics:
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.
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:
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.
The following SQL VARCHAR2 functions and operators are now allowed for CLOBs, as indicated in Table 7-6:
For LONG to LOB migration, the following relational operators in PL/SQL now work on LONGs and LOBs:
These operators are also listed in Table 7-6.
The following CHAR to CLOB conversion functions are now allowed for LOBs:
The following SQL functionality is not supported for LOBs because the functions are either infrequently used or have easy workarounds.
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".
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_SINGLE_BYTE |
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 |
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:
for a detailed description on the usage of new UNICODE functions. |
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.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|