Oracle / PLSQL: CAST Function

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


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


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:


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


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


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.