CAST

Syntax

cast::=

Text description of cast

Purpose

CAST converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.

CAST lets you convert built-in datatypes or collection-typed values of one type into another built-in datatype or collection type. You can cast an unnamed operand (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_name must be the name of a built-in datatype or collection type and the operand must be a built-in datatype or must evaluate to a collection value.

For the operand, expr can be either a built-in datatype or a collection type, and subquery must return a single value of collection type or built-in type. MULTISET informs Oracle to take the result set of the subquery and return a collection value. Table 6-1 shows which built-in datatypes can be cast into which other built-in datatypes. (CAST does not support LONG, LONG RAW, any of the LOB datatypes, or the Oracle-supplied types.)

Table 6-1  Casting Built-In Datatypes

	from CHAR, VARCHAR2	from NUMBER	from DATETIME / INTERVALb	from RAW	from ROWID, UROWID	from NCHAR, NVARCHAR2
to CHAR, VARCHAR2	X	X	X	X	X	--
to NUMBER	X	X	--	--	--	--
to DATE, TIMESTAMP, INTERVAL	X	--	X	--	--	--
to RAW	X	--	--	X	--	--
to ROWID, UROWID	X	--	--	--	Xa	--
to NCHAR, NVARCHAR2	--	X	X	X	X	X
a You cannot cast a UROWID to a ROWID if the UROWID contains the value of a ROWID of an index-organized table. b Datetime/Interval includes DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH,

If you want to cast a named collection type into another named collection type, then the elements of both collections must be of the same type.

If the result set of subquery can evaluate to multiple rows, then 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.

Built-In Datatype Examples

The following examples use the CAST function with scalar datatypes:

SELECT CAST('22-OCT-1997' AS TIMESTAMP WITH LOCAL TIME ZONE) 
   FROM dual;

SELECT product_id, 
   CAST(ad_sourcetext AS VARCHAR2(30))
   FROM print_media;

Collection Examples

The CAST examples that follow build on the cust_address_typ found in the sample order entry schema, oe.

CREATE TYPE address_book_t AS TABLE OF cust_address_typ;
/
CREATE TYPE address_array_t AS VARRAY(3) OF cust_address_typ;
/
CREATE TABLE cust_address (
   custno            NUMBER, 
   street_address    VARCHAR2(40), 
   postal_code       VARCHAR2(10), 
   city              VARCHAR2(30),
   state_province    VARCHAR2(10), 
   country_id        CHAR(2));

CREATE TABLE cust_short (custno NUMBER, name VARCHAR2(31));

CREATE TABLE states (state_id NUMBER, addresses address_array_t);

This example casts a subquery:

SELECT s.custno, s.name,
   CAST(MULTISET(SELECT ca.street_address,   
                        ca.postal_code, 
                        ca.city, 
                        ca.state_province, 
                        ca.country_id
                 FROM cust_address ca
                 WHERE s.custno = ca.custno)
   AS address_book_t)
FROM cust_short s;

CAST converts a varray type column into a nested table:

SELECT CAST(s.addresses AS address_book_t)
   FROM states s 
   WHERE s.state_id = 111; 

The following objects create the basis of the example that follows:

CREATE TABLE projects 
   (employee_id NUMBER, project_name VARCHAR2(10));

CREATE TABLE emps_short 
   (employee_id NUMBER, last_name VARCHAR2(10));

CREATE TYPE project_table_typ AS TABLE OF VARCHAR2(10);
   /

The following example of a MULTISET expression uses these objects:

SELECT e.last_name,
   CAST(MULTISET(SELECT p.project_name
   FROM projects p 
   WHERE p.employee_id = e.employee_id
   ORDER BY p.project_name)
   AS project_table_typ)
FROM emps_short e;