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

As listed in Table 7-6 through Table 7-8, the SQL functions/operators for VARCHAR2s/RAWs have been extended to work on CLOB or BLOB columns.

The return type of the SQL functions depend on the input type. Refer to "Returning CLOB Values from SQL Functions/Operators" for a detailed discussion.

The following examples show queries that benefit from the VARCHAR2 semantics on CLOBs. In prior releases, the effects of these queries used to be achieved, in PL/SQL code, using DBMS_LOB calls. It will be convenient for you to be able to use the same interface as VARCHAR2s to access data.

Note: These examples are based on the following revised version of the Multimedia application schema described in Appendix B, "The Multimedia Schema Used for Examples in This Manual" and Chapter 10, "Internal Persistent LOBs" under "Creating a Table Containing One or More LOB Columns": CREATE TABLE Multimedia_tab (    Clip_ID NUMBER NOT NULL,    Story          CLOB default EMPTY_CLOB(),    Gist           VARCHAR2(100),    ...... }

SQL Query Example 2: A few SQL queries on CLOBs

SELECT Gist||Story FROM Multimedia_tab WHERE Story LIKE Gist; 

SELECT SUBSTR(Story, 20, 1), LENGTH(Story) FROM Multimedia_tab WHERE Gist NOT IN 
Story;
-- A temp LOB is created and returned for 'Gist||Story' and 'SUBSTR(Story,20,1)' because story is a 
CLOB.