Hi, Thanks the answers are: AL32UTF8 for the chracter set and BYTE for the NLS_LENGTH_SEMANTICS. So because a single UTF8 character can take 4 bytes I need to specify that number of bytes, makes sense, even if not obvious to me. Thanks Pete On Mon, Aug 18, 2008 at 3:00 PM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>wrote: > I'm thinking along the same lines as Riyaj. In addition to Riyaj's > question, what's the value of NLS_LENGTH_SEMANTICS? > > -Mark > > -- > Mark J. Bobak > Senior Database Administrator, System & Product Technologies > ProQuest > 789 E. Eisenhower, Parkway, P.O. Box 1346 > Ann Arbor MI 48106-1346 > +1.734.997.4059 or +1.800.521.0600 x 4059 > mark.bobak@xxxxxxxxxxxx > www.proquest.com > www.csa.com > > ProQuest...Start here. > > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > On Behalf Of Riyaj Shamsudeen > Sent: Monday, August 18, 2008 9:55 AM > To: pjhoraclel@xxxxxxxxx > Cc: oracle-l > Subject: Re: Odd virtual column error > > Hi Peter > I can't be sure, but this is probably due to your database > characterset. What is your database characterset ? Following query can > be used to retrieve database characterset. > > select * from v$nls_parameters where parameter='NLS_CHARACTERSET'; > > > Cheers > Riyaj > The Pythian Group http://www.pythian.com > Personal : http://orainternals.wordpress.com > > Peter Hitchman wrote: > > Hi, > > Oracle version 11.1.0.6 <http://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 > > > > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Regards Pete