Substitution Variables over a Database Link in APEX

Situation: APEX 3.1.2 on a 9.2.0.6.0 database linking to Siebel.

When I was building a report on 2 tables, one local and one remote, I noticed a very big performance issue. I thought I had solved this by adding a DRIVING_SITE hint, but this wasn’t enough.

The query looked something like this:

select /*+ DRIVING_SITE(e) */
e.ename
, d.dname
from emp e
, dept@remote d
where d.id = e.dept_id
and d.loc = :P2_DEPT_LOC

In the page, a user can select a Department Location and press a button. The report data is then generated based on this selected location.

To debug this, I started investigating trace files on the remote database. What I immediately noticed was, that the Substitution Variable :P2_DEPT_LOC wasn’t replaced by it’s value that was selected in the APEX page. So that was the problem.

To solve this, I simply had to replace the way the Substitution Variable was called. The query was changed to:


select /*+ DRIVING_SITE(e) */
e.ename
, d.dname
from emp e
, dept@remote d
where d.id = e.dept_id
and d.loc = '&P2_DEPT_LOC.'

And everything was okay. The time it took to load the page changed from 45 minutes to 0.05 seconds.

But be aware! This kind of solutions opens the door for SQL Injection, so make sure that you handle the input before parsing the query.

4 thoughts on “Substitution Variables over a Database Link in APEX

  1. Just curious, what did your remote trace show? Was the bind/substitute really the problem? Or was the predicate not pushed to the remote site at all?

    Did you really want the remote site to be the driver rather than the local?

    You should not have to do this, but try rearranging the query

    SELECT e.ename, d.dname
    FROM emp e,
    (SELECT id, dname
    FROM dept@remote d
    WHERE d.loc = :p2_dept_loc) d
    WHERE d.id = e.dept_id;

    if rearranging does help, perhaps the problem is OPTIMIZER_MAX_PERMUTATIONS set too low

  2. Thanks for your comments Roel and Alex!

    @Roel: Yes, I do know that. But given the fortunate circumstances for this application, that’s not a problem.
    Both your other suggestions resulted in the same problem.

    @Alex: SQL injection is handled in the real application. It’s a very good consideration, so I’ll mention it in the blog post. And you’re right, it’s not a bind variable. I’ll change that in the text.

  3. It’s not a bind variable, it’s a substitution variable. Now enter: x’ or ‘a’=’a
    in P2_DEPT_LOC… SQL Injection.

  4. Hi Michel,
    Be aware that your solution will result in a hard parse for every value of P2_DEPT_LOC. Might not have a serious impact now in your situation, but is not preferable.
    Did you also try either v(‘P2_DEPT_LOC’) or (select :P2_DEPT_LOC from dual) ?

Comments are closed.