Re: Strange Oracle error: ORA-12899: value too large for column when creating table with virtual column

Am 18.03.2014 19:07, schrieb Peter Schneider:
> Hi Mladen,
>
> Am 18.03.2014 00:23, schrieb Mladen Gogala:
>> On Mon, 17 Mar 2014 18:01:43 +0100, Peter Schneider wrote:
>>
>>> Hi,
>>>
>>> I have a strange error message when I try to create a table like this
>>> (DDL generated from SQL Data Modeler).
> [...]
>>
>> What seems to be the problem:
> [...}
>> Just kidding. This works in Oracle 11G. It fails in the version 12c:
>
> Well thanks for your comment, but you kind of missed the point, please see my
> other posting. The pattern is that it fails in single byte DB and in UTF-8 DB
> with byte sementacs, while it works in UTF-8 DB with Char sementics. This is
> the same in 11.1, 11.2, 12c.
>
>> The awkward "CAST" is unnecessary, just increasing the size of the
>> virtual column for 1 byte will do the trick.
>
> I know, but that's not what I want. There is no way on earth that this virtual
> column could ever be longer than 9 characters, so I want this to be reflected
> in the data dictionary. I explicitly don't want it to be a V2(10) because it's
> not. That's simple not true. So my workaround with the cast is what I chose to
> do.

After some more thought I must say that there well *is* a way on earth that this column can indeed be 10 characters, and I must really have been blind not to see this.

Of course Oracle is right and I'm wrong: the additional char is for the sign. Obviously I never intend to insert negative numbers (as I'm just modeling application error messages and number like the Oracle error message system), that's why I'm LTRIMming away the single space that's reserved for the sign. But of course, just by itself, it is not guaranteed that message_number is always positive, Oracle can't know this, and even if it were (by using ABS or an inline check constraint), it is still a numeric expression, and as such, a TO_CHAR on it will always account for maximum number of digits+1.

Bummer!

It works with this CAST AS VARCHAR2(9) around it, it will then just truncate the last character, which is fine when I add an additional check constraint CHECK (message_number > 0).

The reason why in an AL32UTF8 DB there is no such error message is because in Unicode, a VARCHAR2(9) offers 36 bytes of storage, and as all numeic digit characters plus the sign are guaranteed to have single-byte code points, the maximum number of bytes is just 10.

Regards
Peter

-- 
Climb the mountain not to plant your flag, but to embrace the challenge,
enjoy the air and behold the view. Climb it so you can see the world,
not so the world can see you.                    -- David McCullough Jr.