Problem: only sensible queries allowed

I've been asked to provide our marketing people select access on some large tables, but want to stop them using "stupid" where clauses that stop partition pruning / index lookup.

E.g.

SELECT ......

  FROM PM_RATED_CDRS
 WHERE to_char(RATED_ON, 'YYYYMMDD HH24:MI:SS') > '20090914 00:00:00'
   AND to_char(RATED_ON, 'YYYYMMDD HH24:MI:SS') <= '20090917 13:00:31'

or (this one's a cracker,..)

select ....
from....
where
and (trunc(a.CALL_DATE) >=

       trunc(to_date('20070101000000', 'yyyyMMddHH24MISS')))    and (trunc(a.CALL_DATE) <

       trunc(to_date('20090922235959', 'yyyyMMddHH24MISS') + 1))

BTW: The above 2 examples are real and I've feedback some polite suggestions on the use of dates, how trunc works etc. to our application developers. Note: I don't want to create a load of unnecessary functional indexes when its the code that needs fixing.

So I can try to fix the application developer's dodgy stuff.

But is there any way that I can block a query if it breaks some simple rules? Or allow only a short list of columns that can be used in the query?

Thoughts:

1. I was thinking about creating some views on the tables in question and giving these to marketing. But how can I stop select * from CDR_V1 where to_char(RATED_ON, 'YYYYMMDD HH24:MI:SS') > '20090914 00:00:00'
2. Initially I was thinking about a load of pipelined functions that could be used something like select * from table(get_cdrs_for_date_range(to_date('20070101000000', 'yyyyMMddHH24MISS'), to_date('20090922235959', 'yyyyMMddHH24MISS') + 1));

but I may asking for a lot of work here,... "can we please have another one that......"

Any ideas folks?

Cheers
Tony

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