Tuesday, January 3, 2017

Learn to hate repetition - a lesson from APEX LOVs

If I was forced to pick just one piece of advice any developer should follow when writing code it would be: 

Avoid Repetition!
a.k.a, DRY - Don't Repeat Yourself
a.k.a., SPOD - Single Point of Definition

When the same code (business rule, formula, "magic" value, SQL statement, etc.) appears in more than one place, you create opportunities for bugs to creep into your code. How? You fix the problem in one place, but what about all the other places?

Here's an example of repetition in the PL/SQL Challenge APEX application and how I was able to get rid of the redundancy. 

I needed to add an LOV (list of values) to an item. I soon discovered that we had three LOVs already defined that were very similar to what I needed:



Now, just looking at the names of those LOVs made me shudder. They differ only by what appears to be the page number on which they are used. That didn't make much sense to me. So I drilled down in an attempt to gain clarify, and found that I was, indeed, correct. The only differences between these LOVs was the use of a different page item in the WHERE clause.


I had been using APEX for well over a year now by this time, but I still consider myself a novice and certainly lack an understanding of many of the nuances and limitations of the tool. Still, my quick glance at this situation had me thinking as follows:

1. It really would be better if page-specific item references were not stuck inside a named LOV. These LOVs do not "live" inside a single page and can, theoretically, be used across an entire application.

2. Surely there's got to be a way to generalize the query so that I can have a single LOV that can be used in all these locations.

So I clicked on the link in one of the LOVs to get some help and found:


Well, gee, that looks like a very useful approach. So I created a new LOV that does not contain the page number in its name, and does not contain a hard-coded page item reference in the query:


Now the only requirement for using this LOV is that the name of the item contain the domain ID is of the form:

:PNNN_domain_id

where NNN is the page number. So it's not quite completely generic, but it's a lot closer than before, and I was able to replace three LOVs with just one.

Goodbye (the worst of the) hardcoding, goodbye repetition!

And another nice reminder of how easy it is to build and execute dynamic SQL statements via PL/SQL. And you can see here, I changed a static query to a PL/SQL block that returns a string. That string will then be executed by the APEX engine via an EXECUTE IMMEDIATE call.

Note also that even though I concatenate text to put together my where clause, I do not introduce a SQL injection vulnerability. The :app_page_id bind variable is set by APEX itself. And the end result of the concatenation is a string that contains a bind variable.

Resources

Oracle Application Express (v5.1 was just released last month!)
Get Rid of Hard Coding in PL/SQL (a Practically Perfect PL/SQL YouTube playlist)

1 comment:

  1. dear sir, if you have three LOV item in the same page with different domain id. how would you solve it?

    ReplyDelete