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

Another option could be just define either fixed predicate where rownum <= N or user depending predicate where rownum <= some_pkg.some_var and set variable for every user depending on his username.
Or use contexts, add them in where clause and set them in your package individually for every user, this would assure that noone can hack around package global variable as in previous option.

This of course assumes that you can define some upper limit for a day and has weakness that careless user could think that these are all rows for the given period although this might be not true.

We are using that quite succesfully in our applications - for all user queries adding where clause where rownum <= N + 1 and as soon as app gets Nth+1 number rows it knows that search criateria are too weak and there are (possibly) many other rows. Then app displays only first N records to user and shows a warning that there are another rows to fetch. As you haven't app layer in the middle between user and DB it is harder let the user know that there are more rows satisfying his criteria, just these are not selected.

Gints Plivna
http://www.gplivna.eu

2008/5/24, Rumpi Gravenstein <rgravens_at_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.

--
http://www.freelists.org/webpage/oracle-l