Re: Create View that only Returns Data when user supplies predicate

From: Roman Podshivalov <roman.podshivalov_at_gmail.com>
Date: Sat, 24 May 2008 07:44:01 -0400
Message-ID: <55f303590805240444k5e0621a0pa1fd599f02ff70d6@mail.gmail.com>


Hi,

Did you check the functionality of DBMS_ADVANCED_REWRITE package ?

--romas

On 5/23/08, Rumpi Gravenstein <rgravens_at_gmail.com> wrote:
>
> 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 Sat May 24 2008 - 06:44:01 CDT

Original text of this message