CAST() Function in Oracle

In Oracle Database, the CAST() function converts its argument to a different data type.

More specifically, it lets you convert built-in data types or collection-typed values of one type into another built-in data type or collection type.

Syntax

The syntax goes like this:

CAST({ expr | MULTISET (subquery) } AS type_name
  [ DEFAULT return_value ON CONVERSION ERROR ]
  [, fmt [, 'nlsparam' ] ])

Example

Here’s an example to demonstrate:

SELECT CAST('10-AUG-30' AS TIMESTAMP)
FROM DUAL;

Result:

10-AUG-30 12.00.00.000000000 AM

Provide a Default Value on Conversion Error

You can use the DEFAULT return_value ON CONVERSION ERROR argument to specify what to return in the event an error occurs when converting the value.

Example:

SELECT CAST(
    'Homer' AS NUMBER
    DEFAULT '0' ON CONVERSION ERROR
    )
FROM DUAL;

Result:

0

Here’s what happens when we remove the DEFAULT return_value ON CONVERSION ERROR argument:

SELECT CAST(
    'Homer' AS NUMBER
    )
FROM DUAL;

Result:

ORA-01722: invalid number

Specify a Format – The fmt Argument

In this example I use the fmt argument to specify the format of the date in the first argument:

SELECT CAST(
    'Friday, 03 September 2021' AS TIMESTAMP,
    'Day, DD Month YYYY'
    )
FROM DUAL;

Result:

03-SEP-21 12.00.00.000000000 AM

When you Omit the Format

Omitting the format may result in an error, depending on whether the first argument conforms to the session’s default formatting for the resulting data type.

Here’s what happens when I leave out the format argument:

SELECT CAST(
    'Friday, 03 September 2021' AS TIMESTAMP
    )
FROM DUAL;

Result:

Error report -
ORA-01858: a non-numeric character was found where a numeric was expected

In this case, I tried to convert a string to a TIMESTAMP value, but the string didn’t resemble a TIMESTAMP value based on my session’s NLS_TIMESTAMP_FORMAT parameter, and an error occurred.

Here’s the format that my current session uses for TIMESTAMP values:

SELECT VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_TIMESTAMP_FORMAT';

Result:

DD-MON-RR HH.MI.SSXFF AM

Let’s change that:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'fmDay, DD Month YYYY HH.MI.SSXFF AM';

Result:

Session altered.

And now let’s run the previous conversion again:

SELECT CAST(
    'Friday, 03 September 2021' AS TIMESTAMP
    )
FROM DUAL;

Result:

Friday, 3 September 2021 12.0.0.000000000 AM

This time there’s no error.

Note that, although I explicitly changed the NLS_TIMESTAMP_FORMAT parameter here, it’s usually better to change the NLS_TERRITORY parameter instead. Changing the NLS_TERRITORY parameter implicitly changes other NLS parameters, such as the date formats, currency symbols, etc.

In any case, as demonstrated above, instead of changing any of the NLS parameters, you can use the fmt argument when calling the function.

The nlsparam Argument

You can use the optional nlsparam argument to specify NLS parameters from within the function.

Example:

ALTER SESSION SET NLS_TERRITORY = 'Australia';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'English';
ALTER SESSION SET NLS_DATE_FORMAT = 'fmDay, DD Month YYYY';
SELECT 
    CAST(
    'sábado, 10 agosto 30' AS DATE,
    'Day, DD Month RR',
    'NLS_DATE_LANGUAGE = SPANISH'
    )
FROM DUAL;

Result:

Saturday, 10 August 2030

In this case, I started by setting some of my session’s NLS parameters. Then when I called CAST(), I passed the string in Spanish, and then used the nlsparam argument to specify this.

So, the result is displayed using my session’s NLS parameters, but the actual value that I passed was in Spanish.

More Info

There’s a lot to be mindful of when converting between data types. See Oracle’s documentation for more information on using the CAST() function.