ORACLE SQL : Get instance name as global variable/constant-CodePudding

I want to read instance name and set some constant to true if the condition is correct.

For example i have already this

--DYNAMICALLY CREATE A PACKAGE TO HOLD CONSTANTS.
BEGIN
    EXECUTE IMMEDIATE
    '
    CREATE OR REPLACE PACKAGE XXX_COMPILATION_CONSTANTS IS 
    C_MAKE_PUBLIC_XXX_SCHEMA CONSTANT BOOLEAN := '||CASE WHEN USER = 'TEST' THEN 'TRUE' ELSE 'FALSE' END||';
    END;
    ';
END;

How can I read instance name like USER above ?

--DYNAMICALLY CREATE A PACKAGE TO HOLD CONSTANTS.
BEGIN
    EXECUTE IMMEDIATE
    '
    CREATE OR REPLACE PACKAGE XXX_COMPILATION_CONSTANTS IS 
    C_MAKE_PUBLIC_XXX_SCHEMA CONSTANT BOOLEAN := '||CASE WHEN **INSTANCE**= 'MY_TEST_DB' THEN 'TRUE' ELSE 'FALSE' END||';
    END;
    ';
END;

CodePudding user response：

My guess is that you probably don't want to create your own package. The USERENV context probably already has all the information you want.

sys_context( 'USERENV', 'CURRENT_USER' )

for the current user and

sys_context( 'USERENV', 'INSTANCE_NAME` )

for the name of the instance. In the documentation I linked to, you can see that there are a bunch of other attributes in that context that are already populated with useful bits of information.

CodePudding user response：

Your example works if you use sys_context('userenv','instance_name'):

begin
    execute immediate
    'create or replace package xxx_compilation_constants as 
    c_make_public_xxx_schema constant boolean := '||
        case
            when sys_context('userenv','instance_name') = 'MY_TEST_DB' then 'TRUE'
            else 'FALSE'
        end||';
    end;
';
end;

which creates a package as:

create or replace package xxx_compilation_constants as
    c_make_public_xxx_schema constant boolean := FALSE;
    end;

However, it would be simpler to define the package statically as:

create or replace package xxx_compilation_constants
as
    c_make_public_xxx_schema constant boolean :=
        sys_context('userenv','instance_name') = 'MY_TEST_DB';
end;

You might also look at conditional compilation, though I'm not really seeing a use for it in your example.