Oracle implicit conversion and explicit conversion ____oracle

conditions for the occurrence of an implicit conversion

When the type of the source data and the type of the target data are different, if there is no conversion function, an implicit conversion occurs, also known as automatic conversion. Of course, there are cases in which some types cannot be converted, such as converting from a date type to a number type.

problems with implicit conversions

First, the biggest problem with implicit conversions is that conversion results in an invalid index, which can result in full table scans. When the amount of data in a table is very large, there will be a great performance problem. For example, the performance problems caused by VARCHAR2 and NVARCHAR2 implicit data type conversions:

http://www.oracleonlinux.cn/2012/07/varchar_nvarchar_implicit_change_data_type/

Second, because implicit conversions make it difficult for database programmers and DBAs to understand what type conversions are happening, and if the code is very long, it takes a lot of effort to detect errors. For example, each function has input parameters, and if the data type of the input data of the function is different from the data type required by the input parameter of the function, an implicit conversion will occur, if the two types cannot be converted between the words will be an error. Because of the fact that its errors have been quite concealed, it is quite troublesome to find them.

solution to the problem

First: Use conversion functions, that is, explicit conversions.

Each data type has a set of functions that are specifically processed for that type of data. such as To_char,to_clob and so on. Of these, there are three most commonly used, that is, the conversion between numeric types, character types, and date-type data. As follows:

To_char: Converts date or number to a string;

To_date: Converts number, char, or VARCHAR2 to DATE. When you use a timestamp, you can use To_timestamp or To_timestamp_tz.

To_number: Converts char or VARCHAR2 to number. It should be noted that the date type cannot be converted to the number type;

Second: Avoid implicit conversions.

When designing a database, it is best to follow the principle of "what type of data is it, what type of it is designed", for example, not to design date-type data as character types.

Another principle is to use functions in relatively safe places, such as using string-processing functions for numeric values, and not using arithmetic operations on strings.

In addition, in order to facilitate the subsequent inspection of the program, it is best to mark the appropriate location of the data type of automatic conversion.

For Oracle data types, refer to the following article:

http://ajita.iteye.com/blog/1424143