Oracle - CREATE NLS_CALENDAR Table

Hi

In this entry i will explain how can we create a NLS_CALENDAR table  , which can be used in ETL procedures as a lookup table.

At first lets create our table :

(
Table contains DAY , MONTH,YEAR and FULL_DATE values
Partition by YEAR and  Subpartitioned by MONTH
)

CREATE  TABLE   NLS_CALENDAR_TABLE
(
DAY  NUMBER,
MONTH  NUMBER,
YEAR NUMBER,
FULL_DATE  DATE
)
PARTITION BY RANGE ( YEAR )
        SUBPARTITION BY LIST (MONTH)
                SUBPARTITION TEMPLATE
                (
                        SUBPARTITION  MONTH1  VALUES(1),
                        SUBPARTITION  MONTH2  VALUES(2),
                        SUBPARTITION  MONTH3  VALUES(3),
                        SUBPARTITION  MONTH4  VALUES(4),
                        SUBPARTITION  MONTH5  VALUES(5),
                        SUBPARTITION  MONTH6  VALUES(6),
                        SUBPARTITION  MONTH7  VALUES(7),
                        SUBPARTITION  MONTH8  VALUES(8),
                        SUBPARTITION  MONTH9  VALUES(9),
                        SUBPARTITION  MONTH10  VALUES(10),
                        SUBPARTITION  MONTH11  VALUES(11),
                        SUBPARTITION  MONTH12  VALUES(12)
                )
        (
        PARTITION YEARXXX VALUES LESS THAN (2012)
)

Second step PL/SQL code for filling the table :

Note that  NLS_DATE_FORMAT for following code is 'DD.MM.YYYY'


DECLARE
BEGIN_DATE DATE;
END_DATE  DATE;
V_DYNTASK LONG;
VAL1  DATE;
DIFF_VAL  NUMBER;
DAY_VALUE  NUMBER;
MONTH_VALUE  NUMBER;
YEAR_VALUE  NUMBER;
YEAR_PART_VAL  NUMBER;
YEAR_VALUE_TMP NUMBER ;
BEGIN


BEGIN_DATE:= '01.01.2012';
END_DATE:='01.01.2050';  --- NOT INCLUDED
YEAR_VALUE_TMP:=0;

V_DYNTASK:='SELECT TO_DATE('''||END_DATE ||''') -  TO_DATE('''||BEGIN_DATE||''') FROM  DUAL ';
EXECUTE IMMEDIATE V_DYNTASK INTO DIFF_VAL;


FOR  COUNTER1 IN  0..DIFF_VAL-1
LOOP

        V_DYNTASK:= 'SELECT  TO_DATE('''||BEGIN_DATE||''')+'||COUNTER1||'  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO VAL1;



        V_DYNTASK:=  'SELECT  EXTRACT(DAY  FROM  TO_DATE('''||VAL1||''',''DD.MM.YYYY'') )  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO DAY_VALUE;

        V_DYNTASK:=  'SELECT  EXTRACT(MONTH  FROM  TO_DATE('''||VAL1||''',''DD.MM.YYYY'') )  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO MONTH_VALUE;

        V_DYNTASK:=  'SELECT  EXTRACT(YEAR  FROM  TO_DATE('''||VAL1||''',''DD.MM.YYYY'') )  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO YEAR_VALUE;

     
   
         IF   YEAR_VALUE_TMP!=YEAR_VALUE
         THEN
           
            YEAR_PART_VAL:=YEAR_VALUE+1;
            V_DYNTASK:= ' ALTER TABLE NLS_CALENDAR_TABLE   ADD PARTITION YEAR'||YEAR_VALUE||'  VALUES LESS THAN  ( '||YEAR_PART_VAL||' ) ' ;
            DBMS_OUTPUT.PUT_LINE(V_DYNTASK);
            EXECUTE IMMEDIATE  V_DYNTASK;
            COMMIT;
       
         END IF;
     
     
     
       V_DYNTASK:=
       '
       INSERT INTO  NLS_CALENDAR_TABLE  ( DAY ,  MONTH, YEAR ,  FULL_DATE )
       SELECT  '
       ||DAY_VALUE||'  ,  '||MONTH_VALUE||'  ,  '||YEAR_VALUE||' , '''||VAL1||'''  FROM  DUAL';
     
     
       EXECUTE IMMEDIATE V_DYNTASK;
       COMMIT;

       YEAR_VALUE_TMP:=YEAR_VALUE;  --  CHECK FOR YEAR IS CHANGED OR NOT  , IF YES THEN ADD PARITION
     
END LOOP;


END;


Result will be look like this:

Also Final Table Structure: