ORA-12899 Creating a Table With Virtual Columns

(Doc ID 1516303.1)

Last updated on MARCH 01, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 01-Mar-2017***

Symptoms

Data Pump import for tables with virtual columns fails with:
ORA-39083: Object type TABLE:"RECUPERA"."SIRE_VISITDATE" failed to create with error:
ORA-12899: value too large for column "VISIT_DATE_D" (actual: 8, maximum: 75)
Failing sql is:
CREATE TABLE "RECUPERA"."SIRE_VISITDATE" ("CODPAT" NUMBER, "USERID_INS" VARCHAR2(40 CHAR), "ESAM" NUMBER, "PROGR" NUMBER, "VISITNUM" NUMBER, "VISITNUM_PROGR" NUMBER, "CENTER" VARCHAR2(40 CHAR), "VISIT_DATE" DATE, "VISIT_DATERC" VARCHAR2(8 CHAR), "VISIT_DATE_D" VARCHAR2(2 CHAR) GENERATED ALWAYS AS (DECODE(SUBSTR("VISIT_DATERC",1,2)

and

ORA-39083: Object type TABLE:"RECUPERA"."SIRE_VD" failed to create with error:
ORA-12899: value too large for column "VISDAT_D" (actual: 8, maximum: 75)
Failing sql is:
CREATE TABLE "RECUPERA"."SIRE_VD" ("CODPAT" NUMBER, "USERID_INS" VARCHAR2(40 CHAR), "ESAM" NUMBER, "PROGR" NUMBER, "VISITNUM" NUMBER, "VISITNUM_PROGR" NUMBER, "CENTER" VARCHAR2(40 CHAR), "SUBJID" VARCHAR2(14 CHAR), "VISDAT" DATE, "VISDATRC" VARCHAR2(8 CHAR), "VISDAT_D" VARCHAR2(2 CHAR) GENERATED ALWAYS AS (DECODE(SUBSTR("VISDATRC",1,2),'OK',

The same import in 11.1.0.7 is successful.

The errors are NOT impdp specific because the same errors are returned when trying to create the tables in SQL*Plus:

SQL> connect backupadm@db41
Connected.

SQL> set echo on
SQL> @/nfs/dbc-fs_oradump2/db04/dump/ddls.sql
SQL> CREATE TABLE "RECUPERA"."SIRE_VISITDATE"
 2 ( "CODPAT" NUMBER,
 3 "USERID_INS" VARCHAR2(40 CHAR),
 4 "ESAM" NUMBER,
 5 "PROGR" NUMBER,
 6 "VISITNUM" NUMBER,
 7 "VISITNUM_PROGR" NUMBER,
 8 "CENTER" VARCHAR2(40 CHAR),
 9 "VISIT_DATE" DATE,
10 "VISIT_DATERC" VARCHAR2(8 CHAR),
11 "VISIT_DATE_D" VARCHAR2(2 CHAR) GENERATED ALWAYS AS (DECODE(SUBSTR("VISIT_DATERC",1,2),'OK',TO_CHAR("VISIT_DATE",'DD'),'',TO_CHAR("VISIT_DATE",'DD'),'NA','NA','NK','NK')) VIRTUAL VISIBLE ,
12 "VISIT_DATE_M" VARCHAR2(2 CHAR) GENERATED ALWAYS AS (DECODE(SUBSTR("VISIT_DATERC",3,2),'OK',TO_CHAR("VISIT_DATE",'MM'),'',TO_CHAR("VISIT_DATE",'MM'),'NA','NA','NK','NK')) VIRTUAL VISIBLE ,
13 "VISIT_DATE_Y" VARCHAR2(4 CHAR) GENERATED ALWAYS AS (DECODE(SUBSTR("VISIT_DATERC",5,2),'OK',TO_CHAR("VISIT_DATE",'YYYY'),'',TO_CHAR("VISIT_DATE",'YYYY'),'NA','NA','NK','NK')) VIRTUAL VISIBLE
14 ) SEGMENT CREATION IMMEDIATE
15 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
16 NOCOMPRESS LOGGING
17 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
18 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
19 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
20 TABLESPACE "RECUPERA" ;
"VISIT_DATE_D" VARCHAR2(2 CHAR) GENERATED ALWAYS AS (DECODE(SUBSTR("VISIT_DATERC",1,2),'OK',TO_CHAR("VISIT_DATE",'DD'),'',TO_CHAR("VISIT_DATE",'DD'),'NA','NA','NK','NK')) VIRTUAL VISIBLE ,
*
ERROR at line 11:
ORA-12899: value too large for column "VISIT_DATE_D" (actual: 8, maximum: 75)

Changes

Using Oracle 11.2

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms