Agreed - why would you need a CHAR(4) for a 1-character substring of a 2-character column? Or for anything else, for that matter? CHAR is provided to look good in ANSI compatibility checklists, you are not supposed to actually use it. 2008/8/18 Stefan Knecht <knecht.stefan@xxxxxxxxx> > Try using VARCHAR2(4) instead of CHAR(4) ? > > Cheers > > Stefan > > > On Mon, Aug 18, 2008 at 3:38 PM, Peter Hitchman <pjhoraclel@xxxxxxxxx>wrote: > >> 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 >> >>