Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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)??
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.