Gotcha: Application Contexts

This is one of those feature behaviours which isn’t surprising, but you probably wouldn’t think about it unless you saw it.

The flop:

I make heavy use of application contexts in flexible, variable input, dynamically-built queries (one of the options mentioned in this asktom thread for example).

(In the latest edition of Oracle Magazine, Tom explains why this is not an approach he would recommend any more. I don’t necessarily agree but more of that another time perhaps)

The Turn:

My Query Builder – a set of code which I’ve always intended to make publicly available but have yet to do so – uses a context with single namespace and typically reuses attribute names between queries – e.g. a product id populated from an on-screen product selector will typically populate a context attribute called PRODUCT_ID.

And a Query Builder implementation will typically reset any context attributes(DBMS_SESSION.CLEAR_ALL_CONTEXT) at the start to clear any parameters from a previous query.

In one of the more complex scenarios, a controller proc constructs three different queries, based on the input parameters supplied, in order to return three resultsets to populate three different parts of a GUI. A bit like this dummy code:

CREATE OR REPLACE PROCEDURE p_get_matching_data (
  i_parameters             IN  some_array_type
  o_cursor1                 OUT SYS_REFCURSOR,
  o_cursor2                 OUT SYS_REFCURSOR,
  o_cursor3                 OUT SYS_REFCURSOR
)
AS
   l_query1 LONG;
   l_query2 LONG;
   l_query3 LONG;
BEGIN
   l_query1 := my_query_builder_pkg.build_query1(i_parameters);
   OPEN o_cursor1 FOR l_query1;
   l_query2 := my_query_builder_pkg.build_query2(i_parameters);   
   OPEN o_cursor2 FOR l_query2;
   l_query3 := my_query_builder_pkg.build_query3(i_parameters);
   OPEN o_cursor3 FOR l_query3;
END;

The River:

The problem with SYS_CONTEXT is that it is not bound into the query like a bind variable, nor is it subject to the same read consistency mechanism as block data.

Witness:

DOMINIC@11gR1>VAR r REFCURSOR
DOMINIC@11gR1>CREATE CONTEXT demo_ctx USING context_demo_pkg;

Context created.

Elapsed: 00:00:00.00
DOMINIC@11gR1>CREATE OR REPLACE PACKAGE context_demo_pkg
  2  AS
  3    --
  4    PROCEDURE p_set_context (
  5      i_attribute                IN     VARCHAR2,
  6      i_value                    IN     VARCHAR2
  7    );
  8    --
  9    PROCEDURE p_unset_context (
 10      i_attribute                IN     VARCHAR2
 11    );
 12    --
 13  END;
 14  /

Package created.

Elapsed: 00:00:00.00
DOMINIC@11gR1>CREATE OR REPLACE PACKAGE BODY context_demo_pkg
  2  AS
  3    --
  4    k_namespace CONSTANT VARCHAR2(24) := 'DEMO_CTX';
  5    --
  6    PROCEDURE p_set_context (
  7      i_attribute                IN     VARCHAR2,
  8      i_value                    IN     VARCHAR2
  9    )
 10    AS
 11    BEGIN
 12       --
 13       DBMS_SESSION.SET_CONTEXT
 14       (namespace => k_namespace,
 15        attribute => i_attribute,
 16        value     => i_value);
 17       --
 18    END p_set_context;
 19    --
 20    PROCEDURE p_unset_context (
 21      i_attribute                IN     VARCHAR2
 22    )
 23    AS
 24    BEGIN
 25       --
 26       DBMS_SESSION.CLEAR_CONTEXT
 27       (namespace => k_namespace,
 28        attribute => i_attribute);
 29       --
 30    END p_unset_context;
 31    --
 32  END;
 33  /

Package body created.

Elapsed: 00:00:00.00
DOMINIC@11gR1>

Now to demonstrate the different behaviour.

The bind variable:

DOMINIC@11gR1>DECLARE
  2   l_value  varchar2(24) := 'My Test Bind';
  3  BEGIN
  4    open :r for select l_value from dual;
  5    l_value := null;
  6  END;
  7  /

PL/SQL procedure successfully completed.

:B1
--------------------------------
My Test Bind

Elapsed: 00:00:00.00
DOMINIC@11gR1>

No surprises there.
Now the illustration of read consistency:

DOMINIC@11gR1>CREATE TABLE test_data
  2  (col1 VARCHAR2(1));

Table created.

Elapsed: 00:00:01.02
DOMINIC@11gR1>
DOMINIC@11gR1>INSERT
  2  INTO   test_data 
  3  VALUES ('A');

1 row created.

Elapsed: 00:00:00.00
DOMINIC@11gR1>BEGIN
  2     OPEN :r FOR SELECT * FROM test_data;
  3     DELETE FROM test_data;
  4     COMMIT;
  5  END;
  6  /

PL/SQL procedure successfully completed.

C
-
A

Elapsed: 00:00:00.00
DOMINIC@11gR1>

Again, no surprises. Read consistency around cursor opening/fetching as you might expect.
Next, the application context.

Firstly, just to prove that it works:

DOMINIC@11gR1>BEGIN
  2     context_demo_pkg.p_set_context('VALUE1','My Test Bind');
  3     OPEN :r FOR SELECT SYS_CONTEXT('DEMO_CTX','VALUE1') FROM dual;
  4  END;
  5  /

