DDevelopment & Design

Weird Oracle

  • bypiotrp
  • October 4, 2011
  • 2 minute read

“It’s not a bug, it’s a feature” PL/SQL like any other procedural extension to SQL has the ability to execute dynamic statements: EXECUTE IMMEDIATE. But not everyone knows it works differently for SQL statements and PL/SQL blocks. The difference lies in parameters passing.

Consider a simple example when we need to add a new row to a table using dynamic statement:

BEGIN
  p_date := to_char(SYSDATE);
  EXECUTE IMMEDIATE 'INSERT INTO test (created, modified, id, value)
      VALUES ('||p_date||', '||p_date||', '||p_id||', '||p_value||')';
END;

It works, but has a serious flaw: a new statement is compiled for every set of parameters and for every call. We should use placeholders in the statement and pass values through USING clause. To my great surprise, even experienced Oracle programmers may have problems to do it right:

BEGIN
  p_date := to_char(SYSDATE);
  EXECUTE IMMEDIATE 'INSERT INTO test (created, modified, id, value)
      VALUES (:p_date, :p_date, :p_id, :p_value)';
  USING (p_date, p_id, p_value);
END;

Looks good? But id does not work. According to specification when calling SQL statements, Oracle does not even look at placeholders names but on number and order of placeholders – every placeholder needs precisely one argument on the USING list. The correct way to do it is:

BEGIN
  p_date := to_char(SYSDATE);
  EXECUTE IMMEDIATE 'INSERT INTO test (created, modified, id, value)
      VALUES (:x, :x, :x, :x)';
  USING (p_date, p_date, p_id, p_value);
END;

Notice repeated p_date in using clause. Repeating of the placeholder name is also intentional – i think it might help notice that one need to be cautious when modifying this piece of code. Now to make things even more confusing, assume that we add a procedure to insert that row but still need to call it dynamically. This time Oracle will behave differently: it will now look at placeholder names and will expect only one value per placeholder name:

BEGIN
  p_date := to_char(SYSDATE);
  EXECUTE IMMEDIATE 'BEGIN insert_into_test (:p_date, :p_date, :p_id, :p_value); END;';
  USING (p_date, p_id, p_value);
END;

Now the total weirdness: USING clause has no way of specifying placeholder name for each argument – here still only the order counts. Reading such a piece of code and trying to decipher which parameter gets which value may be painful:

BEGIN
  p_date := to_char(SYSDATE);
  EXECUTE IMMEDIATE 'BEGIN some_proc (:p_date, :p_user, :p_date, :p_id, :p_value, :p_user); END;';
  USING (...???...);
END;

Now imagine that the dynamic block consists of several calls with some common arguments and that the block itself is created programmatically… I bet one will quickly use unique placeholder names (like :p1, :p2, :p3,…) and pass each value multiple times or give up parameter passing entirely and use string concatenation method instead. And if you are still reading this – a short riddle:

EXECUTE IMMEDIATE 'call some_proc(:a, :a, :b, :c);' USING (...);

How many values should be passed here?

— Previous article

Grammar parser in C++

Next article —

TouK sponsorem Warsjawa 2011

You May Also Like
DDevelopment & Design

After WHUG meeting

Here are the slides from the talk a gave yesterday. If you have any questions, please ask. Here are the slides from the talk a gave yesterday. If you have any questions, please ask.
DDevelopment & Design

Log4j and MDC in Grails

Log4j provides very useful feature: MDC - mapped diagnostic context. It can be used to store data in context of current thread. It may sound scary a bit but idea is simple.

My post is based on post http://burtbeckwith.com/blog/?p=521 from Burt Beckwith's excellent blog, it's definitely worth checking if you are interested in Grails.

Short background story...


Suppose we want to do logging our brand new shopping system and we want to have in each log customer's shopping basket number. And our system can be used at once by many users who can perform many transactions, actions like adding items and so on. How can we achieve that? Of course we can add basket number in every place where we do some logging but this task would be boring and error-prone. 

Instead of this we can use MDC to store variable with basket number in map. 

In fact MDC can be treated as map of custom values for current thread that can be used by logger. 


How to do that with Grails?


Using MDC with Grails is quite simple. All we need to do is to create our own custom filter which works for given urls and puts our data in MDC.

Filters in Grails are classes in directory grails-app/conf/* which names end with *Filters.groovy postfix. We can create this class manually or use Grails command: 
grails create-filters info.rnowak.App.Basket

In result class named BasketFilters will be created in grails-app/conf/info/rnowak/UberApp.

Initially filter class looks a little bit empty:
class BasketFilters {
    def filters = {
        all(controller:'*', action:'*') {
            before = {

            }
            after = { Map model ->

            }
            afterView = { Exception e ->

            }
        }
    }
}
All we need to do is fill empty closures, modify filter properties and put some data into MDC.

all is the general name of our filter, as class BasketFilters (plural!) can contain many various filters. You can name it whatever you want, for this post let assume it will be named basketFilter. 

Another thing is change of filter parameters. According to official documentation (link) we can customize our filter in many ways. You can specify controller to be filtered, its actions, filtered urls and so on. In our example you can stay with default option where filter is applied to every action of every controller. If you are interested in filtering only some urls, use uri parameter with expression describing desired urls to be filtered.

Three closures that are already defined in template have their function and they are started in these conditions:

  • before - as name says, it is executed before filtered action takes place
  • after - similarly, it is called after the action
  • afterView - called after rendering of the actions view
Ok, so now we know what are these mysterious methods and when they are called. But what can be done within them? In official Grails docs (link again) under section 7.6.3 there is a list of properties that are available to use in filter.

With that knowledge, we can proceed to implementing filter.

Putting something into MDC in filter


What we want to do is quite easy: we want to retrieve basket number from parameters and put it into MDC in our filter:
class BasketFilters {
    def filters = {
        basketFilter(controller:'*', action:'*') {
            before = {
                MDC.put("basketNumber", params.basketNumber ?: "")
            }
            after = { Map model ->
                MDC.remove("basketNumber")
            }
        }
    }
}

We retrieve basket number from Grails params map and then we put in map under specified key ("basketNumber" in this case), which will be later used in logger conversion pattern. It is important to remove custom value after processing of action to avoid leaks.

So we are putting something into MDC. But how make use of it in logs?


We can refer to custom data in MDC in conversion patter using syntax: %X{key}, where key is our key we used in filter to put data, like:
def conversionPattern = "%d{yyyy-MM-dd HH:mm:ss} %-5p %t [%c{1}] %X{basketNumber} - %m%n"


And that's it :) We've put custom data in log4j MDC and successfully used it in logs to display interesting values.