Table redirect / filter / trigger on select Is there any way to redirect queries to different tables / views based on the package that's referencing the table?  I.e. packages A and B both have "select grade from schema1.grd_tbl", but I want package A to get the percent grade that's stored in the table, and package B to get a letter grade that's calculated from the percent.  I'd like to avoid modifying the (dozens of) packages that reference the table.  I'd rather 'spoof' the table somehow if we can, replacing the percent in the grade column with a letter when called from those packages. (The column's varchar2; percents are coded as characters.)

First thought was to create a synonym pointing to a view that massages the column based on the calling package, but the code fully qualifies the table name in most cases so that doesn't seem doable. I went looking for something equivalent to a trigger on select; the closest I've found is fine-grained audit, and it's not row-based.

Is there magic somewhere that might let me do this?  Any hints appreciated.

Perry.
You could do something like this:

create or replace package body pkg1 
is
...
begin
  dbms_application_info.set_module('PKG1');
end;
/

create or replace package body pkg2 
is
...
begin
  dbms_application_info.set_module('PKG2');
end;
/


You can then access the module information using SYS_CONTEXT():

create or replace view v1 
as
select
  case 
    when sys_context('USERENV','MODULE') = 'PKG1' then to_char(grade_pct)
    when sys_context('USERENV','MODULE') = 'PKG2' then calc_grade_letter(grade_pct)
    else ''
  end
...
from
  yourtable


Instead of the function calculating the letter from percentage you could use a view to select from, of course. Be aware that the performance of queries against that view will likely suffer because of all the PL/SQL calls.