Use a Context namespace in a multi-tenant database?

Question

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

Answer 1

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