Thursday, April 17, 2008

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.

No comments:

Post a Comment