Re: Odd virtual column error
- From: "Peter Hitchman" <pjhoraclel@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 18 Aug 2008 15:50:13 +0100
Hi,
Sorrry all , I posted the wrong DDL
the original column definition was CHAR(1), when its CHAR(4) or
VARCHAR2(4), it works.
As Riyaj and Mark have indicated it is probably because of the UTF8
character set.
Regards
Pete
On Mon, Aug 18, 2008 at 3:31 PM, William Robertson <
william@xxxxxxxxxxxxxxxxxxxx> wrote:
> 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
>>>
>>>
>
--
Regards
Pete
Other related posts:
