Oracle CAST Function Tips

The Oracle CAST function converts one data type to another. The
CAST function can convert built-in and collection-typed values into
other built-in or collection typed values.

CAST can convert a date or other unnamed operand (or a nested
table or other named collection) into a type-compatible datatype or
named collection. For this use of CAST, type_name and/or
operand must be of (or evaulate to) a built-in datatype or collection
type .

CAST and ANYDATA Type

When using CAST to convert an operand, the expr can also be either a
built-in datatype or a collection type; however, it can be an instance
of an ANYDATA type as well. When the expr is of an ANYDATA type, CAST
attempts an extraction of the value of the ANYDATA expr and returns it
if it matches the CAST target type. If the there is no match to
the CAST target type, NULL is returned.

CAST with LOB Datatypes

LOB datatypes are not directly supported by the CAST function.
Using CAST to convert CLOB values into a character datatypes or BLOB
values into the RAW datatype results in the database converting the LOB
value implicity to character or raw data. Once this implicit
conversion is done, the resulting value is CAST into the target
datatype. This process will throw an error if the resulting value
is larger than the target type.

The Oracle docs note the syntax for Oracle CAST as follows:

CAST({
expr | MULTISET
(subquery) } AS
type_name)

With the Oracle CAST function a block could be
re-written as:

DECLARE v NUMBER;BEGIN a(CAST (v AS INTEGER));
END;

On the other hand, the function
could be re-written with Oracle CAST as:

MULTISET must be specified when the CAST
subquery results will return multiple rows. In this situation,
the rows returned by the CAST function are the elements that comprise
the collection value into which they are cast. Failure to specify
MULTISET keyword, causes the subquery to be treated as a scalar
subquery.

Conversely, use of MULTISET causes the return of a
collection value from the results of the CAST subquery.

Use of CAST and MULTISET to get a collection value
results in each item in the SELECT list that is passed to the CAST
function to be converted to the attribute type of the target collection
type.

The SQL operators CAST and MULITSET casts a
multiple input data stream into the appropriate data types for the SQL
operation:

Do not use CAST if you need SUBSTR(char), TO_CHAR(number) or
TO_CHAR(date) !

��

Burleson is the American Team

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail:
and include the URL for the page.