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

From: Peter Schneider <pschneider1968_at_googlemail.com>
Date: Mon, 17 Mar 2014 18:01:43 +0100
Message-ID: <lg79po$oq9$1_at_online.de>



Hi,

I have a strange error message when I try to create a table like this (DDL generated from SQL Data Modeler).

The interesting thing is: on a AL32UTF8 DB with NLS_LENGTH_SEMANTICS = CHAR, this works. On a single byte WIN1252 DB, this fails.

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 ( component_short_name || '-' 
|| LTRIM(TO_CHAR(message_number, '00000')) ) VIRTUAL  NOT NULL ,
      developer_reference  VARCHAR2 (255)
     );

ERROR at line 6:
ORA-12899: value too large for column "MESSAGE_CODE" (actual: 9, maximum: 10)

If I can count correctly (and I have strong evidence I can!), the length of the virtual column expression is exactly 9.

Does anybody have an explanation for this behaviour?

Thanks and 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.
Received on Mon Mar 17 2014 - 18:01:43 CET

Original text of this message