ORA-12899 Creating a Table With Virtual Columns
(Doc ID 1516303.1)
Last updated on JANUARY 30, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and laterOracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Data Pump import for tables with virtual columns fails with:
ORA-39083: Object type TABLE:"<SCHEMA>"."TEST" failed to create with error:
ORA-12899: value too large for column "VISIT_DATE_D" (actual: 8, maximum: 75)
Failing sql is:
CREATE TABLE "<SCHEMA>"."TEST" ("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:"<SCHEMA>"."TEST" failed to create with error:
ORA-12899: value too large for column "VISDAT_D" (actual: 8, maximum: 75)
Failing sql is:
CREATE TABLE "<SCHEMA>"."TEST" ("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> @/<DIR>/dump/ddls.sql
SQL> CREATE TABLE "<SCHEMA>"."TEST"
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |