Implicit Conversions for INSERT and UPDATE Statements

Implicit data conversions (coercions) are performed for INSERT and UPDATE statements for both assignments and expressions specified in statements. Data type conversion rules are applied to any expression (function call, operator, etc) used in an INSERT or an UPDATE statement. For more information on data type conversion rules, see Data Type Conversion.

Strict implicit conversion rules are applied to all column types; an error is returned if the assigned value cannot be converted to the type of the target column without loss of information. However for columns of number and string types, implicit conversions are performed differently.

Number Types

For numbers, the assigned value is implicitly rounded to the type of the column. For example:

SQL> CREATE TABLE Table1(n smallint);
SQL> INSERT INTO Table values(3.4), ('3.6');
SQL> SELECT * FROM Table1;

N
--
3
4

If the rounded value does not fit into the column type, an overflow error is returned. For example:

SQL> INSERT INTO Table1 values(40000);
Numeric overflow converting integer 40000 to smallint

SQL> INSERT INTO Table1 values('40000');
Numeric overflow converting string 40000 to smallint

SQL> CREATE TABLE Table2(n numeric(3, 2));
SQL> INSERT INTO Table2 values(1.446);
SQL> SELECT * FROM Table2;

 N
----
1.45

SQL> INSERT INTO Table2 values(9.999);
Numeric Overflow: 10 does not fit in precision, scale (3, 2)

CHAR and VARCHAR Types

The specified length of the CHAR and VARCHAR types represents the number of UTF-8 characters supported by the type. For example:

SQL> CREATE TABLE Table1(c varchar(2));
SQL> INSERT INTO Table1(c) values ('ëë');
SQL> INSERT INTO Table1(c) values ('aa');
SQL> SELECT * FROM Table1;

 C
----
'ëë'
 aa

SQL> INSERT INTO Table1 (c) values('aaa');
overflow converting "abcd" to varchar(2)

Length is represented by characters not bytes, for example ë has two bytes but only one character whereas a has both one byte and 1 character.

If the column type has a maximum length specified (that is, a char or a varchar type), an overflow error is returned when the value is greater than the number of characters specified by the column definition. For example:

SQL> CREATE TABLE Table1(c char(3));
SQL> INSERT INTO Table1(c) values('abcd');
overflow converting "abcd" to char(3)