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:

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:

TL;DR: Don’t concatenate variables or columns in your SQL

Don’t do this in SQL:

Do:

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 a Senior APEX Consultant with Insum Solutions, a consulting firm specialized in Oracle databases and the APEX development tool. I have worked with Oracle since 1995 and done eBusiness Suite implementations and customizations. Nowadays I specialize almost exclusively in Oracle APEX.

Posted in APEX, Oracle, Performance, Video
5 comments on “Video: 006 – Substitution Strings, Bind Variables, and APEX Links
  1. Awesome explanation! I know i’m guilty of using substitution variables in strings. This will help remind me to use bind variables.
    Thanks!

  2. 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.

  3. sahithi says:

    Hi,
    Thanks for the information provided by you on strings.i have learnt some new points from this post.
    Thank you.

  4. Scott says:

    I’ve also covered the danger of substitution strings in queries here
    http://www.grassroots-oracle.com/2012/02/apex-variables-in-sql.html

I love comments, write me a line