[Chapter 10] 10.3 SQL99 Compliance

10.3 SQL99 Compliance

PL/SQL 8.1 adds support for several functions to come into compliance with the SQL99 standard.

10.3.1 The TRIM Function

The TRIM function allows you to trim leading or trailing characters (or both) from a character string. Here is the header of the function:

FUNCTION TRIM ( [LEADING | TRAILING | BOTH] trim_chars FROM trim_source ) 
   RETURN VARCHAR2;

where trim_chars is the number of characters to trim from trim_source.

This function conforms to these rules:

If trim_chars or trim_source is a character literal, you must enclose it in single quotes.
If you specify LEADING, Oracle removes any leading characters that are found in the trim_chars string (equivalent to the LTRIM built-in function).
If you specify TRAILING, PL/SQL removes any trailing characters found in trim_chars (equivalent to the RTRIM built-in function).
If you specify BOTH or none of the three, Oracle removes leading and trailing characters found in trim_chars.
If you do not specify trim_chars, the default value is a blank space.
The maximum length of the value is the length of trim_source.

The following block exercises the various forms of the TRIM command:

/* Filename on companion disk: trim.sql */
DECLARE
   mystr VARCHAR2(10);
   yourstr VARCHAR2(10) := 'Toronto';
BEGIN
   DBMS_OUTPUT.PUT_LINE (
      TRIM (LEADING 'x' FROM 'xyzabc'));
   DBMS_OUTPUT.PUT_LINE (
      TRIM (TRAILING 'cb' FROM 'xyzabc'));
   DBMS_OUTPUT.PUT_LINE (
      TRIM (BOTH 'x1' FROM '1x1yzabc111x'));
   DBMS_OUTPUT.PUT_LINE (
      TRIM ('x1' FROM '1x1yzabc111x'));
   DBMS_OUTPUT.PUT_LINE (
      TRIM (mystr FROM yourstr));
END;
/

and results in this output:

yzabc
xyza
yzabc
yzabc
Toronto

TIP: Oracle documentation states that "if either trim_source or trim_chars is a NULL value, then the TRIM function returns a null value." My tests indicate, however, that if trim_chars is NULL, then trim_source is returned unchanged.

The CAST function converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value. CAST was first made available in Oracle 8.0 and has been extended in a variety of ways in Oracle8i. CAST, in essence, offers a single program name (and different syntax) to replace a variety of conversion programs, such as ROWIDTOHEX, TO_DATE, TO_CHAR, and so on. Here is the general syntax of the CAST statement:

CAST (expression AS type)

where expression is the expression to be converted and type is the name of the datatype to which the expression is converted.

You can cast an unnamed expression (such as a date or the result set of a subquery) or a named collection (such as a VARRAY or a nested table) into a type-compatible datatype or named collection. The type must be the name of a built-in datatype or collection type and the expression must be a built-in datatype or must evaluate to a collection value.

The expression can be either a built-in datatype or a collection type, and a subquery must return a single value of the collection type or the built-in type. If you are casting the result set of a subquery, you must then specify the MULTISET keyword to tell Oracle to return a collection value. MULTISET is illustrated in the last example in Section 10.3.2.2, "Collection examples.

Table 10.1 shows which built-in datatypes can be cast into which other built-in datatypes.

Table 10.1: Conversions Supported by CAST
From/ To	CHAR, VARCHAR2	NUMBER	DATE	RAW	ROWID, UROWID	NCHAR, NVARCHAR2
CHAR, VARCHAR2	✓	✓	✓	✓	✓
NUMBER	✓	✓
DATE	✓		✓
RAW	✓			✓
ROWID, UROWID	✓				✓[1]
NCHAR, NVARCHAR2		✓	✓	✓	✓	✓

[1] You can't cast a UROWID to a ROWID if the UROWID contains the value of a ROWID of an index-organized table.

Note the following about using CAST:

CAST does not support LONG, LONG RAW, or any of the LOB datatypes.
If the UROWID contains the value of a ROWID of an index-organized table, you cannot cast that UROWID to a ROWID.
To cast a named collection type into another named collection type, the elements of both collections must be of the same TYPE.
If the result set of a subquery can evaluate to multiple rows, you must specify the MULTISET keyword. The rows resulting from the subquery form the elements of the collection value into which they are cast. Without the MULTISET keyword, the subquery is treated as a scalar subquery, which is not supported in the CAST expression. In other words, scalar subqueries as arguments of the CAST operator are not valid in Oracle8i.
In many cases, you will not be able to use CAST directly within PL/SQL. It is primarily a SQL operator.

