Re: Odd virtual column error

  • From: "William Robertson" <william@xxxxxxxxxxxxxxxxxxxx>
  • To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 18 Aug 2008 15:31:38 +0100

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
>>
>>

Other related posts: