Oracle / PLSQL: CAST Function

This article is written about how to use the Oracle/PLSQL CAST function with syntax and examples.

Description

The Oracle/PLSQL CAST function converts one datatype to another.

Syntax

The syntax for the CAST function in Oracle/PLSQL is:

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

Allowed Casts

When using the CAST function, the following casts are allowed:

TO FROM

char, varchar2 number datetime / interval raw rowid, urowid nchar, nvarchar2

char, varchar2 X X X X X

number X X

datetime / interval X X

raw X X

rowid, urowid X X

nchar, nvarchar2 X X X X X

Returns

The CAST function returns the new datatype that you have converted to.

If the resulting value is larger than the target type, an error is returned.

Applies To

The CAST function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Example

Let’s look at some Oracle CAST function examples and explore how to use the CAST function in Oracle/PLSQL.

For example:

select CAST( '22-Aug-2003' AS varchar2(30) )
from dual;

This would convert the date (ie: 22-Aug-2003) into a varchar2(30) value.