You Asked

Dear All,

Database Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

I have a table XX_FT with 2 column as below

REF_NO VARCHAR2(100)

This column can have two variations of data
"XX17010XXXXXX"
"YYY17010XXXXXX"
"XX17010XXXXXX" - wherein 17 is the year and 010 is the nth day of the respective year
Similarly "YYY17010XXXXXX" - wherein 17 is the year and 010 is the nth day of the respective year

REF_DATE DATE GENERATED ALWAYS AS (CASE 
                                     WHEN REF_NO LIKE 'XX%'
                                     THEN TRUNC(TO_DATE(SUBSTR(REF_NO,3,2),'YY'),'YEAR')+(TO_NUMBER(SUBSTR(REF_NO,5,3))-1)
                                     ELSE TRUNC(TO_DATE(SUBSTR(REF_NO,4,2),'YY'),'YEAR')+(TO_NUMBER(SUBSTR(REF_NO,6,3))-1)
                                   END)

This second column is a Virtual Column
This has to be used as a date range partition column too

After having this setup, I tried loading the table using the basic insert...select.../merge which works fine. But when trying to load using SQLLDR or IMP/EXP dump i get the following error

ORA-39776: fatal Direct Path API error loading table XX_FT  
ORA-01839: date not valid for month specified  

SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

So I tried removing the partition which seems to have worked. But I am confused why I get this error when using the Virtual Column as partition and loaded using SQLLDR?

and Connor said...

We'd need to see more details, including the sqlldr ones.

The virtual column is only evaluated as required, so its easy to allow incorrect data into a table , only to have the error come out later


SQL> create table t (
  2    REF_NO VARCHAR2(100) ,
  3    REF_DATE DATE GENERATED ALWAYS AS (CASE
  4                                         WHEN REF_NO LIKE 'XX%'
  5                                         THEN TRUNC(TO_DATE(SUBSTR(REF_NO,3,2),'YY'),'YEAR')+(TO_NUMBER(SUBSTR(REF_NO,5,3))-1)
  6                                         ELSE TRUNC(TO_DATE(SUBSTR(REF_NO,4,2),'YY'),'YEAR')+(TO_NUMBER(SUBSTR(REF_NO,6,3))-1)
  7                                       END)
  8  );

Table created.

SQL>
SQL>
SQL> insert into t ( REF_NO ) values ( 'XX1720' );

1 row created.

SQL> insert into t ( REF_NO ) values ( 'YYY1720' );

1 row created.

SQL> insert into t ( REF_NO ) values ( 'YYYZ1720' );

1 row created.

SQL> insert into t ( REF_NO ) values ( 'asdasd' );

1 row created.

SQL>
SQL> insert /*+ append */ into t ( ref_no )
  2  select ref_no from t;

4 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> select * from t;
ERROR:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

If you use that virtual column for partitioning, then evaluation will be done earlier so we can locate the right partition

SQL>
SQL> drop table t purge;

Table dropped.

SQL> create table t (
  2    REF_NO VARCHAR2(100) ,
  3    REF_DATE DATE GENERATED ALWAYS AS (CASE
  4                                         WHEN REF_NO LIKE 'XX%'
  5                                         THEN TRUNC(TO_DATE(SUBSTR(REF_NO,3,2),'YY'),'YEAR')+(TO_NUMBER(SUBSTR(REF_NO,5,3))-1)
  6                                         ELSE TRUNC(TO_DATE(SUBSTR(REF_NO,4,2),'YY'),'YEAR')+(TO_NUMBER(SUBSTR(REF_NO,6,3))-1)
  7                                       END)
  8  )
  9  partition by range ( ref_date )
 10  (
 11    partition p1 values less than ( date '2017-01-01' ),
 12    partition p2 values less than ( date '2018-01-01' )
 13  )  ;

Table created.

SQL>
SQL> insert into t ( REF_NO ) values ( 'XX1720' );

1 row created.

SQL> insert into t ( REF_NO ) values ( 'YYY1720' );

1 row created.

