Video: 006 – Substitution Strings, Bind Variables, and APEX Links
“Substitution Strings, Bind Variables, and APEX Links” oh my.
Have you ever build an APEX link manually and perhaps it looked something like this:
'f?p=&APP_ID.:PAGE:&SESSION.::&DEBUG.:'
We should talk.
I used to make this mistake and I’ve seen it made too many times. It’s time to address it head on.
In this video, I will show you why there may be a much better way and why you should be careful. Of course, there are other ways of building links dynamically, like using APEX_UTIL.PREPARE_URL which could still be affected. Perhaps a better approach would be to use the “new” (in APEX 5.0) APEX_PAGE.GET_URL
In the video I comment on these links:
- Parsing … no big deal eh ? (You’ll want to read this one!
- Conditional column linking in APEX – Caution, the blog post used the &SESSION. approach, but Tony did realize this and noted it in the comments
TL;DR: Don’t concatenate variables or columns in your SQL
Don’t do this in SQL:
'f?p=&APP_ID.:PAGE:&SESSION.::&DEBUG.:'
Do:
'f?p=' || :APP_ID || ':PAGE:' || :APP_SESSION || '::' || :DEBUG || ':'
You’ll avoid flooding your shared pool memory with unique SQL statements that cannot be re-used.
Hi, I'm Jorge Rimblas. Father, husband, photographer, Oraclenerd, Oracle APEX expert, Oracle ACE, coffee lover, car guy, gadget addict, etc... I'm an APEX Tech Lead DRW. I have worked with Oracle since 1995 and done eBusiness Suite implementations and customizations. Nowadays I specialize almost exclusively in Oracle APEX.
Awesome explanation! I know i’m guilty of using substitution variables in strings. This will help remind me to use bind variables.
Thanks!
If your target page is checksum protected, you also need a call of APEX_UTIL.PREPARE_URL around the string. Since 5.0 there is also APEX_PAGE.GET_URL, which has useful defaults and automatically calls PREPARE_URL.
Yes, absolutely good point.
In my demo app, since I used a column of type “Link” the checksum is automatically calculated, which is a pretty handy feature.
Thanks!
Hi,
Thanks for the information provided by you on strings.i have learnt some new points from this post.
Thank you.
I’ve also covered the danger of substitution strings in queries here
http://www.grassroots-oracle.com/2012/02/apex-variables-in-sql.html