TableFunction Component in ODI 12c (12.1.3 onward)

Table Function Component (Available from 12.1.3 version onward)

Scripts:

CREATE TABLE DEV.DIM_DATE ( DATE_KEY NUMBER, DATE_TIME_START DATE, DAY_OF_WEEK_NUMBER NUMBER(3), DAY_OF_WEEK_DESC  VARCHAR2(25), DAY_OF_WEEK_SDESC VARCHAR2(10), WEEK_IN_MONTH_NUMBER NUMBER(4), WEEK_IN_YEAR_NUMBER NUMBER(4) , DAY_OF_MONTH_NUMBER NUMBER(4), MONTH_VALUE NUMBER(4), MONTH_DESC VARCHAR2(25) , MONTH_SDESC VARCHAR2 (25), MONTH_START_DATE DATE, MONTH_END_DATE DATE , DAYS_IN_MONTH NUMBER(5), DAY_OF_QUARTER_NUMBER NUMBER(5), QUARTER_VALUE NUMBER(5), QUARTER_DESC VARCHAR2(25), QUARTER_START_DATE DATE, QUARTER_END_DATE DATE, DAYS_IN_QUARTER NUMBER(5), DAY_OF_YEAR_NUMBER NUMBER(5), YEAR_VALUE NUMBER(5)); / DROP TYPE DEV.T_TF_TAB; DROP TYPE DEV.T_TF_ROW; / CREATE TYPE DEV.T_TF_ROW AS OBJECT (   ID           NUMBER,   CALLDATE DATE ); / CREATE TYPE DEV.T_TF_TAB IS TABLE OF T_TF_ROW; / CREATE OR REPLACE FUNCTION DEV.GET_TAB_TF(STARTDATE VARCHAR2, ENDDATE VARCHAR2) RETURN T_TF_TAB PIPELINED AS NO_OF_DAYS NUMBER; BEGIN   select to_date(ENDDATE,'DD-MON-YYYY')-TO_DATE(STARTDATE,'DD-MON-YYYY') INTO NO_OF_DAYS FROM DUAL;   FOR i IN 0 .. NO_OF_DAYS   LOOP     PIPE ROW(T_TF_ROW(i,TO_DATE(STARTDATE,'DD-MON-YYYY')+i));    END LOOP;   RETURN; END; /

Pre-requisites

·         Need to have knowledge on Oracle Table Function

·         Need to have knowledge on creating function in Oracle DB

Approach:

1.      Create Mapping and Drag and drop the Datastore from Model (i.e., DIM_DATE)

2.      Drag and drop the Table function from components palette into logic editor

3.      Connect Output connector of Table Function to input connector of Data store (i.e., DIM_DATE) as follows

4.      Select table function component and go to property inspector and expand connector points and edit as mention below

5.      Select DIM_DATE datastore and go to property inspector and map each field as follows

TO_NUMBER(TO_CHAR(TABLEFUNCTION.CALLDATE, 'YYYYMMDD')) AS DATE_KEY, TABLEFUNCTION.CALLDATE AS DATE_TIME_START, TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'D'))  AS DAY_OF_WEEK_NUMBER, TO_CHAR (TABLEFUNCTION.CALLDATE, 'DAY')  AS DAY_OF_WEEK_DESC, TO_CHAR (TABLEFUNCTION.CALLDATE, 'DY') AS DAY_OF_WEEK_SDESC, TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'W')) AS WEEK_IN_MONTH_NUMBER, TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'WW')) AS WEEK_IN_YEAR_NUMBER, TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'DD'))  AS DAY_OF_MONTH_NUMBER, TO_CHAR (TABLEFUNCTION.CALLDATE, 'MM') AS MONTH_VALUE, TO_CHAR (TABLEFUNCTION.CALLDATE, 'MONTH')AS MONTH_DESC, TO_CHAR (TABLEFUNCTION.CALLDATE, 'MON') AS MONTH_SDESC, TRUNC (TABLEFUNCTION.CALLDATE, 'MM')  AS MONTH_START_DATE, LAST_DAY (TABLEFUNCTION.CALLDATE) AS MONTH_END_DATE, TO_NUMBER ( TO_CHAR( LAST_DAY (TABLEFUNCTION.CALLDATE), 'DD')) AS DAYS_IN_MONTH, TRUNC(TABLEFUNCTION.CALLDATE) – TRUNC(TABLEFUNCTION.CALLDATE, 'Q') + 1 AS DAY_OF_QUARTER_NUMBER, TO_CHAR(TABLEFUNCTION.CALLDATE, 'Q') AS QUARTER_VALUE, 'Q' || TO_CHAR (TABLEFUNCTION.CALLDATE, 'Q') AS QUARTER_DESC, TRUNC (TABLEFUNCTION.CALLDATE, 'Q') AS QUARTER_START_DATE, ADD_MONTHS(TRUNC (TABLEFUNCTION.CALLDATE, 'Q'), 3) – 1 AS QUARTER_END_DATE, ADD_MONTHS(TRUNC (TABLEFUNCTION.CALLDATE, 'Q'), 3) – TRUNC (TABLEFUNCTION.CALLDATE, 'Q') AS DAYS_IN_QUARTER, TO_NUMBER(TO_CHAR (TABLEFUNCTION.CALLDATE, 'DDD')) AS DAY_OF_YEAR_NUMBER, TO_CHAR(TABLEFUNCTION.CALLDATE, 'YYYY') AS YEAR_VALUE

Output: