Re: Odd virtual column error

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_at_proquest.com>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_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
>
>
>

-- 
Regards

Pete

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