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

From: Rumpi Gravenstein <rgravens_at_gmail.com>
Date: Sun, 25 May 2008 20:19:14 -0400
Message-ID: <9c9b9dc90805251719i3fca228fo45ba4ecff3090e49@mail.gmail.com>


Thanks. Both the VPD route and the route that Mark has described should work. I looked at the query rewrite package and that doesn't seem to be as good a fit as the source SQL can change depending on what the users decide to type in on any given day.

You've given my the necessary assurance that the approach I'm looking at is reasonable and can be made to work! Once I get the details worked out I'll post back a final solution.

On Sat, May 24, 2008 at 3:48 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> Okay, there may be a better way, but this should work. First observe the
> results from:
>
>
>
> select sql.sql_fulltext from v$sql sql,
>
> (select s.sql_address,s.sql_hash_value,s.sql_id from
>
> (select sid from v$mystat where rownum < 2) ms,
>
> v$session s
>
> where ms.sid = s.sid
>
> ) id
>
> where sql.address = id.sql_address
>
> and sql.hash_value = id.sql_hash_value
>
> and sql.sql_id = id.sql_id
>
> and upper(sql.sql_fulltext) like '%WHERE '||'SQL.ADDRESS%';
>
>
>
> (Someone else may supply a faster way to return your own current sql query,
> but this seems to work 10g+. address and hash_value might be all you have at
> an earlier release).
>
>
>
> notice the gratuitous concatenation in the string so it doesn't find itself
> all the time. Not bulletproof, but if someone hacks around it they don't
> need your protection from long running queries.
>
>
>
> Now, change the select to just a count(*)*max_number_of_rows_to_return
> "date_predicate" and change the part of the literal SQL.ADDRESS to
> X_VW.DATE_COLUMN,
>
> add this as an inline view to your view definition,
>
>
>
> and add the predicate … and rownum <= date_predicate
>
>
>
> (if you can't set some appropriate max_number_of_rows_to_return, then you
> won't get to bail out on a stopkey, and you could use … and date_predicate >
> 0 and leave out the multiplication.)
>
>
>
> Oh – you also wanted an error message.
>
>
>
> So for that you add a union all to your query with a table that has the
> matching column types in order and splice your warning message into an
> appropriate column or columns and a view of the table repeating the current
> query scrutiny except … and date_predicate = 0 to return anything.
>
>
>
> This is untested off the top of my head, so let me know if you have
> trouble, or if someone gives you a lot better solution.
>
>
>
> Regards,
>
>
>
> mwf
>
>
>
>
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Rumpi Gravenstein
> *Sent:* Friday, May 23, 2008 7:57 PM
> *To:* oracle-l
> *Subject:* Create View that only Returns Data when user supplies predicate
>
>
>
> 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
>

-- 
Rumpi Gravenstein

--
http://www.freelists.org/webpage/oracle-l
Received on Sun May 25 2008 - 19:19:14 CDT

Original text of this message