ORA-01401, ORA-12899 and ORA-01438

My customer recently upgraded some of its databases from Oracle 9.2.0.7 to 10.2.0.3, so when my colleague and me saw error message ORA-12899 after an insert statement, it came as a surprise. But it was a pleasant surprise.

In 9.2.0.7 we were used to see a ORA-01401 in the situation where you'd insert a varchar2 value that doesn't fit into a column because it's length is too big. And to its counterpart, the ORA-01438, in case of a number instead of a varchar2.

The following SQL script was executed on a 9.2.0.8 database:

SQL> create table t
  2  ( id number(2)
  3  , name varchar2(2)
  4  )
  5  /
 
Table created.
 
SQL> insert into t values (99,'AA')
  2  /
 
1 row created.
 
SQL> insert into t values (99,'AAA')
  2  /
insert into t values (99,'AAA')
                         *
ERROR at line 1:
ORA-01401: inserted value too large for column


SQL> insert into t values (101,'AA')
  2  /
insert into t values (101,'AA')
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

And on a 10.2.0.3 database:

SQL> create table t
  2  ( id number(2)
  3  , name varchar2(2)
  4  )
  5  /
 
Table created.

SQL> insert into t values (99,'AA')
  2  /
 
1 row created.
 
SQL> insert into t values (99,'AAA')
  2  /
insert into t values (99,'AAA')
                         *
ERROR at line 1:
ORA-12899: value too large for column "RWIJK"."T"."NAME" (actual: 3, maximum: 2)


SQL> insert into t values (101,'AA')
  2  /
insert into t values (101,'AA')
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

ORA-12899: now that's what I call a small but lovely improvement. You no longer have to wonder what column in your 100-column-table the error message is complaining about. It's all right there in the error message.

The ORA-01401 has been removed from the documentation in version 10, and ORA-12899 became its substitute. Unfortunately, as can be seen above, the ORA-01438 hasn't been given a same facelift.

I also checked 11.1.0.6:

rwijk@ORA11G> create table t
  2  ( id number(2)
  3  , name varchar2(2)
  4  )
  5  /

Tabel is aangemaakt.

rwijk@ORA11G> insert into t values (99,'AA')
  2  /

1 rij is aangemaakt.

rwijk@ORA11G> insert into t values (99,'AAA')
  2  /
insert into t values (99,'AAA')
                         *
FOUT in regel 1:
.ORA-12899: waarde te groot voor kolom "RWIJK"."T"."NAME" (huidige waarde: 3, maximumwaarde: 2)


rwijk@ORA11G> insert into t values (101,'AA')
  2  /
insert into t values (101,'AA')
                      *
FOUT in regel 1:
.ORA-01438: Waarde is groter dan de voor deze kolom opgegeven maximale precisie.

So no changes from 10 to 11: still ORA-01438 and ORA-12899.

A google search afterwards revealed that this has been "discovered" many times before, but it was new to me.