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:

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.


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

PROCEDURE p_set_context (
i_ctx_parameter IN VARCHAR2,
i_ctx_value IN VARCHAR2
END pkg_ctx_demo;

PROCEDURE p_set_context (
i_ctx_parameter IN VARCHAR2,
i_ctx_value IN VARCHAR2
DBMS_OUTPUT.PUT_LINE('Length of value is '||LENGTH(i_ctx_value));
END p_set_context;
END pkg_ctx_demo;

3. Call it:

pkg_ctx_demo.p_set_context('MY_TEST_PARAMETER','My Test');

4. select it (case insensitive parameter):

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

My Test

Elapsed: 00:00:00.00

5. Check out length limit:

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 /


Elapsed: 00:00:00.00

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

-------------- ----------
256 320

Elapsed: 00:00:00.00

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.


11 Responses to Application contexts

  1. rc says:

    You can do this without sys_context, you can use the method of Darko Egersdorfer. See . You can use execute immediate and bind a nested table. We generate this kind of statements.

    Another method if you use Oracle 11, use dbms_sql and dbms_sql.to_refcursor: (see my comments on this page (rc from the Netherlands)) .

  2. dombrooks says:

    Thanks for your comments RC.

  3. dombrooks says:

    I’ve read Darko’s method in that article and, personally, I see little advantage over generating a sql statement without the dummy clause – you end up with the same number of unique sql statements after all. There is little difference in complexity and I prefer not supplying these dummy clauses which do nothing.

    For me, logic which determines whether to add a where clause or not, add a table or not, even add a column or not, and only adds those that are required is preferable. And why bind 30 variables and have a whole bunch of dummy clauses, when a significant portion of the time only one or two is supplied?

  4. rc says:

    Those dummy clauses are certainly ‘strange’.

    I don’t know which method performs better. We use a method very similar to Darko’s method and we can bind inlists (nested tables). One of my colleagues has build a tool to geneate PL/SQL code very similar to Darko’s method.

    I don’t know if my colleague has compared the performance of these two methods.

    Sometimes the nested table for the in list has more than one column, it is not really easy to use a multi column nested table as an ‘in’ parameter when you call a stored procedure in C#.

    We hope that the UDT support of the new ODP.NET release makes this easier.

  5. Milo says:

    In a question on Ask Tom I compiled a list of various ways to do this. It’s interesting to see how many bad ways there are for binding to dynamic SQL.

    For me, since I’m still stuck on 8i, I’ve opted to stick with DBMS_SQL since it allows me to bulk fetch the rows and doesn’t impose a 255-character limit on bound values.

  6. dombrooks says:

    I’ve only worked on a couple of C# projects and only one of those used the ODP.NET drivers, the other used DataDirect.

    The difference in support for UDT between ODP.NET and JDBC has always annoyed me. I did see that this has been addressed in the latest ODP.NET release, to what extent I don’t know.

    My current client (C#) passes everything back and forward as XML to get around these sorts of issues but with some overhead of XML processing.

  7. dombrooks says:

    Thanks for your comments Milo.

    I use method 4 from your question, no doubt because that was the method that Tom originally advocated in questions such as yours and in his Expert One-on-One Oracle. Although I think I remember using the “dummy” clause method nearly ten years ago before I had tried other ways.

    My current client will shortly be going to 11g so maybe I’ll get an opportunity to review my preferred method on that platform.

  8. Pingback: Been busy but blog’s been quiet « OraStory

  9. Andre says:

    Here’s another way to construct a dynamic query with correctly typed binds and no dummy clauses. See the following (close to the bottom):

  10. dombrooks says:

    Thanks Andre. Most of the methods have the same concepts at heart. I’ve had to do the same thing about five or six times over the last six years for a variety of clients.

    I still need to post the latest incarnation of my solution. Just need to sanitize it first. Next week maybe.

  11. rc says:

    dbms_sql.to_refcursor doesn’t work with .NET .

    With dbms_sql.to_refcursor one can convert a dbms_sql cursor to a ref cursor (new in Oracle 11) but for some reason one can’t use a converted ref cursor in .NET .

    See here:

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: