Re: View with a parameter

Wim de Lange (w.delange_at_alcontrol.nl_nospam) wrote:

: For reporting purposes I want to define a crosstab view as follows:

: select 1+floor((rownum-1)/3) as rownumber,
: max(decode( mod(rownum,3), 1, article_id, null)) as col1,
: max(decode( mod(rownum,3), 2, article_id, null)) as col2,
: max(decode( mod(rownum,3), 0, article_id, null)) as col3
: from (select article_id from article where order_id=6 order by name)
: group by floor((rownum-1)/3)

: In this view the part "order_id = 6" is the "parameter" of this view. Is
: it possible to define a parameter in a view? So that I can pass the
: information about the order I want to this view?

Normally you build a view of the entire (unfiltered) data and then select the item you want with the WHERE clause when you use the view.

As of 8.1.7 you can use SYS_CONTEXT to make a view with a parameter (never used it, but it looks very useful, and I am looking for an excuse to use it myself).

Before 8.1.7 you can enter a value in a table of parameters and index the value using the sessionid and USERENV. You have to clean up the parameter table yourself though.

The only reason to do either of the latter two is if the view cannot be setup to return a (normally small) set of values efficiently enough. So for example if the view contains millions of rows built using outer joins on calculated values and you want to select one value for a drop down box in a form, then it might be hard to figure out how to make the response time ok. Received on Tue Mar 11 2003 - 23:04:54 CST