Hi,

Is it possible to create a view or a version of data accessible through
same object name (table or view), which returns different values for
different sessions?

Here is what I am trying to do:

I need to create a view with a different underlying query each time it
is accessed. But I still need it to behave like a static object so I
could use it to link with an external tool (so, it's name and structure
etc will not change, just the underlying query and it's where clause
willl change). To make matters more complex, the query would be
different for different people logged in, hence the SESSION SPECIFIC
contstraint.

Any ideas?

thanks much,
Mark

<vistav20 (AT) yahoo (DOT) com> wrote

yes,

you want VPD (Virtual Private Database) functionality, AKA 'FGA' (Fine
Grained Access) -- see
http://www.oracle.com/technology/pub...14_10gdba.html

++ mcs

"Mark C. Stock" <mcstockX@Xenquery .com> wrote

PS: you just need to post to one group -- keep any future response in
comp.databases.oracle.server (didn't notice the cross-posting on my first
reply)

On 19 May 2005 13:36:00 -0700, vistav20 (AT) yahoo (DOT) com wrote:

I'm not exactly sure what you're trying to do but:

You could use a view and sys_context variables.
You could create a pipelined function.

Either way, the STRUCTURE must remain the same but the way the execute
can change.

More info with DB version and more detaield requirements might help.

Thanks,

Lewis


-----------------------------------------------------------
Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle
http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database
http://www.suite101.com/welcome.cfm/oracle

Sign up for courses here:
http://www.suite101.com/suiteu/default.cfm/416752
-----------------------------------------------------------

vistav20 (AT) yahoo (DOT) com wrote:
: Hi,

: Is it possible to create a view or a version of data accessible through
: same object name (table or view), which returns different values for
: different sessions?

: Here is what I am trying to do:

: I need to create a view with a different underlying query each time it
: is accessed. But I still need it to behave like a static object so I
: could use it to link with an external tool (so, it's name and structure
: etc will not change, just the underlying query and it's where clause
: willl change). To make matters more complex, the query would be
: different for different people logged in, hence the SESSION SPECIFIC
: contstraint.

: Any ideas?


select col1,col2 from table1 where user = 'FRED'
UNION ALL
select col3,col4 from table2 where user = 'SAM'

etc


--

This space not for rent.

vistav20 (AT) yahoo (DOT) com wrote:
No version number ... do you think the solution is generic going back
to version 4?

If 9i or above use a pipelined table funtion.

http://www.psoug.org
click on Morgan's Library
click on Pipelined Table Functions

But I am agreement with Jim and Sybrand ... what you are asking has
most of the hallmarks of the wrong solution to an unstated problem.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)