Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
Nice simple idea. I think it'll work nicely, including that you include the schema in the context name. Performance in queries might be an issue, although this might be helped with RESULT_CACHE. –  Jeffrey Kemp Feb 7 '13 at 12:00
    
There might be a small overhead over using sys_context but in most queries this difference will be negligible compared to the actual work implied by a query. I'm not sure function result cache would apply here since this function will not return the same results for all sessions. –  Vincent Malgrat Feb 7 '13 at 12:23
    
Thanks, good point - result cache is exactly the opposite of what I'd want. –  Jeffrey Kemp Feb 8 '13 at 0:48
1  
Maybe this trick will help: docs.oracle.com/cd/E11882_01/appdev.112/e25519/… –  Jeffrey Kemp Feb 8 '13 at 0:54
    
Made the change. No significant change to performance as far as I can see, so that's good. I'm a bit happier that the details about the context are encapsulated better - no more calls to SET_CONTEXT sprinkled all over the app. –  Jeffrey Kemp Feb 8 '13 at 1:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.