SQL> insert into t ( REF_NO ) values ( 'YYYZ1720' );
insert into t ( REF_NO ) values ( 'YYYZ1720' )
                                             *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


SQL> insert into t ( REF_NO ) values ( 'asdasd' );
insert into t ( REF_NO ) values ( 'asdasd' )
                                           *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


SQL>

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alex, October 18, 2018 - 4:23 pm UTC

Can you guys please help me figure out what is wrong with this syntax?

CREATE TABLE CONFIG_VERSIONS
   (    ID NUMBER(19,0) NOT NULL ENABLE,
        CHANGENOTES VARCHAR2(1000 CHAR),
        CONFIGDATA CLOB,
        PARENT NUMBER(10,0),
        PRODUCTION_STATUS VARCHAR2(255 CHAR) NOT NULL ENABLE,
        SECURE NUMBER(1,0),
        STAGING_STATUS VARCHAR2(255 CHAR) NOT NULL ENABLE,
        TIMESTAMP DATE NOT NULL ENABLE,
        USER_ID VARCHAR2(255 CHAR) NOT NULL ENABLE,
        USERDATA CLOB,
        VERSIONNUM NUMBER(10,0) NOT NULL ENABLE,
        PROPERTY_ID NUMBER(19,0) NOT NULL ENABLE,
        HIDDEN NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
        PRODUCT_VERSION_ID_ID NUMBER(19,0) DEFAULT null NOT NULL ENABLE,
        CONFIG_VERSION NUMBER(10,0) DEFAULT null,
        CATALOG_VERSION VARCHAR2(255 CHAR) DEFAULT null,
        PARENT_ID NUMBER(19,0),
        LAST_SAVED_STATE VARCHAR2(255 CHAR) DEFAULT 'UNKNOWN' NOT NULL ENABLE,
        RULE_FORMAT VARCHAR2(100 CHAR) DEFAULT 'latest',
        VALIDATION_RESULTS CLOB,
        ENABLE_OPTIMIZED_XML NUMBER(1,0),
        DELETE_STATUS GENERATED ALWAYS AS (CASE WHEN production_status NOT IN ('ACTIVE', 'PENDING', 'PENDING_DEACTIVATION') 
                                            AND staging_status NOT IN ('ACTIVE', 'PENDING', 'PENDING_DEACTIVATION')
                                           THEN 'DELETE_AVAILABLE'
                                           ELSE 'DELETE_UNAVAILABLE'
                                            END
                                           ) virtual
)
 ROW STORE COMPRESS ADVANCED LOGGING
 LOB (CONFIGDATA) STORE AS SECUREFILE (COMPRESS HIGH  DEDUPLICATE LOB)
 LOB (USERDATA) STORE AS SECUREFILE (COMPRESS HIGH  DEDUPLICATE LOB)
 LOB (VALIDATION_RESULTS) STORE AS SECUREFILE (COMPRESS HIGH  DEDUPLICATE LOB)
 PARTITION BY LIST(DELETE_STATUS) 
 SUBPARTITION BY RANGE(TIMESTAMP)
( 
  PARTITION list01 values ('DELETE_AVAILABLE'),
  (
   SUBPARTITION timestamp01 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')),
   SUBPARTITION timestamp02 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')), 
   SUBPARTITION timestamp03 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
   SUBPARTITION timestamp04 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
   SUBPARTITION timestamp05 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
   SUBPARTITION timestamp06 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')), 
   SUBPARTITION timestamp07 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY'))
  )
  PARTITION list02 values ('DELETE_UNAVAILABLE')
  (
   SUBPARTITION timestamp08 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')),
   SUBPARTITION timestamp09 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')),
   SUBPARTITION timestamp10 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
   SUBPARTITION timestamp11 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
   SUBPARTITION timestamp12 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
   SUBPARTITION timestamp13 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')), 
   SUBPARTITION timestamp14 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY'))
  )
);

Error report -
ORA-14004: missing PARTITION keyword
14004. 00000 -  "missing PARTITION keyword"
*Cause:    keyword PARTITION missing
*Action:   supply missing keyword

