This tutorial is based on examples to be easier to follow. The Oracle Cast function allows converting one built-in type of value to another one. In other-words using Oracle Cast you can change a value to a different value type. Also Cast can be used to insert a set of data into a collection type. The Oracle Cast syntax for converting a value to another type is following:
CAST (<value> AS <convert_into_type_name>);
The first Oracle Cast example will convert a date value to a Timestamp. You are able to see how the time digits became longer in the output and since a Timestamp is more accurate than a date value, it stores smaller units 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;
This example below is an opposite way conversion than the first example because now we will convert a Timestamp value to a date. Take a look at the SQL query output to see that time zone attributes are missing since a date don’t have them.
SELECT CAST(systimestamp AS DATE) AS THE_DATE FROM DUAL;
To cast a number 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 one of the reasons 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 fourth example shows how to convert a Rowid value into text. The Rowid values are quite useful to find out the certain row in a table. They are widely used in debugging process to point to a certain row that caused a trouble.
SELECT CAST(rowid AS VARCHAR2(30)) AS my_text FROM DUAL;
Oracle Cast can be used with the Oracle collection types and the advantage is that you can insert them into a type inside SQL query and that way you code will be smaller and cleaner. The Oracle Cast syntax for casting a data set into a collection type is following:
CAST (MULTISET (<select_statement>) AS <convert_to_a_collection_type_name>);
To try the MULTISET keyword in Oracle Cast function we need 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; /
Now we need 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 number from 1 to 10. Take a look at the following SQL query.
SELECT rownum
FROM DUAL
CONNECT BY rownum < 11;
We are ready to try out the example since we did create the ROWNUMS_T type and we have a 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 example above. The Oracle SQL Developer shows the context of the ROWNUMS_T type and they are 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:
Home Oracle Select
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.