Re: Parameterized Views

From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com>
Date: Wed, 19 Sep 2007 11:05:18 -0700 (PDT)
Message-ID: <993232.78588.qm@web58713.mail.re1.yahoo.com>

Mir

You can easily parameterise a view by including a SYS_CONTEXT function (see http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions122a.htm#SQLRF06117)

1. create your own context and supporting package
2. call your own set_context function to set the value of a parameter
3. define the view using a call to sys_context('your namespace','your parameter name') - or use your own get_context function

I posted a description of how to do everything but 3 here: http://preferisco.blogspot.com/2006/07/using-global-context-variables.html

Two things to beware of when parameterising views:

1. always set the context variable(s) before querying (or make sure your get_context function provides a suitable default)
2. WATCH OUT FOR SESSION POOLS

If you have a session pool, remember that the context is private to a (database) session - but because of the session pool, each database session is serially used by a number of user/application sessions. It can be like sharing needles - remember to "sterilise" your context before using it (and it's polite to do it afterwards too).

HTH Regards Nigel

• Original Message ---- From: Mir M. Mirhashimali <mhyder_at_rice.edu>

I was wondering if it is possible to parameterizes a view. did some googling but did not find any satisfactory results.
--

http://www.freelists.org/webpage/oracle-l Received on Wed Sep 19 2007 - 13:05:18 CDT