Thank you.

October 19, 2018 - 10:58 pm UTC

One too many commas :-)



SQL> CREATE TABLE CONFIG_VERSIONS
  2     (    ID NUMBER(19,0) NOT NULL ENABLE,
  3          CHANGENOTES VARCHAR2(1000 CHAR),
  4          CONFIGDATA CLOB,
  5          PARENT NUMBER(10,0),
  6          PRODUCTION_STATUS VARCHAR2(255 CHAR) NOT NULL ENABLE,
  7          SECURE NUMBER(1,0),
  8          STAGING_STATUS VARCHAR2(255 CHAR) NOT NULL ENABLE,
  9          TIMESTAMP DATE NOT NULL ENABLE,
 10          USER_ID VARCHAR2(255 CHAR) NOT NULL ENABLE,
 11          USERDATA CLOB,
 12          VERSIONNUM NUMBER(10,0) NOT NULL ENABLE,
 13          PROPERTY_ID NUMBER(19,0) NOT NULL ENABLE,
 14          HIDDEN NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
 15          PRODUCT_VERSION_ID_ID NUMBER(19,0) DEFAULT null NOT NULL ENABLE,
 16          CONFIG_VERSION NUMBER(10,0) DEFAULT null,
 17          CATALOG_VERSION VARCHAR2(255 CHAR) DEFAULT null,
 18          PARENT_ID NUMBER(19,0),
 19          LAST_SAVED_STATE VARCHAR2(255 CHAR) DEFAULT 'UNKNOWN' NOT NULL ENABLE,
 20          RULE_FORMAT VARCHAR2(100 CHAR) DEFAULT 'latest',
 21          VALIDATION_RESULTS CLOB,
 22          ENABLE_OPTIMIZED_XML NUMBER(1,0),
 23          DELETE_STATUS GENERATED ALWAYS AS (CASE WHEN production_status NOT IN ('ACTIVE', 'PENDING', 'PENDING_DEACTIVATION')
 24                                              AND staging_status NOT IN ('ACTIVE', 'PENDING', 'PENDING_DEACTIVATION')
 25                                             THEN 'DELETE_AVAILABLE'
 26                                             ELSE 'DELETE_UNAVAILABLE'
 27                                              END
 28                                             ) virtual
 29  )
 30   ROW STORE COMPRESS ADVANCED LOGGING
 31   LOB (CONFIGDATA) STORE AS SECUREFILE (COMPRESS HIGH  DEDUPLICATE LOB)
 32   LOB (USERDATA) STORE AS SECUREFILE (COMPRESS HIGH  DEDUPLICATE LOB)
 33   LOB (VALIDATION_RESULTS) STORE AS SECUREFILE (COMPRESS HIGH  DEDUPLICATE LOB)
 34   PARTITION BY LIST(DELETE_STATUS)
 35   SUBPARTITION BY RANGE(TIMESTAMP)
 36  (
 37    PARTITION list01 values ('DELETE_AVAILABLE')
 38    (
 39     SUBPARTITION timestamp01 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')),
 40     SUBPARTITION timestamp02 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')),
 41     SUBPARTITION timestamp03 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
 42     SUBPARTITION timestamp04 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
 43     SUBPARTITION timestamp05 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
 44     SUBPARTITION timestamp06 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
 45     SUBPARTITION timestamp07 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY'))
 46    ),
 47    PARTITION list02 values ('DELETE_UNAVAILABLE')
 48    (
 49     SUBPARTITION timestamp08 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')),
 50     SUBPARTITION timestamp09 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')),
 51     SUBPARTITION timestamp10 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
 52     SUBPARTITION timestamp11 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
 53     SUBPARTITION timestamp12 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
 54     SUBPARTITION timestamp13 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
 55     SUBPARTITION timestamp14 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY'))
 56    )
 57  );

Table created.

SQL>

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

VLDB

VLDB

Virtual Date Column as paritition doesn't work

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

VLDB

VLDB