Oracle CAST Function Explained with Examples

The Oracle CAST function is quite powerful, but it can be hard to use. Learn how to use it and what it can do in this article.

Purpose of the Oracle CAST Function

The purpose of the Oracle CAST function is to convert one data type to another.

It allows for more functionality than the TO_NUMBER, TO_CHAR and TO_DATE functions, as it allows you to specify the actual data types you want to convert to, rather than just use the defaults of those functions.

Syntax

This structure is a little different as it does not separate the parameters by a comma, as many other functions do. Instead, it has the word “AS”.

There are two main ways you can run this query. You can run it using a single result data type, which would be:

CAST( expr AS type_name )

Or, you can run it using a subquery, which would be:

CAST( MULTISET (subquery) AS type_name )

Parameters

The parameters of the CAST function are:

expr (mandatory choice): This is the value or expression that is to be converted to another data type. See below for the valid data types.

MULTISET (optional): This keyword indicates that the subquery results will return multiple rows, and are then CAST into a collection value. If this is omitted, and the subquery returns multiple rows, it will only return a single row.

subquery (mandatory choice): This is the subquery that can be run to be converted into a collection data type.

type_name (mandatory): This is the Oracle data type that the expr or subquery will be converted to. See below for the valid data types.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

Valid Data Types for Oracle Cast

The Oracle CAST function can only convert to and from certain data types. This table from Oracle shows which types are compatible.

from BINARY_FLOAT, BINARY_DOUBLE

from CHAR, VARCHAR2

from NUMBER

from DATETIME / INTERVAL

from RAW

from ROWID, UROWID

from NCHAR, NVARCHAR2

to BINARY_FLOAT, BINARY_DOUBLE

X

X

X

X

to CHAR, VARCHAR2

X

X

X

X

X

X

to NUMBER

X

X

X

X

to DATE, TIMESTAMP, INTERVAL

X

X

to RAW

X

X

to ROWID, UROWID

X

X

to NCHAR, NVARCHAR2

X

X

X

X

X

X

A few things to note from this table:

The Datetime/Interval column includes DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH.

You cannot cast a UROWID to a ROWID if the UROWID contains the value of a ROWID of an index-organized table.

CAST does not support any of the LOB data types. If you try to CAST a CLOB into a character data type, for example, the LOB value is converted to a character value and then converted to the target data type. If the resulting value is larger than the target type, then you’ll get an error.

Want to see articles like this on all of the other Oracle functions? Check out this page which lists all of the functions and guides for each of them.

Examples of the Oracle CAST Function

Here are some examples of the Oracle CAST function. I find that examples are the best way for me to learn about code, even with the explanation above.