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.

About these ads

9 Responses to Gotcha: Application Contexts

  1. Toon Koppelaars says:

    The same happens if your SELECT list contains a stored function call. The function is called upon fetch of rows, and sees its own snapshot of the database.

    SQL> select * from test;

    I
    ———-
    1
    2

    SQL> select text from user_source where name=’F';

    TEXT
    ——————————————————————————–
    function f return number as i number; begin select count(*) into i from test; return i; end;

    SQL>
    1 BEGIN
    2 OPEN :b1 FOR SELECT f as cnt FROM dual;
    3 DELETE FROM test;
    4 COMMIT;
    5* END;
    SQL> /

    PL/SQL procedure successfully completed.

    SQL> print b1

    CNT
    ———-
    0

    SQL>

    This doesn’t happen if we embed the count(*) into the query itself:

    SQL> select * from test;

    I
    ———-
    1
    2

    SQL>
    SQL> BEGIN
    OPEN :b1 FOR SELECT (select count(*) from test) as cnt FROM dual;
    DELETE FROM test;
    COMMIT;
    END;
    /

    PL/SQL procedure successfully completed.

    SQL> print b1

    CNT
    ———-
    2

    SQL>

    Something to be aware of indeed.

    • dombrooks says:

      Hi Toon,

      I’d expect it to happen with a function call.
      I understand it, I expect it, I’m happy with it.

      But with sys_context, I understand it but it caught me unawares at the time. And I’m still not sure I’m 100% happy about it.

      Cheers,
      Dominic

  2. Milo says:

    Thank you for posting this. I am the one who started the asktom thread you referenced, and it’s nice to see that others are interested in the same problem.

    I’ll probably add a reference to your post to that thread, as well as Tom’s new position, just to keep it relevant for the next guy.

    • dombrooks says:

      Cheers Milo.

      It’s a solution I’ve implemented many times, going back many years further than that thread :) but it is a good example of what I was talking about for anyone unfamiliar with the topic.

      It’s worked for me many times with good effect and I’ve been meaning to put my latest version out there for public consumption as you often see the same question on the forums, etc.

      Interesting that it’s not a solution that Tom would recommend now.

      Thanks for stopping by.

  3. Toon says:

    “I’d expect it to happen with a function call.
    I understand it, I expect it, I’m happy with it.”

    Yes, but I still feel it should not behave in this way.
    The result set of a query should be read-consistent: it should be a result that was true for a database state that existed at a single point in time.
    Also it should (I think) not be too difficult for Oracle to “fix” this: just provide the SCN to the pl/sql engine on context switch and have the pl/sql engine see the state from that point-in-time.

  4. Pingback: Log Buffer #154: a Carnival of the Vanities for DBAs | Pythian Group Blog

  5. Mark Brady says:

    Tom Kyte says that Sys_context are bind variables. I’m not clear in your demo where you’re proving that to be false. Can you clarify?

  6. Dom Brooks says:

    Hi Mark,

    Certainly we should think of contexts as like bind variables, certainly from a performance and cursor reuse perspective.

    Contexts are like bind variables but with one important difference. Bind variables are bound into your cursor at execution time – you open the cursor, the value of those binds for that execution cannot change.

    Binds can be peeked etc.

    Contexts aren’t peeked and one of the reasons is presumably because the value is not necessarily fixed, because the value of the context could be changed dynamically during the execution of the cursor – i.e you could say they exhibit read-consistency issues with respect to your cursor if the value was changed by something whilst your select was executing.

    In the very last bit of the code demo above in the article, you see that I opened the cursor with one value in the context and then changed the value of the context before fetching from the cursor. And that change was shown when fetching from the cursor.

    It’s one of the reasons why you can use application contexts to get around vpd not working with ora_rowscn – one of the workarounds is to use a policy which sets a context, the value of which changes as the policy fires for each row (can’t find the link just now to illustrate) – I appreciate that might be a bit of an out of the blue comment but the penny just dropped for me.

    If it’s not clear, let me know and I’ll expand further.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 69 other followers

%d bloggers like this: