Figure 7-3. Casting built-in datatypes

First let's take a look at using CAST as a replacement for scalar datatype conversion. I can use it in a SQL statement:

SELECT employee_id, cast (hire_date AS VARCHAR2 (30)) FROM employee;

and I can use it in native PL/SQL syntax:

DECLARE hd_display VARCHAR2 (30);BEGIN hd_display:= CAST (SYSDATE AS VARCHAR2);END;

A much more interesting application of CAST comes into play when you are working with PL/SQLcollections (nested tables and VARRAYs). For these datatypes, you use CAST to convert from one type of collection to another. You can also use CAST to manipulate (from within a SQL statement) a collection that has been defined as a PL/SQL variable.

Chapter 11 covers these topics in more detail, but the following example should give you a sense of the syntax and possibilities. First I create two nested table types and a relational table:

CREATE TYPE names_t AS TABLE OF VARCHAR2 (100); CREATE TYPE authors_t AS TABLE OF VARCHAR2 (100); CREATE TABLE favorite_authors (name VARCHAR2(200))

I would then like to write a program that blends together data from the favorite_ authors table with the contents of a nested table declared and populated in my program. Consider the following block:

/* File on web: cast.sql */ 1 DECLARE 2 scifi_favorites authors_t 3:= authors_t ('Sheri S. Tepper', 'Orson Scott Card', 'Gene Wolfe'); 4 BEGIN 5 DBMS_OUTPUT.put_line ('I recommend that you read books by:'); 6 7 FOR rec IN (SELECT column_value favs 8 FROM TABLE (CAST (scifi_favorites AS names_t)) 9 UNION10 SELECT NAME11 FROM favorite_authors)12 LOOP13 DBMS_OUTPUT.put_line (rec.favs);14 END LOOP;15 END;

On lines 2 and 3, I declare a local nested table and populate it with a few of my favorite science fiction/fantasy authors. In lines 7 through 11, I use the UNION operator to merge together the rows from favorite_authors with those of scifi_ favorites. To do this, I cast the PL/SQL nested table (local and not visible to the SQL engine) as a type of nested table known in the database. Notice that I am able to cast a collection of type authors_t to a collection of type names_t; this is possible because they are of compatible types. Once the cast step is completed, I call the TABLE operator to ask the SQL engine to treat the nested table as a relational table. Here is the output I see on my screen:

I recommend that you read books by:Gene WolfeOrson Scott CardRobert HarrisSheri S. TepperTom SegevToni Morrison