Extending apex.env on Oracle APEX ... an Open Source story

I really like apex.env. It allows you to access information about your Application through JavaScript. For example you can use it to find the current APP_ID or APP_PAGE_ID

What does frequently annoy me though, is when I want to grab a value from there… like the Application Name… its just not there.

Wouldn’t it be nice to extend it? Yep you can.. I was going to write a thing to do it, however there is already a Oracle APEX Process Plug-in called Load JSON Object written by Stefan Dobre to add JSON objects to pages.

I’ve known about this plug-in for years and used it on occasion (but not for a while now). Wwhat’s cool about this is that it adds a JSON Object into a JavaScript variable based on SQL or PL/SQL into the page at render time. Being at render time is magic as it’s baked into the page load rather than at any point after that - which may cause timing issues with page load JS or Dynamic actions 👌

The problem was that the latest version was 1.0 in 2019 (7 years ago) and it was designed for Oracle APEX 18. 2 and unfortunately it now doesn’t work in APEX 24.2.

Therefore since this was a commercially supported plug-in, I checked out the support route and found:

I could just contact Stefan - but I didn’t (because I thought it would be fun to fix it myself)
“The guys at FOEX also offer commercial support for this plug-in.”. They did, at one point, adopt support, for this Plug-in. So, I tried http://foex.at/ however it just bounces to a “Oracle Acquisitions - Oracle Buys FOEX“ page
There still exists a FOS Plug-ins page. However its not listed on there either.
Then I remembered UC has adopted the FOS Plug-ins, however this one wasn’t listed as one of the Community Plug-ins

OK, so probably contacting Stefan would be my best bet. But I just wanted to get this done ASAP and I doubted Stefan would remember what he did 7 years ago.

Basically I had to do a quick fix to support APEX 24.2 to use the CLOB output instead of the HTP buffer - as required for APEX 24.2.

The steps I followed were basically Philipp’s steps in how to contribute to an open-source Oracle APEX Plug-in.

Fork the original plug-in - proof
Do the changes to the forked plug-in - proof
Submit a Pull Request - proof
Wait until Stefan accepts the Pull Request or otherwise comments on my work

To be honest, I don’t really have to wait as I can just use the forked version I changed.

In a boost for Open Source, Stefan did comment on my changes. I got those done and added some bonus features of my own too.

After I completed the changes, here is how I set it up

select json_object(
    'APPLICATION_NAME' value application_name 
    )
  from apex_applications
 where application_id = :APP_ID

Notes:

The plug-in only works as an After Footer Process for several reasons that will take too long to explain
The Source is set to SQL Query Returning a JSON Object. I could have selected the standard SQL Query option, however all my rows (all one of them) would be as a JSON array starting at [0] - which I don’t want. In the help, Stefan suggests this json_objectagg method ;which works very nicely.
Version 1.0 of the plug-in didn’t actually support APEX bind variables - it was an easy change; so now it does support APEX binds
The results load into apex.env I made a change to the version 1.0 approach so that instead of destroying the the env object, it just merges the changes in.

Did it work? yes of course

Q. Matt, why is your Application Name come through as a Chili Pepper emoji?

A. Because the actual name of my Application is the 🌶️ emoji! 😜. I haven’t found a single place in Oracle APEX where emojis are not supported.

Q. Anything else?

Of course you can can use this Plug-in to stash any type of JSON on the page to be picked up by a later process. Is it pretty handy if you want to grab some values at render type and make them available in JS without burning a Page Item or re-querying the DB. For example, Im stashing some EXTRA information in the apex.env.EXTRA name