VALIDATE_CONVERSION
Syntax
Purpose
VALIDATE_CONVERSION
determines whether expr
can be converted to the specified data type. If expr
can be successfully converted, then this function returns 1; otherwise, this function returns 0. If expr
evaluates to null, then this function returns 1. If an error occurs while evaluating expr
, then this function returns the error.
For expr
, specify a SQL expression. The acceptable data types for expr
, and the purpose of the optional fmt
and nlsparam
arguments, depend on the data type you specify for type_name
.
For type_name
, specify the data type to which you want to convert expr
. You can specify the following data types:
-
BINARY_DOUBLE
If you specify
BINARY_DOUBLE
, thenexpr
can be any expression that evaluates to a character string ofCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
data type, or a numeric value of typeNUMBER
,BINARY_FLOAT
, orBINARY_DOUBLE
. The optionalfmt
andnlsparam
arguments serve the same purpose as for theTO_BINARY_DOUBLE
function. Refer to TO_BINARY_DOUBLE for more information. -
BINARY_FLOAT
If you specify
BINARY_FLOAT
, thenexpr
can be any expression that evaluates to a character string ofCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
data type, or a numeric value of typeNUMBER
,BINARY_FLOAT
, orBINARY_DOUBLE
. The optionalfmt
andnlsparam
arguments serve the same purpose as for theTO_BINARY_FLOAT
function. Refer to TO_BINARY_FLOAT for more information. -
DATE
If you specify
DATE
, thenexpr
can be any expression that evaluates to a character string ofCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
data type. The optionalfmt
andnlsparam
arguments serve the same purpose as for theTO_DATE
function. Refer to TO_DATE for more information. -
INTERVAL
DAY
TO
SECOND
If you specify
INTERVAL
DAY
TO
SECOND
, thenexpr
can be any expression that evaluates to a character string ofCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
data type, and must contain a value in either the SQL interval format or the ISO duration format. The optionalfmt
andnlsparam
arguments do not apply for this data type. Refer to TO_DSINTERVAL for more information on the SQL interval format and the ISO duration format. -
INTERVAL
YEAR
TO
MONTH
If you specify
INTERVAL
YEAR
TO
MONTH
, thenexpr
can be any expression that evaluates to a character string ofCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
data type, and must contain a value in either the SQL interval format or the ISO duration format. The optionalfmt
andnlsparam
arguments do not apply for this data type. Refer to TO_YMINTERVAL for more information on the SQL interval format and the ISO duration format. -
NUMBER
If you specify
NUMBER
, thenexpr
can be any expression that evaluates to a character string ofCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
data type, a numeric value of typeNUMBER
,BINARY_FLOAT
, orBINARY_DOUBLE
or value of typeBOOLEAN
. The optionalfmt
andnlsparam
arguments serve the same purpose as for theTO_NUMBER
function. Refer to TO_NUMBER for more information.If
expr
is a value of typeNUMBER
, then theVALIDATE_CONVERSION
function verifies thatexpr
is a legal numeric value. Ifexpr
is not a legal numeric value, then the function returns 0. This enables you to identify corrupt numeric values in your database. -
TIMESTAMP
If you specify
TIMESTAMP
, thenexpr
can be any expression that evaluates to a character string ofCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
data type. The optionalfmt
andnlsparam
arguments serve the same purpose as for theTO_TIMESTAMP
function. If you omitfmt
, thenexpr
must be in the default format of theTIMESTAMP
data type, which is determined by theNLS_TIMESTAMP_FORMAT
initialization parameter. Refer to TO_TIMESTAMP for more information. -
TIMESTAMP
WITH
TIME
ZONE
If you specify
TIMESTAMP
WITH
TIME
ZONE
, thenexpr
can be any expression that evaluates to a character string ofCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
data type. The optionalfmt
andnlsparam
arguments serve the same purpose as for theTO_TIMESTAMP_TZ
function. If you omitfmt
, thenexpr
must be in the default format of theTIMESTAMP
WITH
TIME
ZONE
data type, which is determined by theNLS_TIMESTAMP_TZ_FORMAT
initialization parameter. Refer to TO_TIMESTAMP_TZ for more information. -
TIMESTAMP
WITH
LOCAL
TIME
ZONE
If you specify
TIMESTAMP
, thenexpr
can be any expression that evaluates to a character string ofCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
data type. The optionalfmt
andnlsparam
arguments serve the same purpose as for theTO_TIMESTAMP
function. If you omitfmt
, thenexpr
must be in the default format of theTIMESTAMP
data type, which is determined by theNLS_TIMESTAMP_FORMAT
initialization parameter. Refer to TO_TIMESTAMP for more information. -
BOOLEAN
BOOLEAN
is supported as a target type. It supportsNUMBER
type family,VARCHAR
type family, andBOOLEAN
itself as input.
Examples
In each of the following statements, the specified value can be successfully converted to the specified data type. Therefore, each of these statements returns a value of 1.
SELECT VALIDATE_CONVERSION(1000 AS BINARY_DOUBLE) FROM DUAL; SELECT VALIDATE_CONVERSION('1234.56' AS BINARY_FLOAT) FROM DUAL; SELECT VALIDATE_CONVERSION('July 20, 1969, 20:18' AS DATE, 'Month dd, YYYY, HH24:MI', 'NLS_DATE_LANGUAGE = American') FROM DUAL; SELECT VALIDATE_CONVERSION('200 00:00:00' AS INTERVAL DAY TO SECOND) FROM DUAL; SELECT VALIDATE_CONVERSION('P1Y2M' AS INTERVAL YEAR TO MONTH) FROM DUAL; SELECT VALIDATE_CONVERSION('$100,00' AS NUMBER, '$999D99', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL; SELECT VALIDATE_CONVERSION('29-Jan-02 17:24:00' AS TIMESTAMP, 'DD-MON-YY HH24:MI:SS') FROM DUAL; SELECT VALIDATE_CONVERSION('1999-12-01 11:00:00 -8:00' AS TIMESTAMP WITH TIME ZONE, 'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL; SELECT VALIDATE_CONVERSION('11-May-16 17:30:00' AS TIMESTAMP WITH LOCAL TIME ZONE, 'DD-MON-YY HH24:MI:SS') FROM DUAL;
The following statement returns 0, because the specified value cannot be converted to BINARY_FLOAT
:
SELECT VALIDATE_CONVERSION('$29.99' AS BINARY_FLOAT) FROM DUAL;
The following statement returns 1, because the specified number format model enables the value to be converted to BINARY_FLOAT
:
SELECT VALIDATE_CONVERSION('$29.99' AS BINARY_FLOAT, '$99D99') FROM DUAL;