Reputation: 3
I am updating string to column of length 35 into two tables
first table update was success but second table give ORA error ORA-12899 Error Too large String
select length('Andres Peñalver D1 Palmar Sani salt') bytes from dual;
BYTES
----------
35
select lengthb('Andres Peñalver D1 Palmar Sani salt') bytes from dual;
BYTES
----------
36
Both tables colm1 field declared as VARCHAR(35), first table update fails and second one success.
update t
set colm1='Andres Peñalver D1 Palmar Sani Salt'
where value1='123456';
update t2
set colm1='Andres Peñalver D1 Palmar Sani Salt'
where value1='123456';
ORA-12899
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
AL32UTF8
let me know why this behaviour for these table which is having same column type
Upvotes: 0
Views: 176
Reputation: 303
Check the actual columns size for both the tables in all_tab_columns. 35 Char is 3 times 35 bytes, and if one table's column is defined in char other in byte(during ddl) the size is different. Normal characters like A-Z a-z take 1 byte to store but language specific characters take 3 byte to store.
Upvotes: 0
Reputation: 7892
The full error message as described in the error message documentation should give you the answer:
$ oerr ora 12899
12899, 00000, "value too large for column %s (actual: %s, maximum: %s)"
// *Cause: An attempt was made to insert or update a column with a value
// which is too wide for the width of the destination column.
// The name of the column is given, along with the actual width
// of the value, and the maximum allowed width of the column.
// Note that widths are reported in characters if character length
// semantics are in effect for the column, otherwise widths are
// reported in bytes.
// *Action: Examine the SQL statement for correctness. Check source
// and destination column data types.
// Either make the destination column wider, or use a subset
// of the source column (i.e. use substring).
This is likely linked to character length semantics.
Upvotes: 0