Oracle® Database Application Developer's Guide - Large Objects 10g Release 1 (10.1) Part Number B10796-01 |
|
|
View PDF |
This chapter describes SQL semantics that are supported for LOBs. These techniques allow you to use LOBs directly in SQL code and provide an alternative to using LOB-specific APIs for some operations.
This chapter covers the following topics:
You can access CLOB and NCLOB datatypes using SQL VARCHAR2 semantics, such as SQL string operators and functions. (LENGTH functions can be used with BLOB datatypes as well as CLOB and NCLOBs.) These techniques are beneficial in the following situations:
SQL semantics are not recommended in the following situations:
Many SQL operators and functions that take VARCHAR2 columns as arguments also accept LOB columns. The following list summarizes which categories of SQL functions and operators are supported for use with LOBs. Details on individual functions and operators are given in Table 9-1.
The following categories of SQL functions and operators are supported for use with LOBs:
The following categories of functions are not supported for use with LOBs:
Details on individual functions and operators are given in Table 9-1. This table lists SQL operators and functions that take VARCHAR2 types as operands or arguments, or return a VARCHAR2 value, and indicates in the "SQL" column which functions and operators are supported for CLOB and NCLOB datatypes. (The LENGTH function is also supported for the BLOB datatype.)
The DBMS_LOB PL/SQL package supplied with Oracle Database supports using LOBs with most of the functions listed in Table 9-1 as indicated in the "PL/SQL" column.
Note: Operators and functions with "No" indicated in the SQL column of Table 9-1 do not work in SQL queries used in PL/SQL blocks - even though some of these operators and functions are supported for use directly in PL/SQL code. |
Functions designated as "CNV" in the SQL or PL/SQL column of Table 9-1 are performed by converting the CLOB to a character datatype, such as VARCHAR2. In the SQL environment, only the first 4K bytes of the CLOB are converted and used in the operation; in the PL/SQL environment, only the first 32K bytes of the CLOB are converted and used in the operation.
Category | Operator / Function | SQL Example / Comments | SQL | PL/SQL |
---|---|---|---|---|
Concatenation |
||, CONCAT() |
Select clobCol || clobCol2 from tab; |
Yes |
Yes |
Comparison |
= , !=, >, >=, <, <=, <>, ^= |
if clobCol=clobCol2 then... |
No |
Yes |
Comparison |
IN, NOT IN |
if clobCol NOT IN (clob1, clob2, clob3) then... |
No |
Yes |
Comparison |
SOME, ANY, ALL |
if clobCol < SOME (select clobCol2 from...) then... |
No |
N/A |
Comparison |
BETWEEN |
if clobCol BETWEEN clobCol2 and clobCol3 then... |
No |
Yes |
Comparison |
LIKE [ESCAPE] |
if clobCol LIKE '%pattern%' then... |
Yes |
Yes |
Comparison |
IS [NOT] NULL |
where clobCol IS NOT NULL |
Yes |
Yes |
Character Functions |
INITCAP, NLS_INITCAP |
select INITCAP(clobCol) from... |
CNV |
CNV |
Character Functions |
LOWER, NLS_LOWER, UPPER, NLS_UPPER |
...where LOWER(clobCol1) = LOWER(clobCol2) |
Yes |
Yes |
Character Functions |
LPAD, RPAD |
select RPAD(clobCol, 20, ' La') from... |
Yes |
Yes |
Character Functions |
TRIM, LTRIM, RTRIM |
...where RTRIM(LTRIM(clobCol,'ab'), 'xy') = 'cd' |
Yes |
Yes |
Character Functions |
REPLACE |
select REPLACE(clobCol, 'orig','new') from... |
Yes |
Yes |
Character Functions |
SOUNDEX |
...where SOUNDEX(clobCOl) = SOUNDEX('SMYTHE') |
CNV |
CNV |
Character Functions |
SUBSTR |
...where substr(clobCol, 1,4) = 'THIS' |
Yes |
Yes |
Character Functions |
TRANSLATE |
select TRANSLATE(clobCol, '123abc','NC') from... |
CNV |
CNV |
Character Functions |
ASCII |
select ASCII(clobCol) from... |
CNV |
CNV |
Character Functions |
INSTR |
...where instr(clobCol, 'book') = 11 |
Yes |
Yes |
Character Functions |
LENGTH |
...where length(clobCol) != 7; |
Yes |
Yes |
Character Functions |
NLSSORT |
...where NLSSORT (clobCol,'NLS_SORT = German') > NLSSORT ('S','NLS_SORT = German') |
CNV |
CNV |
Character Functions |
INSTRB, SUBSTRB, LENGTHB |
These functions are supported only for CLOBs that use single-byte character sets. (LENGTHB is supported for BLOBs as well as CLOBs.) |
Yes |
Yes |
Character Functions - Regular Expressions |
REGEXP_LIKE |
This function searches a character column for a pattern. Use this function in the WHERE clause of a query to return rows matching the regular expression you specify. See the Oracle Database SQL Reference for syntax details on SQL functions for regular expressions. See the Oracle Database Application Developer's Guide - Fundamentals for information on using regular expressions with the database. |
Yes |
Yes |
Character Functions - Regular Expressions |
REGEXP_REPLACE |
This function searches for a pattern in a character column and replaces each occurrence of that pattern with the pattern you specify. |
Yes |
Yes |
Character Functions - Regular Expressions |
REGEXP_INSTR |
This function searches a string for a given occurrence of a regular expression pattern. You specify which occurrence you want to find and the start position to search from. This function returns an integer indicating the position in the string where the match is found. |
Yes |
Yes |
Character Functions - Regular Expressions |
REGEXP_SUBSTR |
This function returns the actual substring matching the regular expression pattern you specify. |
Yes |
Yes |
Conversion |
CHARTOROWID |
CHARTOROWID(clobCol) |
CNV |
CNV |
Conversion |
HEXTORAW |
HEXTORAW(CLOB) |
No |
CNV |
Conversion |
CONVERT |
select CONVERT(clobCol,'WE8DEC','WE8HP') from... |
Yes |
CNV |
Conversion |
TO_DATE |
TO_DATE(clobCol) |
CNV |
CNV |
Conversion |
TO_NUMBER |
TO_NUMBER(clobCol) |
CNV |
CNV |
Conversion |
TO_TIMESTAMP |
TO_TIMESTAMP(clobCol) |
No |
CNV |
Conversion |
TO_SINGLE_BYTE |
TO_SINGLE_BYTE(clobCol) |
CNV |
CNV |
Conversion |
TO_CHAR |
TO_CHAR(clobCol) |
Yes |
Yes |
Conversion |
TO_NCHAR |
TO_NCHAR(clobCol) |
Yes |
Yes |
Conversion |
TO_LOB |
INSERT INTO... SELECT TO_LOB(longCol)... Note that 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 |
Conversion |
TO_CLOB |
TO_CLOB(varchar2Col) |
Yes |
Yes |
Conversion |
TO_NCLOB |
TO_NCLOB(varchar2Clob) |
Yes |
Yes |
Aggregate Functions |
COUNT |
select count(clobCol) from... |
No |
N/A |
Aggregate Functions |
MAX, MIN |
select MAX(clobCol) from... |
No |
N/A |
Aggregate Functions |
GROUPING |
select grouping(clobCol) from... group by cube (clobCol); |
No |
N/A |
Other Functions |
GREATEST, LEAST |
select GREATEST (clobCol1, clobCol2) from... |
No |
CNV |
Other Functions |
DECODE |
select DECODE(clobCol, condition1, value1, defaultValue) from... |
CNV |
CNV |
Other Functions |
NVL |
select NVL(clobCol,'NULL') from... |
Yes |
Yes |
Other Functions |
DUMP |
select DUMP(clobCol) from... |
No |
N/A |
Other Functions |
VSIZE |
select VSIZE(clobCol) from... |
No |
N/A |
Unicode |
INSTR2, SUBSTR2, LENGTH2, LIKE2 |
These functions use UCS2 code point semantics. |
No |
CNV |
Unicode |
INSTR4, SUBSTR4, LENGTH4, LIKE4 |
These functions use UCS4 code point semantics. |
No |
CNV |
Unicode |
INSTRC, SUBSTRC, LENGTHC, LIKEC |
These functions use complete character semantics. |
No |
CNV |
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 9-1.)
See Also:
for a detailed description on the usage of UNICODE functions. |
Codepoint semantics of the INSTR, SUBSTR, LENGTH, and LIKE functions, described in Table 9-1, differ depending on the datatype 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:
The return type of a function or operator that takes a LOB or VARCHAR2 is the same as the datatype of the argument passed to the function or operator.
Functions that take more than one argument, such as CONCAT, return a LOB datatype if one or more arguments is a LOB. For example, CONCAT(CLOB, VARCHAR2) returns a CLOB.
See Also:
Oracle Database SQL Reference for details on the CONCAT function and the concatenation operator (||). |
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.
The return value of the LENGTH function differs depending on whether the argument passed is a LOB or a character string:
Some LOB datatypes support implicit conversion and can be used in operations such as cross-type assignment and parameter passing. These conversions are processed at the SQL layer and can be performed in all client interfaces that use LOB types.
The database enables you to perform operations such as cross-type assignment and cross-type parameter passing between CLOB and NCLOB datatypes. The database performs implicit conversions between these types when necessary to preserve properties such as characterset 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 convesion 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
See Also:
|
Table 9-2 lists SQL operations that are not supported on LOB columns.
The following semantics, used with VARCHAR2 and RAW datatypes, also apply to LOBs:
You can define a VARCHAR2 for a CLOB and RAW for a BLOB column. You can also define CLOB and BLOB types for VARCHAR2 and RAW columns.
If a CLOB column is selected into a VARCHAR2 variable, then 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, then a truncation error is thrown and no data is written to the buffer. After successful completion of the SELECT operation, the VARCHAR2 variable holds as a regular character buffer.
In contrast, when a CLOB column is selected into a local CLOB variable, the CLOB locator is fetched.
When a BLOB column is selected into a RAW variable, the BLOB data is copied into the RAW buffer. If the size of the BLOB exceeds the size of the buffer, then a truncation error is thrown and no data is written to the buffer.
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:
DBMS_LOB.FREETEMPORARY()
call to release the resources taken by the local temporary LOBs.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
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.
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;