Init Blocks, init.

That’s repository variable Initialization Blocks to you. Init blocks as they are more commomnly known retrieve the data that is held in memory by the Session and Repository variables associated with them. They are of course accessed through Manage>Variables in the Administration console, and this is another area where subtle changes have been made for Oracle BIEE, to add a new init block open the Variable Manager, click Initialization Blocks and then right click in the righ pane or choose Action>New>Repository>Initialization Block.

Then the configuration itself, no more tabs, one form with buttons.

Edit Data Source button gives access
to further options.

Edit Data Target launches what used to be on the Variables tab, click here to name your variables.

Anyway, enough pictures, click on Edit Data Source to reveal the Init Block form where you will compose the initialization String, aka the SQL. You can now select db specific SQL if you wish, and now is also the time to Browse for your connection pool.

Oracle has two functions for returning the current date, SYSDATE and CURRENT_DATE, both should be selected from system dummy table DUAL, as this will always return only one row, sysdate returns the current date on the server.

You can convert to and from datetime types using the following functions;
TO_CHAR ({datetime| interval}, format)
TO_DATE (string, format)
TO_TIMESTAMP (string, format) (Oracle 10g onwards)

To return a specific element of the current date use the EXTRACT function;

SELECT EXTRACT({Second|Minute|Hour|Day|Month|Year} FROM SYSDATE) FROM DUAL;

CAST(value as datatype) If value is a string it must match the default text representation of the datatype, so for example

CAST (’27-Mar-2007’ AS DATE) may work where
CAST(’27-03-2007’ AS DATE

In the following example note how the case format applied to the format part of the TO_CHAR function is used to format the results ‘TUESDAY’ and ‘March’, this could of course have a crucial effect if the value is used in a filter.
Thanks to Oracle’s built in LAST_DAY function, the formula to find the last day of the month, 3 months hence, is simple.

As an alternative method you could of course W_DAY_D as the source for the majority of your dates, pre-calculated columns such as cal_month, cal_week and week_ago_dt will make function statements shorter and easier to read. The SQL below extracts much the same as the previous one with addition of two Julian dates at the end, note also that you can use comments in these statements, always a good idea.

SELECT

CURRENT_DATE,
TO_CHAR(CALENDAR_DATE, ‘DD/MM/YYYY’),
TO_NUMBER(DAY_OF_MONTH,’99’),
DAY_NAME,
TO_CHAR(WEEK_AGO_DT, ‘DD/MM/YYYY’),
CAL_MONTH,
CAL_WEEK,
MONTH_NAME,
TO_CHAR(ADD_MONTHS(SYSDATE,-1),’MONTH’),
CAL_YEAR,
EXTRACT (YEAR FROM (ADD_MONTHS(CURRENT_DATE, -12))),
EXTRACT (YEAR FROM (ADD_MONTHS(CURRENT_DATE, -24))),
EXTRACT (YEAR FROM (ADD_MONTHS(CURRENT_DATE, -36))),
LAST_DAY(DAY_DT),
’01’||SUBSTR(TO_CHAR(DAY_DT,’DD/MM/YYYY’),3,8),
TO_CHAR(ADD_MONTHS(LAST_DAY(DAY_DT),2),’DD/MM/YYYY’),
ADD_MONTHS((CAST(DAY_DT AS DATE) – EXTRACT(DAY FROM CAST(DAY_DT AS DATE))+1),1)-1, /* Last day of previous month */
JULIAN_DAY_NUM,
JULIAN_MONTH_NUM

FROM
W_DAY_D
WHERE
CAST(SUBSTR(SYSDATE,1,10) AS DATE)=DAY_DT