Parameterized views in Oracle

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) ;
begin
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 ;
end;

Step 3: Create view to use this context

CREATE OR REPLACE FORCE VIEW MTD_CALC_AVG_V1
AS
SELECT TRUNC(TO_DATE(SYS_CONTEXT(‘PARAM_CONTEXT’, ‘END_DATE’)),’MM’) fromdate,
TO_DATE(SYS_CONTEXT(‘PARAM_CONTEXT’,’END_DATE’)) todate,
numbercode,
TRUNC(AVG (numbers),2) mtd_numbers
FROM DAILY_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’)),
numbercode;

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.

Advertisements

About Diwakar Kasibhotla

Oracle Database Tuning, VLDB designing, ETL/BI architect, Data Modelling, Exadata Architect.
This entry was posted in Oracle Database, PL/SQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s