ORA-12899 when creating varchar virtual column based on numeric column

Question

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.

Answer 1

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.

Answer 2

Use a format with your to_char:

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

Table created

SQL> alter table test add (b varchar2(4) as ('A:'||to_char(a, 'fm99')));

Table altered

SQL> insert into test (a) values (99);

1 row inserted

SQL> select * from test;

  A B
--- ------------
 99 A:99

Interestingly, Oracle doesn't seem to check the actual length in this case in version 11.2.0.3.0:

SQL> insert into test (a) values (-99);

1 row inserted

SQL> select * from test;

  A B
--- ------------
 99 A:99
-99 A:-99

SQL> desc test

Name Type        Nullable Default                   Comments 
---- ----------- -------- ------------------------- -------- 
A    NUMBER(2)   Y                                           
B    VARCHAR2(4) Y        'A:'||TO_CHAR("A",'fm99') -- should be VARCHAR2(5)??