Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Syntax
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, a collection type, or an instance of an ANYDATA
type. If expr
is an instance of an ANYDATA
type, CAST
will try to extract the value of the ANYDATA
instance and return it if it matches the cast target type, otherwise, null will be returned. MULTISET
informs Oracle Database to take the result set of the subquery and return a collection value. Table 5-1 shows which built-in datatypes can be cast into which other built-in datatypes. (CAST
does not support LONG
, LONG
RAW
, or the Oracle-supplied types.)
CAST
does not directly support any of the LOB datatypes. When you use CAST
to convert a CLOB
value into a character datatype or a BLOB
value into the RAW
datatype, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target datatype. If the resulting value is larger than the target type, then the database returns an error.
When you use CAST
... MULTISET
to get a collection value, each select list item in the query passed to the CAST
function is converted to the corresponding attribute type of the target collection element type.
Table 5-1 Casting Built-In Datatypes
from BINARY_FLOAT, BINARY_DOUBLE | from CHAR, VARCHAR2 | fromNUMBER | from DATETIME / INTERVAL (Note 1) | fromRAW | from ROWID, UROWID (Note 2) | from NCHAR, NVARCHAR2 | |
---|---|---|---|---|---|---|---|
to BINARY_FLOAT, BINARY_DOUBLE |
|
|
|
|
|
|
|
to CHAR, VARCHAR2 |
|
|
|
|
|
|
|
to NUMBER |
|
|
|
|
|
|
|
to DATE, TIMESTAMP, INTERVAL |
|
|
|
|
|
|
|
to RAW |
|
|
|
|
|
|
|
to ROWID, UROWID |
|
|
|
|
|
|
|
to NCHAR, NVARCHAR2 |
|
|
|
|
|
|
|
Note 1: Datetime/interval includes DATE
, TIMESTAMP
, TIMESTAMP WITH TIMEZONE
, INTERVAL DAY TO SECOND
, and INTERVAL YEAR TO MONTH
.
Note 2: You cannot cast a UROWID
to a ROWID
if the UROWID
contains the value of a ROWID
of an index-organized table.
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.
See Also:
"Implicit Data Conversion" for information on how Oracle Database implicitly converts collection type data into character dataIf 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;