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 62 other followers