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

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 




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