RE: Odd virtual column error

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_at_proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.


-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Riyaj Shamsudeen
Sent: Monday, August 18, 2008 9:55 AM
To: pjhoraclel_at_gmail.com
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

>

>


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l