Oracle ORA-12899 Error – How to Resolve When Creating Varchar Virtual Column Based on Numeric Column

oracleoracle-11g-r2

I have a table with numeric column:

create table test (a number(2,0));

Next I want to create a virtual column based on column a:

alter table test add (b varchar2(3) as ('A:'||to_char(a)));

This results in error:

ORA-12899: value too large for column "B" (actual: 12, maximum: 42)

Why do I get this error? This works if I replace to_char with substr, but I'm curious how does Oracle determine the "actual" and "maximum" values?

I suspect the maximum value is related to maximum precision of numeric types (Oracle ignoring the data type precision), but I still don't quite understand this error. Character set is UTF-8.

Best Answer

The error message is somewhat strange as presented, but when you get this message creating a virtual column, the value for maximum is the minimum length that you need to specify to be able to create the column.

The reason for this is that Oracle doesn't know the maximum length of the expression to_char(a) and assumes that it will be 40 characters: 38 places of precision for the number, plus 1 for each of the decimal point and positive/negative indicator.

Add 2 characters for "A:" and the minimum length that Oracle judges as "safe" is 42.

Related Question