There are two types of data type conversions for Oracle: Implicit conversions and explicit conversions. Try to use explicit conversions when you use them.
Implicit conversions are typically Varchar2 or char conversions to number and date, or vice versa. Example,hir_date> ' 1-jan-2014 ' string is implicitly converted to the date type, and select ' 53.5 ' +23 from dual is implicitly converted to the number type for the string.
An explicit conversion:
An explicit type conversion is achieved by to_number,to_char,to_date the conversion of a numeric type to a string type between a date type and a string type.
The return value for To_char is VARCHAR2 type
To_char (date, ' Format_model ') date converted to a string
I. Case sensitive
II. Precede the Format_model with a leading 0 in the FM removable date. such as, To_char (sysdate, ' fmyyyy-mm-dd ').
To_char (number, ' Format_model ') numbers converted to strings
If the format_model is insufficient to accommodate the converted to numeric value, the conversion fails, displaying a string of #.
To_number (char[, ' Format_model ')
To_date (char[, ' Format_model ')) Format_model pre-plus FX indicates an exact match.
Oracle 11g SQL Fundamentals 03--Type conversion function