Oracle implicit data conversion rules

Oracle implicit data conversion rules The following rules govern the direction inwhich Oracle makes implicit data type conversions (in oracle, if different data types are associated, if The data is not explicitly converted, it will implicitly convert data according to the following rules): 1) During INSERT and UPDATE operations, Oracle converts the value tothe datatype of the affected column (for INSERT and UPDATE operations, oracle implicitly converts the inserted value or updated value to the Data Type of the field. For example, if the data type of the id column is numberupdate t set id = '1 '; -> equivalent to update t set id = to_number ('1'); insert in To t (id) values ('1')-> insert into t values (to_number ('1'); 2) During select from operations, oracle converts the data from thecolumn to the type of the target variable (for SELECT statements, oracle implicitly converts the field data type to the variable data type ). for example, assume that the data type of the id column is varchar2select * from t where id = 1;-> select * from t where to_number (id) = 1; however, if the data type of the id column is number, select * from t where id = '1';-> select * from t where id = to_number ('1'); 3) W Hen comparing a character value with a NUMBER value, Oracleconverts the character data to NUMBER (when comparing a numeric value with a numeric value, oracle implicitly converts the numeric value to a numeric value ). for example, assume that the data type of the id column is numberselect * from t where id = '1';-> select * from t where id = to_number ('1'); 4) when comparing a character value with a DATE value, Oracle convertsthe character data to DATE (oracle converts the character type to the DATE type When comparing the character type and DATE type data ). for example, if create_date is of the limit type, select * fromt Where create_date> sysdate;-> select * from t whereto_date (create_date)> sysdate; (Note: At this time, the session nls_date_format must be consistent with the string format) assume that create_date is of the date type select * fromt where create_date> '2017-11-11 11:11:11 ';-> select * from t wherecreate_date> to_date ('2017-11-11 11:11:11 '); (Note: At this time, the session nls_date_format must be consistent with the string format) 5) When you use a SQL function or operator with an argument of adatatype other than the one it Accepts, Oracle converts the argument to theaccepted datatype. (if the data type of the input parameter is different from that of the function or process, oracle converts the Data Type of the input parameter to a function or a data type defined in the process. For example, the following process defines p (p_1 number) exec p ('1 '); -> exec p (to_number ('1'); 6) When making assignments, Oracle converts the value on the right sideof the equal sign (=) to the datatype of the target of the assignment on theleft side (oracle converts the data type on the right of the equal sign to the data type on the left ). example: v Ar a numbera: = '1';-> a: = to_number ('1'); 7) During concatenation operations, oracle converts from noncharacterdatatypes to CHAR or NCHAR (when using the concatenation operator (|), oracle converts non-character data to character type ). for example, select 1 | '2' from dual;-> select to_char (1) | '2' from dual; 8) During arithmetic operations on and comparisons between characterand noncharacter datatypes, oracle converts from any character datatype to anumber, date, or Rowid, as appropriate. in arithmetic operations betweenCHAR/VARCHAR2 and NCHAR/NVARCHAR2, Oracle converts to a number. if data of the character type and data of the non-character type (such as number, date, and rowid) are used for arithmetic operations, oracle converts the data of the character type to the appropriate data type, these data types may be number, date, and rowid. If an arithmetic operation is performed between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, oracle converts all of them to number type data for comparison. 9) Comparisons between CHAR/VARCHAR2 and NCHAR/NVARCHAR2 types mayentail different character sets. the default direction of conversion in suchcases is from the database character set to the national character set. when comparing CHAR/VARCHAR2 and NCHAR/NVARCHAR2, if the character sets are different, the default conversion method is to convert the data encoding from the database character set to the national character set.