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