PL/SQL procedure successfully completed.

SYS_CONTEXT('DEMO_CTX','VALUE1')
-------------------------------------
My Test Bind

Elapsed: 00:00:00.00
DOMINIC@11gR1>

But if we reset it before we fetch from the cursor:

DOMINIC@11gR1>BEGIN
  2     context_demo_pkg.p_set_context('VALUE1','My Test Bind');
  3     OPEN :r FOR SELECT SYS_CONTEXT('DEMO_CTX','VALUE1') FROM dual;
  4     context_demo_pkg.p_unset_context('VALUE1');
  5  END;
  6  /

PL/SQL procedure successfully completed.


SYS_CONTEXT('DEMO_CTX','VALUE1')
---------------------------------------

Elapsed: 00:00:00.00
DOMINIC@11gR1>

So, once you see it, not entirely unsurprising but maybe not something that you’d considered before ( I hadn’t ). Behaviour neither like a bind nor read consistency like block data.

Application contexts

Not for the first time, I find myself writing a “flexible” query generating procedure that uses application contexts (SYS_CONTEXT) to generate specific sql statements (with binds, of course) based on the populated inputs.

I hope (as ever) to expand on this flexible query mechanism in a future article and might even provide a version for download. But the basic idea is that if you write SQL like this:


SELECT *
FROM emp e
WHERE e.empno = NVL(:empno,e.empno)

then you might end up with SQL that is not as efficient as it could be and it can even return false results. Typically you find this SQL coming from SQL generators, but I know that I’ve been guilty of this in the not-as-distant-as-I-might-hope past. Jonathan Lewis has a couple of articles on some of the implications.

Some queries at my current client take this generic query and take it to the extreme with tens of arguments, some of which are collections, and all of which can be null. As a result, the performance of some queries is reduced to accomodate the needs to the many possible parameters and subsequent joins.

The idea of the flexible query “generator” is to produce more specific SQL statements – we end up producing more individual SQL statements, but each more specific to the populated parameters. Which gives us a lot more flexibility when specific statements perform suboptimally.

Anyway, I digress from the original point of this article which was just to point out one aspect of SYS_CONTEXT behaviour that at best I had forgotten but I don’t think I knew before. This was causing an issue in my latest implementation.

The default maximum size of the returned value of the context is 256 bytes. The actual maximum value is 4000 bytes and this can be specified with a third argument to the SYS_CONTEXT.


SYS_CONTEXT(<namespace>,<parameter>,<length>)

So, a quick demo. At creation time, contexts need to be associated with a package that will be used to manage them. Presumably this is for security purposes so that they cannot be manipulated outside of that package (contexts are one of the features supporting fine-grained access control (FGAC)).

1. create context


CREATE CONTEXT demo_ctx USING pkg_ctx_demo;

2. create package


CREATE OR REPLACE PACKAGE pkg_ctx_demo
AS
--
PROCEDURE p_set_context (
i_ctx_parameter IN VARCHAR2,
i_ctx_value IN VARCHAR2
);
--
END pkg_ctx_demo;
/


CREATE OR REPLACE PACKAGE BODY pkg_ctx_demo
AS
--
PROCEDURE p_set_context (
i_ctx_parameter IN VARCHAR2,
i_ctx_value IN VARCHAR2
)
AS
BEGIN
--
DBMS_OUTPUT.PUT_LINE('Length of value is '||LENGTH(i_ctx_value));
DBMS_SESSION.SET_CONTEXT('DEMO_CTX',i_ctx_parameter,i_ctx_value);
--
END p_set_context;
--
END pkg_ctx_demo;
/

3. Call it:


begin
pkg_ctx_demo.p_set_context('MY_TEST_PARAMETER','My Test');
end;
/

4. select it (case insensitive parameter):


SQL>select sys_context('demo_ctx','my_test_parameter') from dual;

SYS_CONTEXT('DEMO_CTX','MY_TEST_PARAMETER')
-------------------------------------------------------------------------------------------
My Test

Elapsed: 00:00:00.00
SQL>

5. Check out length limit:


SQL>select
2 length('this is a long string, well not that long, but long enough to demo the issue 1, this is
a long string, well not that long, but long enough to demo the issue 2, this is a long string, well
not that long, but long enough to demo the issue 3, this is a long string, well not that long, but
long enough to demo the issue 4, ') len
4 from dual
5 /

LEN
----------
320

Elapsed: 00:00:00.00
SQL>


1 begin
2 pkg_ctx_demo.p_set_context('MY_LONG_TEST_PARAMETER','this is a long string, well not that lon
3* end;
>/
Length of value is 320

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00


1 select length(sys_context('DEMO_CTX','MY_LONG_TEST_PARAMETER')) default_length
2 , length(sys_context('DEMO_CTX','MY_LONG_TEST_PARAMETER',4000)) longer
3* from dual
SQL>

DEFAULT_LENGTH LONGER
-------------- ----------
256 320

Elapsed: 00:00:00.00
SQL>

Unfortunately, in this specific implementation, I need to go bigger than 4000 characters (for a comma-separated variable in-list) so a sys_context ceases to be viable. So I shall have to look at other options such as a normal bound variable, a packaged global variable or even a temporary table.

Follow

Get every new post delivered to your Inbox.

Join 68 other followers