Last week I came across an interesting problem.
Problem: I want to centralize my average assets calculation in one place and different downstream systems should be able to consume it. For example: Cognos reports should be able to use this, Informatica mapping can use this as a source. Very similar to an enterprise service.
Solutions: Initially I thought an Oracle stored procedure would work great. I can pass a date to the stored procedure and it can return a sys_refcursor and it could be used by Cognos reports. (You can build report based on stored procedures in Cognos).
The problem I encountered was when using the same stored procedure as a source in Informatica. Working with sys_refcursor is tricky in informatica which uses row based approach.
I found a good way of parametrizing a view on asktom.com. It would be great if Oracle can come up with a parametrized view concept like parametrized cursors.
Here is how I was able to create parametrized views to solve the problem that could be used in both Cognos and Informatica.
Step 1: Create a context
CREATE CONTEXT param_context using set_context_proc;
param_context – name of the context
set_context_proc – name of a stored procedure where the context will be set for the session. The stored procedure need not exist when creating the context.
Note: You need create any context privilege.
Step 2: Create set_context_proc
create or replace procedure set_context_proc(p_end_date IN DATE, p_cursor OUT sys_refcursor) as
l_sql_text Varchar2(100) ;
l_sql_text := ‘select ”’ || p_end_date || ”’ from dual’ ;
— set the session context
dbms_session.set_context(‘PARAM_CONTEXT’, ‘END_DATE’, p_end_date);
— this is only required when you call this stored proc in cognos to set the context date
open p_cursor for l_sql_text ;
Step 3: Create view to use this context
CREATE OR REPLACE FORCE VIEW MTD_CALC_AVG_V1
SELECT TRUNC(TO_DATE(SYS_CONTEXT(‘PARAM_CONTEXT’, ‘END_DATE’)),’MM’) fromdate,
TRUNC(AVG (numbers),2) mtd_numbers
WHERE tdate BETWEEN TRUNC(TO_DATE (SYS_CONTEXT (‘PARAM_CONTEXT’, ‘END_DATE’)),’MM’)
AND TO_DATE (SYS_CONTEXT (‘PARAM_CONTEXT’, ‘END_DATE’))
GROUP BY TRUNC (TO_DATE (SYS_CONTEXT (‘PARAM_CONTEXT’, ‘END_DATE’)), ‘MM’),
TO_DATE (SYS_CONTEXT (‘PARAM_CONTEXT’, ‘END_DATE’)),
Step 4: Make this work in Cognos
When building cognos framework you can use stored procedure as a data source.
- Add stored procedure set_context_proc as data source in framework manager
- Add view MTD_CALC_AVG_V1 as data source in framework manager
- When building reports first call set_context_proc by passing the date picked by the user. The session context date parameter will be set.
- Use the view in the reports. The views will start showing data as soon as the parameter is set.
Step 5: Make this work in Informatica
- Add the view as a source in Informatica
- Build your mapping using the view as source
- Open Properties tab on your source qualifier. In pre-sql section call the stored proc by passing the data that you want the data for.
I am interested in knowing if there are any other solutions for this problem.
Suggestions are most welcome.