Here is the email thread with my friend.
IS there a way I can rap this up into view????
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.
Permalink Submitted by ivanelson on Mon, 2012-03-05 08:34.
Where's the code for "function get_context_name" in the body?
Permalink Submitted by oralover2006 on Fri, 2012-06-29 07:04.