Let APEX do the work for you

Replace ORACLE APEX substitution strings at once

apex_application.do_substitutions function

Just by accident I recently bumped into this function on Martin's blog from 2009.

Since I was in a need for something like this I thought how come people do not talk about this function more often as it can come very handy. So thanks Martin! :)

We were working on an application where users would store their links in database. Initially I was instinctively going with PL/SQL replace function (or regular expression way) but since recursive-ness became messy I was wondering if there is APEX build in function that could help me with this.    

As I had problems finding anything I decided to blog about it. This is a whole point of writing blogs so people can easily find things like this to save them time and give them access to these nice little features of APEX.

For demo purpose imagine something like this to be within your table column:

This is &APP_ID. with alias &APP_ALIAS. and &APP_SESSION.

Now if you wanted to build a simple report region (or use this data in your APEX page) most people would jump to using a REPLACE() function to get

But wait you can simply use apex_application.do_substitutions() that will do this for you. :)

Then in my demo example simply use 

select  apex_application.do_substitutions ('This is &APP_ID. with alias &APP_ALIAS. and &APP_SESSION.') content_with_subs FROM dual

and that is it. 

Of course a demo example is just a dummy but any of standard APEX links would be handled as well i.e. - 'f?p=&APP_ID.:DEMO:&SESSION_ID.::::::'

Or if you have some application defined substitution strings for your local images folder and JS files that are stored as part of column data.

Let APEX handle replacement of all these variables for you and no replace functions are needed.

Only note is that this function will only render the correct values once it runs withing your APEX page.

Regards,

SLino