creating parameterized views in oracle11g

Question

I have a big query with nesting and left join and Ineed to create a view out of it so as not to run it from the application. The issue is I need the date range and some other fields as input parameters since it will vary from the front end for each request. I just looked up and saw some posts referring to using SYS_CONTEXT for parameterized views and need to know exactly how do I create the view for example with 2 parameters - fromdate, todate and how I invoke the view from the application.

Just for info I am using grails/groovy for developing the application. and here is the query I want to create view out of..

 select 
    d.dateInRange as dateval,
    eventdesc,
    nvl(td.dist_ucnt, 0) as dist_ucnt
from (
    select 
        to_date(fromdate,'dd-mon-yyyy') + rownum - 1 as dateInRange
    from all_objects
    where rownum <= to_date(fromdate,'dd-mon-yyyy') - to_date(todate,'dd-mon-yyyy') + 1
) d
left join (
    select 
        to_char(user_transaction.transdate,'dd-mon-yyyy') as currentdate,
        count(distinct(grauser_id)) as dist_ucnt,
        eventdesc 
    from
        gratransaction, user_transaction 
  where gratransaction.id = user_transaction.trans_id and 
  user_transaction.transdate between to_date(fromdate,'dd-mon-yyyy') and to_date(todate,'dd-mon-yyyy') 
    group by  to_char(user_transaction.transdate, 'dd-mon-yyyy'), eventdesc 
) td on td.currentdate = d.dateInRange order by d.dateInRange asc

Solution 1

The context method is described here: http://docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm

e.g. (example adapted from the above link)

CREATE CONTEXT dates_ctx USING set_dates_ctx_pkg;

CREATE OR REPLACE PACKAGE set_dates_ctx_pkg IS 
  PROCEDURE set(d1 in date, d2 in date); 
END; 
/

CREATE OR REPLACE PACKAGE BODY set_dates_ctx_pkg IS
  PROCEDURE set(d1 in date, d2 in date) IS 
  BEGIN 
    DBMS_SESSION.SET_CONTEXT('dates_ctx', 'd1', TO_CHAR(d1,'DD-MON-YYYY'));
    DBMS_SESSION.SET_CONTEXT('dates_ctx', 'd2', TO_CHAR(d2,'DD-MON-YYYY'));
  END;
END;
/

Then, set the dates in your application with:

BEGIN set_dates_ctx_pkg.set(mydate1, mydate2); END;
/

Then, query the parameters with:

SELECT bla FROM mytable
WHERE mydate
  BETWEEN TO_DATE(
            SYS_CONTEXT('dates_ctx', 'd1')
          ,'DD-MON-YYYY')
      AND TO_DATE(
            SYS_CONTEXT('dates_ctx', 'd2')
          ,'DD-MON-YYYY');

The advantage of this approach is that it is very query-friendly; it involves no DDL or DML at runtime, and therefore there are no transactions to worry about; and it is very fast because it involves no SQL - PL/SQL context switch.

Alternatively:

If the context method and John's package variables method are not possible for you, another one is to insert the parameters into a table (e.g. a global temporary table, if you're running the query in the same session), then join to that table from the view. The downside is that you now have to make sure you run some DML to insert the parameters whenever you want to run the query.

Solution 2

I have just made a workaround for this annoying Oracle disadvantage. Like this

create or replace package pkg_utl
as
  type test_record is record (field1 number, field2 number, ret_prm_date date);
  type test_table is table of test_record;
  function get_test_table(prm_date date) return test_table pipelined;
end;
/

create or replace package body pkg_utl
as
  function get_test_table(prm_date date) return test_table pipelined
  is
  begin
    for item in (
      select 1, 2, prm_date
      from dual
    ) loop
      pipe row (item);
    end loop;
    return;
  end get_test_table;
end;
/

it still requires a package, but at least i can use it in more convinient way:

select *
from table(pkg_utl.get_test_table(sysdate))

i am not sure about performance...

Solution 3

To use parameters in a view one way is to create a package which will set the values of your parameters and have functions that can be called to get those values. For example:

create or replace package MYVIEW_PKG as
  procedure SET_VALUES(FROMDATE date, TODATE date);

  function GET_FROMDATE
    return date;

  function GET_TODATE
    return date;
end MYVIEW_PKG;

create or replace package body MYVIEW_PKG as
  G_FROM_DATE   date;
  G_TO_DATE     date;

  procedure SET_VALUES(P_FROMDATE date, P_TODATE date) as
  begin
    G_FROM_DATE := P_FROMDATE;
    G_TO_DATE := P_TODATE;
  end;

  function GET_FROMDATE
    return date is
  begin
    return G_FROM_DATE;
  end;

  function GET_TODATE
    return date is
  begin
    return G_TO_DATE;
  end;
end MYVIEW_PKG;

Then your view can be created thus:

create or replace view myview as
    select 
        d.dateInRange as dateval,
        eventdesc,
        nvl(td.dist_ucnt, 0) as dist_ucnt
    from (
        select 
            MYVIEW_PKG.GET_FROMDATE + rownum - 1 as dateInRange
        from all_objects
        where rownum <= MYVIEW_PKG.GET_FROMDATE - MYVIEW_PKG.GET_TODATE + 1
    ) d
    left join (
        select 
            to_char(user_transaction.transdate,'dd-mon-yyyy') as currentdate,
            count(distinct(grauser_id)) as dist_ucnt,
            eventdesc 
        from
            gratransaction, user_transaction 
      where gratransaction.id = user_transaction.trans_id and 
      user_transaction.transdate between MYVIEW_PKG.GET_FROMDATE and MYVIEW_PKG.GET_TODATE
        group by  to_char(user_transaction.transdate, 'dd-mon-yyyy'), eventdesc 
    ) td on td.currentdate = d.dateInRange order by d.dateInRange asc;

And to run it you must set the values first:

exec MYVIEW_PKG.SET_VALUES(trunc(sysdate)-1,trunc(sysdate));

And then calls to it will use these values:

select * from myview;

Comments

[pri_dev]

hmm... It looks like this approach will definitely need functions/packages...which we are not allowed in this implementation. I did see some solutions online but most of them have functions/procedures

[pri_dev]

we cannot use procedures of functions, but I would be looking into the options and trying out the solutions suggested here

[Jeffrey Kemp]

Why can't you use procedures, functions or packages? If you're using Oracle, there's no good reason for you to avoid them.

[Jeffrey Kemp]

How exactly does this address the requirement to be able to push arguments into a view? Your sample query still has the parameter hard-coded within the view (sysdate). You may as well just use sysdate directly in the view without needing a package.

[Neco]

Hi, Jeffrey. sysdate is just an example - you can put there any value. The key thing here is that you can reuse your query (in my example it is "select 1, 2, prm_date from dual") just like a view, but with an external parameter (prm_date).

[Jeffrey Kemp]

So you're suggesting replacing the view entirely with a query inside a pipelined function?

[Neco]

Yes, exactly. Just as an alternative.

[n8.]

This is more similar to the MS "table valued functions" which, to me, were pretty straightforward.