Odd virtual column error

From: Peter Hitchman <pjhoraclel_at_gmail.com>
Date: Mon, 18 Aug 2008 14:38:21 +0100
Message-ID: <5e317f310808180638s4b5a1a57ia22da06ca80f4f05@mail.gmail.com>


Hi,
Oracle version 11.1.0.6

Any ideas why when I run this ddl:

CREATE TABLE PILOT1_METADATA
(
 PATENT_SK NUMBER(9) NOT NULL
,COLLECTION_ID VARCHAR2(8) NOT NULL
,PDOC_COUNTRY CHAR(2) NOT NULL
,PDOC_SERIAL NUMBER(12) NOT NULL
,PDOC_KIND VARCHAR2(2) NOT NULL
,KI_SHORT CHAR(4) GENERATED ALWAYS AS (SUBSTR(PDOC_KIND,1,1)) VIRTUAL
,PUBLICATION_DATE DATE NOT NULL
,PUBLICATION_WEEK NUMBER(6) NOT NULL
,PUBLICATION_YEAR NUMBER(4) NOT NULL
,APPLICATION_NUMBER VARCHAR2(15) NOT NULL
,APPLICATION_DATE DATE NOT NULL
,STATUS VARCHAR2(3)
,MOD_DATE DATE

)
/

the result is:

ORA-12899: value too large for column "KI_SHORT" (actual: 1, maximum: 4)

I changed the column definition of PDOC_KIND to CHAR(4) and the table builds and then an insert creates the correct virtual data.

Thanks

Pete

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 18 2008 - 08:38:21 CDT

Original text of this message