Is there a way to query Oracle DB server name and use in conditional compilation?

Question

I got bit trying to maintain code packages that run on two different Oracle 11g2 systems when a line of code to be changed slipped by me. We develop on one system with a specific data set and then test on another system with a different data set.

The differences aren't tremendous, but include needing to change a single field name in two different queries in two different packages to have the packages run. On one system, we use one field, on the other system... a different one. The databases have the same schema name, object names, and field names, but the hosting system server names are different.

The change is literally as simple as

INSERT INTO PERSON_HISTORY 
  ( RECORD_NUMBER, 
    UNIQUE_ID, 
    SERVICE_INDEX, 
    [... 140 more fields... ] 
   ) 
 SELECT LOD.ID RECORD_NUMBER ,
      -- for Mgt System, use MD5 instead of FAKE_SSN
      -- Uncomment below, and comment out Dev system statement
      -- MD5 UNIQUE_ID ,
      -- for DEV system, use below
         '00000000000000000000' || LOD.FAKE_SSN  UNIQUE_ID , 
         null  SERVICE_INDEX ,
         [... 140 more fields... ] 
 FROM LEGACY_DATE LOD
 WHERE  (conditions follow)
 ;

I missed one of the field name changes in one of the queries, and our multi-day run is crap.

For stupid reasons I won't go into, I wind up maintaining all of the code, including having to translate and reprocess developer changes manually between versions, then transfer and update the required changes between systems.

I'm trying to reduce the repetitive input I have to provide to swap out code -- I want to automate this step so I don't overlook it again.

I wanted to implement conditional compilation, pulling the name of the database system from Oracle and having the single line swap automatically -- but Oracle conditional compilation requires a package static constant (boolean in this case). I can't use the sys_context function to populate the value. Or, it doesn't seem to let ME pull data from the sys_context and evaluate it conditionally and assign that to a constant. Oracle isn't having any. DB_DOMAIN, DB_NAME, or SERVER_HOST might work to differentiate the systems, but I can't find a way to USE the information.

An option is to create a global constant that I set manually when I move the code to the other system, but at this point, I have so many steps to do for a transfer that I'm worried that I'd even screw that up. I would like to make this independent of other packages or my own processes.

Is there a good way to do this?

-------- edit I will try the procedure and try to figure out the view over the weekend. Ultimately, the project will be turned over to a customer who expects to "just run it", so they won't understand what any switches are meant to do, or why I have "special" code in a package. And, they won't need to... I don't even know if they'll look at the comments. Thank you

Answer 1

As Mat says in the comments for this specific example you can solve with a view, however there are other ways for more complex situations.

If you're compiling from a filesystem or using any automatic system you can create a separate PL/SQL block/procedure, which you execute in the same session prior to compilation. I'd do something like this:

declare
   l_db varchar2(30) := sys_context('userenv','instance_name');
begin
   if l_db = 'MY_DB' then
      execute immediate 'alter session set plsql_ccflags = ''my_db:true''';
   end if;
end;
/

One important point; conditional compilation does not involve a "package static constant", but a session one. So, you need to ensure that your compilation flags are identical/unique across packages/sessions.