RE: Create View that only Returns Data when user supplies predicate
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <rgravens@xxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 24 May 2008 15:48:31 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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
Other related posts:
