Oracle CAST() Function

Oracle CAST() is a built-in function that converts a given argument from one type to another. This function supports both basic data types and collection types.

Oracle `CAST()` Syntax

Here is the syntax for the Oracle CAST() function:

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

Parameters

expr: Required. It can be an instance of a built-in data type, a collection type, or the ANYDATA type. One of this parameter or the MULTISET (subquery) parameter must be provided.
type_name: Required. It must be the name of a built-in data type or collection type.
DEFAULT return_value ON CONVERSION ERROR: Optional. It allows you to specify the value to be returned when a conversion error occurs.
fmt: Optional. A formatting string.
'nlsparam': Optional. You can use this 'NLS_DATE_LANGUAGE = language' form to set this parameter, where language is the language name.

Return Value

The Oracle CAST() function converts the given argument from one type to another and returns the converted value.

CAST does not support LONG and LONG RAW types. CAST does not directly support any LOB data type. When using CAST to convert a CLOB value to a character data type or a BLOB value to a RAW data type, the database implicitly converts the LOB value to character or raw data and then explicitly forces the result value to be converted to the target data type. If the result value is larger than the target type, the database will return an error.

When using CAST ... MULTISET to retrieve collection values, each selection list item in the query passed to the CAST function will be converted to the corresponding attribute type of the target collection element type.

Oracle `CAST()` Examples

Here are some examples demonstrating the usage of the Oracle CAST() function.

Basic Usage

The following example demonstrates how to convert a string date to a TIMESTAMP type:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF';
SELECT
    CAST('2023-02-28' AS TIMESTAMP, 'YYYY-MM-DD') Result
FROM dual;

输出：

RESULT
________________________________
2023-02-28 00:00:00.000000000

National Language Support

The Oracle CAST() function allows you to specify the language for conversion to be performed in the specified language.

SELECT CAST(
        '28-Feb-23' AS TIMESTAMP,
        'DD-MON-YY',
        'NLS_DATE_LANGUAGE = American'
    ) Result
FROM dual;

输出：

RESULT
________________________________
2023-02-28 00:00:00.000000000

Here is an example using Simplified Chinese language:

SELECT CAST(
        '20-2月 -23' AS TIMESTAMP,
        'DD-MON-YY',
        'NLS_DATE_LANGUAGE = ''Simplified Chinese'''
    ) Result
FROM dual;

输出：

RESULT
__________________________________
20-2月 -23 12.00.00.000000000 上午

Error Handling

The Oracle CAST() function allows you to specify the content to return when an error occurs during value conversion using the DEFAULT return_value ON CONVERSION ERROR parameter.

If you run the following statement directly, Oracle may return the following error:

SELECT
    CAST('2023-02-28' AS TIMESTAMP) Result
FROM dual;

输出：

SQL Error: ORA-01843: not a valid month
01843. 00000 -  "not a valid month"

This is because the format may be inconsistent with your session language. In this case, you can specify a default value as follows:

SELECT
    CAST('2023-02-28' AS TIMESTAMP DEFAULT '28-2月 -23' ON CONVERSION ERROR) Result
FROM dual;

输出：

RESULT
__________________________________
28-2月 -23 12.00.00.000000000 上午

Note that the default value needs to match the current environment settings or the format specified by the fmt parameter.

Conclusion

The Oracle CAST() is a built-in function that converts the given parameter from one type to another. This function supports basic data types and collection types.

Oracle CAST() Syntax