Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Manuel.

Asked: January 18, 2018 - 5:30 pm UTC

Last updated: November 06, 2023 - 1:59 pm UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

Hi ,

We have a process that cannot be changed and that executes a query over a specific table.
Due to a new development it is necessary to had a new statment to the WHERE clause of the query. But the executable cannot be changed.

So the idea would be to have an object (view ?) with the same name of the used table that is only used when we execute this process. and this new object already has the condition that we require. Is this doable ?

Can you please give us some kind og hint ?


Thanks

and Chris said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Carlos Santana, November 06, 2023 - 11:54 am UTC

Thanks for the answer the behaviour that I would like to have is something like

If table T has

t1 t2 t3
a null 1
a null 2
a null 3
b null 1
b null 2
b null 3


select * from T
where t1='a'

then the result to be

t1 t2 t3
a null 1

if
select * from T
where t3=2

then the result to be

t1 t2 t3
a null 2
b null 2


if
select * from T

then the result to be

t1 t2 t3
a null 1
a null 2
a null 3
b null 1
b null 2
b null 3


Chris Saxon
November 06, 2023 - 1:59 pm UTC

What exactly is the logic that determines these results? Please submit a new question including:

- Create table + insert into
- The output you want and an explanation of why that's the output you wan.t

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.