Native LOB Operations in Oracle9i

Almost since the day Oracle unleashed LOB functionality to the vast hordes of Oracle database users, programmers and query-writers have wanted to treat LOBs as very large versions of regular, scalar variables. Particularly, users wanted to treat CLOBs as very large character strings, passing them to SQL functions, using them in SQL statement WHERE clauses, and so forth. To the dismay of many, CLOBs could not be used interchangeably with VARCHAR2s. For example, in Oracle8 and Oracle8i, you could not apply a character function to a CLOB column:

SELECT SUBSTR(falls_directions,1,60) FROM waterfalls;

Beginning in Oracle9i Release 1, however, you can now use CLOBs interchangeably with VARCHAR2s in a wide variety of situations:

· You can pass CLOBs to most SQL and PL/SQL VARCHAR2 functions.

· In PL/SQL, but not in SQL, you can use various relational operators such as less-than (<), greater-than (>), and equals (=) with LOB variables.

· You can assign CLOB values to VARCHAR2 variables and vice versa. You can also select CLOB values into VARCHAR2 variables and vice versa. This is because PL/SQL now implicitly converts between the CLOB and VARCHAR2 types.

Oracle refers to these new capabilities as offering "SQL semantics" for LOBs. From a PL/SQL developer's standpoint, it means that you can manipulate LOBs using native operators rather than a supplied package.

 

 

Following is an example showing some of the new things you can do with LOBs in Oracle9i:

DECLARE name CLOB; name_upper CLOB; directions CLOB; blank_space VARCHAR2(1) := ' ';BEGIN --Retrieve a VARCHAR2 into a CLOB, apply a function to a CLOB SELECT falls_name, SUBSTR(falls_directions,1,500) INTO name, directions FROM waterfalls WHERE falls_name = 'Munising Falls'; --Uppercase a CLOB name_upper := UPPER(name); -- Compare two CLOBs IF name = name_upper THEN DBMS_OUTPUT.PUT_LINE('We did not need to uppercase the name.'); END IF; --Concatenate a CLOB with some VARCHAR2 strings IF INSTR(directions,'Mackinac Bridge') <> 0 THEN DBMS_OUTPUT.PUT_LINE('To get to ' || name_upper || blank_space || 'you must cross the Mackinac Bridge.'); END IF;END;

This example will output the following message:

To get to MUNISING FALLS you must cross the Mackinac Bridge.

The small piece of code in this example does several interesting things:

· The falls_name column is a VARCHAR2 column, yet it is retrieved into a CLOB variable. This is a demonstration ofimplicit conversion between the VARCHAR2 and CLOB types.

· the SUBSTR function is used to limit retrieval to only the first 500 characters of the directions to Munising Falls. Further, the UPPER function is used to uppercase the falls name. This demonstrates the application of SQL and PL/SQL functions to LOBs.

· The IF statement that compares name to name_upper is a bit forced, but it demonstrates that relational operators may now be applied to LOBs.

· The uppercased falls name, a CLOB, isconcatenated with some string constants and one VARCHAR2 string (blank_space). This shows that CLOBs may now be concatenated.

There are many restrictions and caveats that you need to be aware of when using this functionality. For example, not every function that takes a VARCHAR2 input will accept a CLOB in its place; there are some exceptions. Likewise, not all relational operators are supported for use with LOBs. All of these restrictions and caveats are described in detail in the section called "SQL Semantics Support for LOBs" in Chapter 7 of Oracle's Modeling and Design Application Developer's Guide—Large Objects. If you're using this new functionality, we strongly suggest that you take a look at that section of the manual.

SQL semantics for LOBs apply only to internal LOBs: CLOBs, BLOBs, and NCLOBs. SQL semantics support does not apply to BFILEs.