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;
/

Answer 1

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.