Database Reference
In-Depth Information
CAST ( { collection | MULTISET (subquery) } AS type )
. Changes
one datatype into another datatype or changes one type into another
type. The CAST function, in object-oriented design parlance, is a
typecasting function. A typecasting function casts one datatype into
another or changes one type to another. The following example sim-
ply typecasts or converts from a numeric to a string value. Obviously,
converting from a string to a number would require numerals. There-
fore, the first query will work and the second will not.
SELECT CAST(ARTIST_ID AS VARCHAR2(38)) FROM ARTIST); --valid
SELECT CAST('abc' AS NUMBER) FORM DUAL; --invalid
The CAST function, the MULTISET operator, and a subquery
can be used to create a nested table from a relational table. MULTI-
SET operators are explained in Chapter 7.
CREATE OR REPLACE TYPE tINSTRUMENT AS OBJECT(
INSTRUMENT_ID NUMBER
, SECTION_ID NUMBER, NAME VARCHAR2(32));
/
CREATE OR REPLACE TYPE tINSTRUMENTS AS TABLE OF tINSTRUMENT;
/
SELECT CAST(MULTISET(SELECT * FROM INSTRUMENT) AS
tINSTRUMENTS) FROM DUAL;
COLLECT (columnar expression)
. Returns a nested table
from the row set result of a column expression in a table. The follow-
ing queries are valid:
SELECT COLLECT(NAME) FROM INSTRUMENT;
SELECT COLLECT(TO_CHAR(SECTION_ID)||','||NAME)
FROM INSTRUMENT;
The following query is not valid because a columnar expression is
required:
SELECT COLLECT(*) FROM INSTRUMENT;
