Substitution Variables over a Database Link in APEX
Posted in Apex, Oracle, Smart Queries on 12/02/2011 11:35 am by Michel van ZoestSituation: 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.
