Retrieve Oracle database name without querying

Question

I have two Oracle 11gR2 databases (one for testing purposes and the other is production).

By using conditional compilation I need to create a function that will use database link in test environment and will not use database link in production.

The problem is that I need to put that into package body which will be automatically compiled in both environments. There will be no such database link in production environment, therefore the package will not compile.

One solution would be to create a dummy database link which in production environment resolves to itself (production -> production), but that surely is not the right way to solve this problem.

For example:

DECLARE 
    v_db_name     VARCHAR2(200);
    vt_tbl_data   data_table%ROWTYPE;
BEGIN
    $IF v_db_name = 'TEST' $THEN 
        SELECT tbl.*
          INTO vt_tbl_data
          FROM data_table@otherdb tbl
         WHERE ROWNUM = 1;
    $ELSIF v_db_name = 'PROD' $THEN
        SELECT tbl.*
          INTO vt_tbl_data
          FROM data_table tbl
         WHERE ROWNUM = 1;
    $END
END;
/

How does the automatic compilation occur - do you have the ability to alter the session the compilation happens in? (Presumably the schema names are the same - otherwise you could check that more easily...) — Alex Poole
– Alex Poole, Commented Jan 12, 2018 at 19:04
PLSQL_CCFLAGS can be used. You can do ALTER SYSTEM SET PLSQL_CCFLAGS = 'env_test:true'; on testing database, and then use $IF $$env_test $THEN xxxxxx $ELSE yyyyyy $END. Please see the documentation for details. However I like the idea with synonym the most. — krokodilko
– krokodilko, Commented Jan 12, 2018 at 19:07
No, I'm unable to change the compilation sequence and alter the session. However, thanks to both of you for your comments. Didn't know about PLSQL_CCFLAGS until now. — Alexander Gusev
– Alexander Gusev, Commented Jan 12, 2018 at 20:15

Sportball · Accepted Answer · 2018-01-12 18:35:46Z

4

Don't have the database link syntax in your package body. Create a synonym in both environments and reference the synonym in your package body.

That way your package bodies are the same in both environments - only the definition of the synonyms are different.

answered Jan 12, 2018 at 18:35

Sportball

9538 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2018-01-12 19:20:25Z

2

If you can alter the compilation session - in either or both environments - you could use a compilation flag via PLSQL_CCFLAGS:

alter session set PLSQL_CCFLAGS = 'has_db_link:TRUE';

DECLARE 
    vt_tbl_data   data_table%ROWTYPE;
BEGIN
    $IF $$has_db_link $THEN 
        SELECT tbl.*
          INTO vt_tbl_data
          FROM data_table@otherdb tbl
         WHERE ROWNUM = 1;
    $ELSE
        SELECT tbl.*
          INTO vt_tbl_data
          FROM data_table tbl
         WHERE ROWNUM = 1;
    $END
END;
/

or with less repetition, but maybe harder to follow:

DECLARE 
    vt_tbl_data   data_table%ROWTYPE;
BEGIN
    SELECT tbl.*
      INTO vt_tbl_data
    $IF $$has_db_link $THEN 
      FROM data_table@otherdb tbl
    $ELSE
      FROM data_table tbl
    $END
     WHERE ROWNUM = 1;
END;
/

If you set the flag to FALSE, or don't set it at all, in production then it will only compile the $ELSE branch.

You can't set the flag to a string, so you can't test for a specific name; but true/false seems more intuitive to me anyway.

If you can't change how the compilation happens in order to alter the session, you could potentially do that via a logon trigger - perhaps only in the test environment in that case, and maybe with a more general flag name like env_test as @krokodilko suggested.

edited Jan 12, 2018 at 19:20

answered Jan 12, 2018 at 19:08

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

1 Comment

Alexander Gusev Over a year ago

Thanks for your answer, however I am unable to change how the compilation happens. I'm sure your answer could help some other folks, too.

Collectives™ on Stack Overflow

Retrieve Oracle database name without querying

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related