Create View that only Returns Data when user supplies predicate
Date: Fri, 23 May 2008 19:56:51 -0400
Message-ID: <9c9b9dc90805231656l633ef8b2h3cdf00b6e0508e9@mail.gmail.com>
We have a problem where a we have created views for users to query data.
When our end users query the view with a 1 day date range everything works
well, and the query returns in a few seconds. Unfortunately users don't
always supply a predicate. When that happens things take a very long time.
What I'm wondering is if there is a standard way to force users into supplying a predicate to the query. At the end of the day what I want is a view that returns an error when it's queried with inappropriate conditions. This is on 10gR2. I've been puzzling over the challenge and think I can come up with a way to do this using a function in the underlying view predicate that throws an error when the appropriate conditions aren't supplied. Before I go through the work of creating that function I thought I'd ask here to see if someone has done something like this before or if there is a better alternative.
To recap.
I have a complicated view x_vw with a lot of columns, one of which is a date column. What I'd like to do is have this query throw an error:
SELECT *
FROM x_vw
and have this query run to completion
SELECT *
FROM x_vw
WHERE x.vw.date_column between TRUNC(SYSDATE) and TRUNC(SYSDATE)+1
Just one comment, I know that you can define resource limits. I'd like to avoid that approach as I think it better to stop the query right at the start and not after it has "wasted" a lot of system resources.
Thanks in advance.
--
Rumpi Gravenstein
--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 23 2008 - 18:56:51 CDT