Doubly dynamic SQL

It is great to see a new post from Oracle WTF last week, after a quiet period. Which reminded me to post this example of a dynamic search.

I won't post the whole thing, and I have disguised the column names to protect the guilty. The basic problem is that the developer didn't quite understand that if you are going to generate a dynamic query, you don't have to include all the possibilities into the final SQL.

Let's say the example is based on books published in a given year. First, to decide whether to do a LIKE or an equality, he did this:

' WHERE' ||

' (('||p_exact||' = 1 AND pdc.title = '||chr(39)||p_title||chr(39)||')' ||

' OR ('||p_exact||' = 0 AND pdc.title LIKE '||chr(39)||l_title||'%'||chr(39)||')) ' ||

' AND ('||p_year||' = 0 OR pdc.year = '||p_year||')' ||

So at runtime you get both predicates coming through. Suppose you wanted an exact search (p_exact=1), for p_title='GOLDFINGER'. We don't know the year of publication so we supply 0. The generated predicates are:

WHERE ((1 = 1 AND pdc.title = 'GOLDFINGER')
OR (1 = 0 AND pdc.title LIKE 'GOLDFINGER%'))
AND (0 = 0 or pdc.year = 0)

Wouldn't the logically equivalent:

WHERE (pdc.title = 'GOLDFINGER')

have been much easier? Add a few of these together and a nice indexed query plan soon descends into a pile of full table scans and humungous hash joins. Oh, and no use of bind variables, so in a busy OLTP application this could sabotage the SQL cache quite quickly.

My favourite part though is with the sort order. The user can choose to order by a number of different columns, either ascending or descending:

l_order := ' ORDER BY ' ||

' case '||chr(39)||p_sort||chr(39)||' when ''publisher asc'' then publisher end asc, ' ||

' case '||chr(39)||p_sort||chr(39)||' when ''publisher desc'' then publisher end desc,' ||

' case '||chr(39)||p_sort||chr(39)||' when ''book_type asc'' then book_type end asc,' ||

' case '||chr(39)||p_sort||chr(39)||' when ''book_type desc'' then book_type end desc,' ||

' case '||chr(39)||p_sort||chr(39)||' when ''title asc'' then title end asc,' ||

' case '||chr(39)||p_sort||chr(39)||' when ''title desc'' then title end desc,' ||

' case '||chr(39)||p_sort||chr(39)||' when ''year asc'' then year end asc,' ||

' case '||chr(39)||p_sort||chr(39)||' when ''year desc'' then year end desc,' ||

' case '||chr(39)||p_sort||chr(39)||' when ''book_id asc'' then book_id end asc,' ||

' case '||chr(39)||p_sort||chr(39)||' when ''book_id desc'' then book_id end desc';

Yes, you got it; given that the variable p_sort has been picked from an LOV, the whole piece of PL/SQL can be replaced by:

l_order := ' ORDER BY ' ||p_sort;

That looks better, doesn't it?