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

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Mon, 17 Mar 2014 23:23:56 +0000 (UTC)
Message-ID: <pan.2014.03.17.23.23.55_at_gmail.com>

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

What seems to be the problem:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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)
  9       );

Table created.

Elapsed: 00:00:03.64

Just kidding. This works in Oracle 11G. It fails in the version 12c:

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)
  9       );
      message_code	   VARCHAR2 (9) AS ( component_short_name || '-'
      *

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

However, the fix is trivial:

"afiedt.buf" 10L, 389C written

  1 CREATE TABLE hot_messages

  2	  (
  3	   component_short_name VARCHAR2 (3)  NOT NULL ,
  4	   message_number	NUMBER (5)  NOT NULL ,
  5	   message_type 	VARCHAR2 (1) DEFAULT 'E'  NOT NULL ,
  6	   message_code 	VARCHAR2 (10) AS ( component_short_name 

|| '-'
  7 || LTRIM(TO_CHAR(message_number, '00000')) ) VIRTUAL NOT NULL ,

  8	   developer_reference	VARCHAR2 (255)
  9*	  )

SQL> / Table created.

The awkward "CAST" is unnecessary, just increasing the size of the virtual column for 1 byte will do the trick.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

Received on Tue Mar 18 2014 - 00:23:56 CET