Use a Context namespace in a multi-tenant database?

I'm developing an Apex application that uses database Contexts in conjunction with Row Level Security. I'd like to install a second copy of the schema and workspace on the same instance; this works except for the Context, which is a global thing.

For example, in schema "DEV", I create the context:

CREATE OR REPLACE CONTEXT myapp_ctx USING myapp_pkg;

When I query dba_context, I see my context has been created:

NAMESPACE  SCHEMA  PACKAGE    TYPE
=========  ======  =========  ================
MYAPP_CTX  DEV     MYAPP_PKG  ACCESSED LOCALLY

In schema "TEST", I create the context as before:

CREATE OR REPLACE CONTEXT myapp_ctx USING myapp_pkg;

This has the effect of removing the dev context and replacing it with the test one:

NAMESPACE  SCHEMA  PACKAGE    TYPE
=========  ======  =========  ================
MYAPP_CTX  TEST    MYAPP_PKG  ACCESSED LOCALLY

I've got the context name hardcoded in various parts of the application (e.g. views, packages, etc) and would rather not have it differ between environments if I can help it.

At the moment my only idea is to parameterize the namespace name in the deployment scripts - so that the namespace will be e.g. MYAPP_CTX_DEV or MYAPP_CTX_TEST depending on a configuration option in the deployment scripts.

Are there any better alternatives that allow me to install multiple versions of my application on the one instance?

Oracle version 11.1.0.7

You could encapsulate the logic into the packages themselves:

SQL> CREATE OR REPLACE PACKAGE pkg IS
  2     g_context VARCHAR2(30);
  3     FUNCTION  get_ctx(p_variable VARCHAR2) RETURN VARCHAR2;
  4     -- set_ctx may be private of course
  5     PROCEDURE set_ctx(p_variable VARCHAR2, p_value VARCHAR2);
  6  END;
  7  /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY pkg IS
  2  
  3     FUNCTION  get_ctx(p_variable VARCHAR2) RETURN VARCHAR2 IS
  4     BEGIN
  5        RETURN sys_context(g_context, p_variable);
  6     END get_ctx;
  7  
  8     PROCEDURE set_ctx(p_variable VARCHAR2, p_value VARCHAR2) IS
  9     BEGIN
 10        dbms_session.set_context(g_context, p_variable, p_value);
 11     END set_ctx;
 12  
 13  -- set ctx dynamically
 14  BEGIN
 15     g_context := 'MYCTX_'||sys_context('userenv', 'current_schema');
 16  END;
 17  /

Package body created

Let's create the context and call it :

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'CREATE CONTEXT ' || pkg.g_context || ' USING PKG';
  3  END;
  4  /

PL/SQL procedure successfully completed

SQL> exec pkg.set_ctx ('a', '123');

PL/SQL procedure successfully completed

SQL> select pkg.get_ctx('a') from dual;

PKG.GET_CTX('A')
--------------------------------------------------------------------------------
123

StackExchange Database Administrators Q#34110, answer score: 2