Dynamic Views, Creating Views based on Variables

I have been asked several times recently, how to create "dynamic" views such that a view can be created using "variables". Now I see this question asked on the OraFaq message board, so I thought maybe this is worth posting as an article, so here it is. My answer comes in the form of an Email reply to a friend (why bother to rewrite a prefectly good answer). Please feel free to abuse my answer if you think it deficient, but you better be posting your own alternatives if you do so.

Here is a link to the recent OraFaq question from the sql and plsql newbies section:

http://www.orafaq.com/forum/t/90828/0/

Here is the email thread with my friend.

_____________________________________________
From: Vlad
Sent: Thursday, September 27, 2007 11:15 AM
To: Meade, Kevin
Subject: Dynamic View with Variables

IS there a way I can rap this up into view????

Select * 
 FROM
(TABLE(CAST (sf_GET_MONTHLY_POINTS (parmDatefrom, parmDateTo) AS collection_MONTHLY_POINTS) ) ) months_span

thanks

_____________________________________________
From: Meade, Kevin
Sent: Thursday, September 27, 2007 12:13 PM
To: Vlad
Subject: Dynamic View with Variables

I presume you are asking because you have two parameters in the query that are not hard coded constants (parmdatefrom,parmdateto)?

There are three solutions I have used for this, you may or may not like any. Which one you choose depends upon how much work you want to do and what kind of environment you are executing your code from. The most flexible solution and oldest is #1, the newest solution is #3. They all work in that they all will allow you to pre-create a view that will give you the data you ask for. The basic requirement for each is that you must supply two date values to your view somehow. Each solution differs in how these values are supplied, and in the restrictions they may impose.

In solution #1, you build your view to select the values of your dates from pre-existing rows in lookup tables. Then you supply your parameters to the query against the view rather than the underlying table. This means the solution is 100% query only, but it requires that you know all possible dates that could be supplied, ahead of time, because the view will be selecting date rows off this lookup table. This is a problem if you are using dates with a granularity of seconds, because it requires you to prefill 60*60*24*365 rows per year over all years of possible time frame. Then again, its only rows in a table right?, and Oracle is good at storing rows in a table right? Just don't ask you users to choose among dates with seconds.

In solution #2, you use a working table into which you insert your dates first. Your view is created to select from the working table to get its parameter values. This does not require you to know ahead of time what all possible date values would be. But your solution is now two step and your transaction is no longer query only, as you must modify the database with your parameter insert/update/delete as required. Problems associated with this two step issue can be somewhat mitigated using autonomous transactions as the mechanisms for inserting/updating/deleting your parameter rows.

In solution #3, you set values in what is called a SYSTEM CONTEXT. Think of a context as a memory area in which string variables are defined. You create variables in the memory area and then you can reference them using a specific syntax. Essentially you have created your own named constants for your session (and other sessions too). This solution is a 100% query only solution because you are not issuing insert/update/delete against data in your database. However, is it still a two part solution because you must set these values before you can use them. Another advantage (or dis-advantage) of this method is that these values are shareable between different sessions. Thus it can be used as yet another inter process communications vehicle, but you don't need it for that do you.

** Solutions #1 and #3 have variations. For example: for #3, you could use something more familiar: a package with global variables. You could set values of these variables using procedures of your package, and have your view use function calls to your package to get the values. Indeed, this is to some degree what you are doing when you use sys_context. For #1, if your dates are coming from values in some date column on some table (most likely), then you could construct your view using a WITH clause to pull your date lists out of the column. There may however be performance issues to deal with here, you will have to test that. But it does get around having to prepopulate date values and ensures you are always dealing with a date known to match the data in your data table.

** lastly, I have not tested any of the code samples below. I leave it to you to ferret out syntax errors etc.

Good luck, Kevin

Solution#1: provide access to the dates via lookup tables of dates (asssumes date lists already exist)

Create or replace
View vw_vlad
As
Select
           date_list_from.date from_date
         , date_list_to.date to_date
         , months_span.*
From
       date_list date_list_from
     , date_list date_list_to
     , TABLE(CAST (sf_GET_MONTHLY_POINTS (date_list_from.date, date_list_to.date) AS collection_MONTHLY_POINTS) ) months_span
/

Select *
From vw_vlad
Where from_date = :parmdatefrom
And to_date = :paredateto
/

Solution#2: user a work table for the parameters

Create sequence query_parameter_seq
/

Create table query_parameters
 (
    usage_context varchar2(255) not null
   ,usage_key number not null
   ,data_type varchar2(30) not null
   ,parameter_name varchar2(30) not null
   ,value varchar2(4000)
)

/

select query_parameter_seq.nextval in usage_key_v from dual;

Insert into query_parameters values (
                                      'vlads view'
                                     ,usage_key_v
                                     ,'DATE'
                                     ,'parmdatefrom'
                                     ,'01-jan-2000 12:30:00'
                                    )
/
Insert into query_parameters values (
                                      'vlads view'
                                     ,usage_key_v
                                     ,'DATE'
                                     ,'parmdateto'
                                     ,'01-jan-2001 12:30:00'
                                    )
/

Create or replace
View vw_vlad
as
Select *
From query_parameters p1
    ,query_parameters p2
    ,TABLE(CAST (sf_GET_MONTHLY_POINTS (to_date(p1.value,'dd-mon-rrrr hh24:mi:ss'), to_date(p2.value,'dd-mon-rrrr hh24:mi;ss')) AS collection_MONTHLY_POINTS) ) months_span
Where p1.usage_key = usage_key_v and p1.parameter_name = 'parmdatefrom')
And  p2.usage_key = usage_key_v and p2.parameter_name = 'parmdateto')
/

Select *
From vw_vlad
/

Solution#3 Use sys_context()

create or replace
context vlads_app_context
using pkg_vlads_app_context
accessed globally
/

create or replace
package pkg_vlads_app_context
is

   function get_context_name return varchar2;
   procedure set_from_date (from_date_p in date);
   procedure set_to_date (to_date_p in date);

end;
/
show errors


create or replace
package body pkg_vlads_app_context
is

   context_name_gv varchar2(30) := 'VLADS_APP_CONTEXT';

   procedure set_from_date (from_date_p in date) is
   begin
      dbms_session.set_context(
                                context_name_gv
                              , 'FROM_DATE'
                              , from_date_p
                              );
   end;

   procedure set_to_date (to_date_p in date)is
   begin
      dbms_session.set_context(
                                context_name_gv
                              , 'TO_DATE'
                              , to_date_p
                              );
   end;

end;
/
show errors


create or replace
view vw_vlad
as
select months_span.*
from TABLE(CAST (sf_GET_MONTHLY_POINTS (
                                        to_date(sys_context('VLADS_APP_CONTEXT','FROM_DATE'),'dd-mon-rrrr hh24:mi:ss')
                                       ,to_date(sys_context('VLADS_APP_CONTEXT','TO_DATE'),'dd-mon-rrrr hh24:mi:ss')
                                       ) AS collection_MONTHLY_POINTS) ) months_span
/

exec pkg_vlads_app_context.set_from_date(sysdate-100);
exec pkg_vlads_app_context.set_to-date(sysdate);

select *
from vw_vlad
/

_____________________________________________
From: Vlad
Sent: Thursday, September 27, 2007 12:58 PM
To: Meade, Kevin
Subject: Dynamic View with Variables

You're the best…

Thanks bud, vlad