Let's take a look at some examples (you will find all of these grouped together in the cast.sql file on the disk).

10.3.2.1 Built-in datatype examples

You can use CAST instead of other datatype conversion functions:

/* Filename on companion disk: cast.sql */
BEGIN
   /* CAST for TO_DATE...no way to pass format mask.
      Instead, the database default or session setting of
      NLS_DATE_FORMAT is used. */
   DBMS_OUTPUT.PUT_LINE (CAST ('10-JAN-99' AS DATE));
   
   /* CAST as TO_NUMBER...no way to pass format mask.  */ 
   DBMS_OUTPUT.PUT_LINE (CAST ('107898888' AS NUMBER));
END;
/

As noted, there isn't any way to include a format mask with which to drive the conversion. If I use a different format for my date string, I get an error:

SQL>
  1  BEGIN
  2     DBMS_OUTPUT.PUT_LINE (
  3        CAST ('1/1/99' AS DATE));
  4* END;
  5  /

ERROR at line 1:
ORA-01843: not a valid month

I can use CAST to convert a ROWID to a string as follows:

SQL> SELECT CAST (ROWID AS VARCHAR2(20)) Vrowid 
       FROM department;

VROWID
--------------------
AAADIKAACAAAAMeAAA
AAADIKAACAAAAMeAAB
AAADIKAACAAAAMeAAC
AAADIKAACAAAAMeAAD

Yet I cannot run this same query inside PL/SQL:

SQL> DECLARE
  2     my_rowid VARCHAR2(20);
  3  BEGIN
  4     FOR rec IN (
  5        SELECT CAST (ROWID AS VARCHAR2(20)) Vrowid 
  6          FROM department)
  7     LOOP
  8        DBMS_OUTPUT.PUT_LINE (rec.Vrowid);
  9     END LOOP;
 10  END;
 11  /      
ERROR at line 1:
PLS-00220: simple name required in this context

So I am not sure how useful CAST will ever be for PL/SQL developers for conversion of scalar datatypes.

10.3.2.2 Collection examples

CAST will almost certainly come in very handy when you want to convert between types of collections, or if you want to convert a subquery directly into a collection.

To demonstrate these capabilities, I will use the following structures (see cast.sql for all of these statements and the actual exercises):

/* Filename on companion disk: cast.sql */
CREATE TYPE address_t AS OBJECT 
   (street VARCHAR2(100), city VARCHAR2(100));
/       
CREATE TYPE address_book_t AS TABLE OF address_t; 
/
CREATE TYPE address_array_t AS VARRAY(3) OF address_t; 
/
CREATE TABLE addresses OF address_t;

INSERT INTO addresses VALUES (
    address_t('19th St', 'St. Louis')); 
INSERT INTO addresses VALUES (
    address_t('Biglanes Blvd', 'Dallas'));
INSERT INTO addresses VALUES (
    address_t('Upanddown Lane', 'San Francisco'));

I now have a nested table, variable array, and database table all with the same structure. I can then use CAST -- along with the MULTISET operator in SQL -- to move between the different structures.

Suppose, for example, that I have a nested table declared in PL/SQL, and I want to move that to a VARRAY. Here are the steps I would take:

/* Filename on companion disk: cast.sql */
DECLARE
   addr_varray address_array_t;
   
   addr_ntable address_book_t := 
      address_book_t (
         address_t('Madison Ave', 'Chicago'), 
         address_t('Devon Ave', 'Chicago')); 
BEGIN 
   SELECT CAST (addr_ntable AS address_array_t)
     INTO addr_varray
     FROM dual;

I would love to be able to do this:

addr_varray := CAST (addr_ntable AS address_array_t); /* INVALID! */

Unfortunately, the CAST operator cannot be used directly (natively) in PL/SQL to operate on collections.

I can also transform the result set of a query into a nested table or VARRAY using CAST and MULTISET, as shown in the following:

/* Filename on companion disk: cast.sql */
DECLARE
   addr_varray address_array_t;
BEGIN
   SELECT CAST (MULTISET (
             SELECT street, city
               FROM addresses p
              ORDER BY city) 
       AS address_array_t)
     INTO addr_varray
     FROM dual;

Notice that I have included an ORDER BY in my query; it will generally be much more efficient to rely on SQL to do your sorting before moving your data to a PL/SQL structure.


10.2 Calling Packaged Functions in SQL		10.4 SQL Operations on Collections

10.3 SQL99 Compliance

10.3.1 The TRIM Function

10.3.2 The CAST Function

10.3.2.1 Built-in datatype examples

10.3.2.2 Collection examples