Re: Odd virtual column error
Date: Mon, 18 Aug 2008 16:19:49 +0200
Message-ID: <486b2b610808180719j413d3523sbb4775ecf569e55@mail.gmail.com>
Try using VARCHAR2(4) instead of CHAR(4) ?
Cheers
Stefan
On Mon, Aug 18, 2008 at 3:38 PM, Peter Hitchman <pjhoraclel_at_gmail.com>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
>
>
>
-- ========================= Stefan P Knecht Senior Consultant Infrastructure Managed Services Trivadis AG Europa-Strasse 5 CH-8152 Glattbrugg Phone +41-44-808 70 20 Fax +41-808 70 12 Mobile +41-79-571 36 27 stefan.knecht_at_trivadis.com http://www.trivadis.com OCP 9i/10g SCSA SCNA ========================= -- http://www.freelists.org/webpage/oracle-lReceived on Mon Aug 18 2008 - 09:19:49 CDT