1

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.

  • I wonder if you could do something with a pipelined table function? But I'm not sure how the function would detect the difference in "context" -- maybe a context package in the session? – Colin 't Hart Aug 14 '13 at 17:24
  • Thanks Colin. At first look I'm not sure how I could use that without modifying the querying packages, but I'll think about it. – Perry Ulmer Aug 14 '13 at 18:21
1

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.

  • Thanks, but I think I have the problem of identifying the calling package already sorted. My problem now is how to alter the query results without changing the package. Of course I can do the %-to-letter translation in a view, but then I have to change all the packages to query the view instead of the table, which is what I'm trying to avoid. – Perry Ulmer Aug 14 '13 at 18:24
  • May be I misunderstand something, but the point is to rename the original table and create the view named as the original table. – mustaccio Aug 14 '13 at 18:33
  • Thanks mustaccio, that would do it, but it's not an option. There are actually several tables, they're core tables for a very large complex ERP application, and my DBAs wouldn't go for renaming base tables, partly because of upgrade and maintenance issues (the ERP is a 3rd-party product). – Perry Ulmer Aug 14 '13 at 18:50
1

Just to finish this up... I've come to the conclusion that the only option is to mod the packages. To keep the mods as lightweight as possible we'll create a simple view on each table with a Grade column, replacing the % with letter, so the only mods should be changing table names to view names.

Thanks for your comments, folks.

Perry.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.