How To Use CAST In Oracle

This help is based on examples so it would be easier to understand. Oracle Cast function allows to convert one built-in value type to another value type. That does mean using Oracle Cast you can put a value into a different value type. In addition, Cast can be used to insert a set of data into an Oracle collection type. The syntax of Oracle Cast for converting a value to another type is following:

CAST (<value> AS <convert_into_type_name>);

This Oracle Cast example will convert a date value to an Oracle Timestamp type. After the conversion you’ll able to see how the time digits became longer in the output and since the Timestamp is more accurate than a date value, it stores smaller units of time than a date. Of course the conversion is missing the time units and they are all set to zero.

 SELECT CAST (TO_DATE ('11/04/2013','DD/MM/YYYY')
              AS TIMESTAMP WITH LOCAL TIME ZONE) AS my_timestamp
   FROM DUAL;

The second Oracle Cast example is an opposite way conversion than the example above because we will convert an Oracle Timestamp value to a date. Take a look at the SQL query output to see that time zone attributes are missing since Oracle Date don’t have them.

 SELECT CAST(systimestamp AS DATE) AS THE_DATE
   FROM DUAL;

To convert a number value to a text keep in mind that a number doesn’t keep zero values in front and you can’t save them even with Oracle Cast function. This is why mobile numbers and ID numbers are stored as text to keep them with their original look.

 SELECT CAST(0000111001 AS VARCHAR2(30)) AS my_text
   FROM DUAL;

The following example shows how to convert an Oracle Rowid value into text. The Rowid values are quite useful to find out the certain row in a table. They are widely used in the code debugging process to find out a certain row that causes a trouble.

 SELECT CAST(rowid AS VARCHAR2(30)) AS my_text
   FROM DUAL;

You can also use the Oracle Cast with the Oracle collection types and the advantage is you can insert them into a type inside SQL query without having an extra procedure. This way your code will be smaller and cleaner. The Oracle Cast syntax for inserting a data set into a collection type is following:

CAST (MULTISET (<select_statement>) AS <convert_to_a_collection_type_name>);

To try in Oracle Cast function the MULTISET keyword we need to create a collection type. The following script will create a type named ROWNUMS_T as a table of numbers.

CREATE TYPE rownums_t AS TABLE OF NUMBER;
/

The second thing we need a data set. To get a set of number we will use a Select query that returns only numbers. To keep this example as simple as possible we are going to use the Oracle Dual table and hierarchical operator CONNECT BY to create 10 lines with numbers from 1 to 10. Take a look at the SQL query below.

 SELECT rownum
     FROM DUAL
  CONNECT BY rownum < 11;

Now we are ready to try out the example since we did create the ROWNUMS_T type and we do have the query that returns only number (take a look at the SQL query above). We will use both objects in the Oracle Cast function in the following example. The Oracle SQL Developer’s screen will show the context of the ROWNUMS_T type and they will be numbers from 1 to 10.

 SELECT du1.dummy,
    CAST (MULTISET (SELECT rownum
                      FROM DUAL
                   CONNECT BY rownum < 11)
                     AS rownums_t) as casted_rownums
   FROM dual du1;

Take a look at the table below to see values that can be used with Oracle Cast function.

See Also:
Oracle Select Oracle Timestamp Oracle Home

Casting Built-In Data Types:

	from BINARY_FLOAT, BINARY_DOUBLE	from CHAR, VARCHAR2	from NUMBER	from DATETIME / INTERVAL (Note 1)	from RAW	from ROWID, UROWID (Note 2)	from NCHAR, NVARCHAR2
to BINARY_FLOAT, BINARY_DOUBLE	Y	Y	Y				Y
to CHAR, VARCHAR2	Y	Y	Y	Y	Y	Y
to NUMBER	X	Y	Y				Y
to DATE, TIMESTAMP, INTERVAL		Y		Y
to RAW		Y			Y
to ROWID, UROWID		Y				Y
to NCHAR, NVARCHAR2	Y		Y	Y	Y	Y	Y

Note 1: Datetime/interval includes DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH.

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