So you've got a query in some application that you can't change? But you need to add a where clause to it?
If so, yes, you can swap out the table for a view by:
- Renaming the table
- Creating a view with the where clause:
create table t as
select level x, mod(level, 2) y from dual
connect by level <= 10;
select * from t;
X Y
1 1
2 0
3 1
4 0
5 1
6 0
7 1
8 0
9 1
10 0
rename t to ttab;
create or replace view t as
select * from ttab
where y = 0;
select * from t;
X Y
2 0
4 0
6 0
8 0
10 0
Of course, this will affect
all code accessing the table. So check thoroughly before doing this.
Another method you could use is Virtual Private Database. This can dynamically add a where clause to your query based on criteria, such as the user:
https://oracle-base.com/articles/8i/virtual-private-databases https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/using-oracle-vpd-to-control-data-access.html Provided you can identify or set some session attributes specific to the executable's query (e.g. user, action, module, etc.) you can apply the conditions only when this runs and not other code.