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

Am 17.03.2014 18:18, schrieb joel garry:
> On Monday, March 17, 2014 10:01:43 AM UTC-7, Peter Schneider wrote:
>
>>
>> Does anybody have an explanation for this behaviour?
>
> Bug? See MOS ORA-12899: Value Too Large For Column when using Deterministic Function (Doc ID 1599351.1) and ORA-12899 Creating a Table With Virtual Columns (Doc ID 1516303.1) and related links in those.

Yes, that seems to be it - bugs 16066239 and 13640613. However the bug descriptions seem not quite accurate as to in what scenarios this occurs. For me, the error reproduces always in a single byte DB, and with byte semantics in an Al32UTF8 DB. With char semantics it works. I've tested it with 11.1.07, 11.2.0.4 and 12.1.0.1 and they are all the same.

The MOS states "Fixed in 12.2", so I think I'll not hold my breath ;-)

As a workaround, I seem to be able to use a cast expression around the virtual column, and this seems to work:

CREATE TABLE hot_messages

     (
      component_short_name VARCHAR2 (3)  NOT NULL ,
      message_number       NUMBER (5)  NOT NULL ,
      message_type         VARCHAR2 (1) DEFAULT 'E'  NOT NULL ,
      message_code         VARCHAR2 (9) AS ( CAST(component_short_name || '-' 
|| LTRIM(TO_CHAR(message_number, '00000')) AS VARCHAR2(9) )) VIRTUAL  NOT NULL ,
      developer_reference  VARCHAR2 (255)
     );

Thanks
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.