Monday, August 05, 2013

ORA-12899

If you try to update a column with a value which is too big, you get an ORA-12899. If this happens in a PL/SQL block, all the updates done in that block will be rolled back, not just the one which failed. You can see what I mean in the example below, which I ran on an Oracle 11.2 database:

SQL> create table tab1 (first_name varchar2(10))
  2  /

Table created. 

SQL> insert into tab1 values ('Brian')
  2  /

1 row created.

SQL> insert into tab1 values ('Colin')
  2  /

1 row created.

SQL> begin
  2   update tab1
  3    set first_name = 'Boris'
  4     where first_name = 'Brian';
  5   update tab1
  6    set first_name = 'Christopher'
  7     where first_name = 'Colin';
  8  end;
  9  /
begin
*
ERROR at line 1:
ORA-12899: value too large for column
"ANDREW"."TAB1"."FIRST_NAME" (actual: 11, maximum: 10)
ORA-06512: at line 5

SQL> select * from tab1
  2  /

FIRST_NAME
----------
Brian
Colin

SQL>

No comments: