Wednesday, 4 January 2017

APEX attributes for Escaping Special Characters

A relatively common on the forums is regarding the escaping of special characters in reports, but it seems the developer isn't always sure what is actually happening and how to how to search for it.

It seems I've had this on my "to blog" list since April 2015, but now that 5.1 has been released, it seems more people are coming out to leave 4.x can't work out where the Standard Report Column option is.

APEX 4.x Display As attribute

This was required when HTML was present in the query, either to add tabular items manually using apex_item, or to style data (though you should use HTML Expression instead)

Example of special characters being escaped

For instance, if you've written a query like so

SELECT APEX_ITEM.CHECKBOX2(1, empno, 'CHECKED') chk, ename
FROM   emp

And are only seeing the HTML code in your column output

<input type="checkbox" name="f01" value="7369" CHECKED />

Then you need to Escape Special Characters, now found in the Security section of the column properties as a Yes/No option.

APEX 5.0 Escape Special Characters attribute

This is defaulted to Yes to help protect from cross-site scripting (XSS), a common security concern in the web world where data entered by users is stored in the database, then when rendered it can be interpreted as HTML code.

Set to No to allow your data to be rendered as you may expect. 
Note that in the 5.0 component view this is still referenced as Display As - Standard Report Column.

The change in terminology is documented in the 5.0 release notes

Report column property naming differences
Please note that if setting this attribute to no, you should still make efforts to protect your applications by escaping data where possible. For example, if I wanted to replace all carriage returns with the HTML line break, you can still escape your data then add HTML content.

replace(apex_escape.html(card_title), chr(10), '<br>')

You could probably do a variation of this using apex_escape.html_whitelist

If you're combining two fields, separated by the line break:


then you might as well use HTML Expression and keep your data/UI layers separate.

HTML Expression attribute

Check out the open source project APEX-SERT to help find potential security concerns with your Oracle APEX applications.

Wednesday, 21 December 2016

Declarative Favicon in 5.1

Some features you just tend to stumble upon in the builder somewhere.

Sure it's in the documentation, but not listed under new features.

Where to find declarative favicon setting in APEX 5.1
Patrick described how to do it in APEX 5.0;
Amanda for APEX 4.2;
Christian if you're still using APEX Listener;

Now we have an dedicated attribute. Neat.

Phased APEX migrations

While looking through the comments regarding 5.1 features it occured to me how many different ways the Oracle APEX team enable us to phase our applications into any new APEX version, thereby minimising risk and introducing new IDE features to developers earlier.

I've always thought APEX does a really good job of leaving our applications alone during upgrades. It normally depends on how much the boundaries have been pushed, and how much the theme has been modified, or templates added. In fact, most of the work is in upgrading the theme, which is why the Universal Theme came about.

So that that masterpiece may start the list:
  1. Universal Theme
    Designed to remove need to migrate themes in future. Instead, theme templates re-subscribed on demand as long as you kept your theme locked to the repository.
  2. Compatibility mode
    A relict from Oracle Forms, this feature allows you to prepare your app for behavioural changes between APEX versions. This great write up from Carsten describes how asynchronous developments in 5.1 may apply to your applications.
    Check this setting on any applications borne out of earlier APEX versions.
  3. jQuery MigrateTo keep libraries lean, older functions are removed over time. Older references can sustained until they're refactored, as described by Marko.
  4. Utilities -> Upgrade Application
    APEX will leave deprecated features alone, until you're ready to upgrade each instance. The classic example is the old datepicker from 3.1. All your items called the horrible window until you replaced them all. This feature makes this process quicker and this older reference from Dimitri still applies. I may translate my mentioned of it in this presentation (as it applies to charts) to a blog post.
  5. Multiple concurrent versionsIt's not here year, but 5.2 may see the introduction of the ability to run two different APEX versions on the same database instance.
    This will always be a sandbox for the latest version or patch, so why not try your applications out in there? You can script up your tables, generate some data and import your app. Start preparing now and see how much your application won't break.
    Let's not forget, when the latest major release is in early adopter mode.
If you can think of any others, let me know and I'll add them to the list.

Or do you have any stories of nasty surprises? I haven't heard of too many serious issues with APEX upgrades.



Monday, 19 December 2016

Upgrading the Universal Theme

Back when APEX 5.0 was released, these forward thinkers at the development team installed a feature to verify your Universal Theme against the one defined in the repository.

There hasn't really been anything to verify against, until now.

There are a number of changes to the Universal Theme, and one way to explore these changes is to check out what's reported under the Verify button in the Theme definition.

Though unfortunately on I'm still getting the same error
ORA-20001: Unable to subscribe to report template.
ORA-00040: active time limit exceeded - call aborted

Errors aside, this facility all planned, of course, to minimise the impact theme upgrades have caused in the history of APEX. And no doubt used internally while they were developing UT in the first place.

As Patrick Wolf points out in this forum post, some features aren't borne out of the IDE upgrade alone, but are found with the theme upgrade. This includes RTL functionality.

So some thoughts that come out of reviewing the comparison:

  • A couple of new default styles, to go along with Slate. Nice.
  • Two new region templates to explore - Content Block and Blank with Attributes (No Grid)
  • Every other template has been touched in some way. It would be nice to see a diff of the updates.

If your Universal Theme is still locked, you just refresh your theme definition and your application should continue to work wonderfully.

Take care if you have any customisations or extensions, regression test as necessary. We've tried to minimise ours so it will be interesting to explore when 5.1 becomes generally available.

If you want to learn more about the features of the Universal Theme, check out the sample application, which also got a wicked upgrade, always available at
There are some awesome tools in the reference section alone.

Wednesday, 14 December 2016

APEX 5.2 early outlook

It seems at a recent conference a slide was put up that a few tweeters managed to snap a photo of:

It describes some goals for APEX 5.2 and beyond, of course coming with the usual safe harbor statement - we're still waiting for 5.1!

I do enjoy these snippets into the future, having talked about various APEX SOD in the past. Though I realise I'm doing this not having heard the talk that came with the slide, so I could be interpreting things wrong.

This was the best image I saw, so for those who struggle to read it (emphasis theirs):
  1. Tighter integration / native integration with RESTful web services and ORDS
  2. Improved integration with Developer Cloud Service (hudson, git)
  3. Enhanced self service cloud with documented RESTful APIs
  4. Proper APEX app diff, full app change history capture
  5. Friendly URLs
  6. Automated APEX app testing
  7. Greater internal use of JSON expanding on interactive grids and page designer
  8. Allow page designer to set IR and IG report settings at design time
  9. Multiple concurrent versions, for example 5.1 and 5.2 - goal is to ease upgrade
  10. Documented APIs to dynamically generate APEX application components
#1 is no surprise, I'd be shocked if this didn't continue to happen, as with the cloud stuff.
#4 will excite the bean counters, though I'd be happy with just a more accurate change history.
#5 A zillion ways to do this, but something within the builder would be cool. Particularly since I've attempted none of them.
#6 well, this would be impressive.
#8 excites me, particular in a world where half the time I don't include the search bar, so modifying report settings becomes a pain.
#9 surprises me. I'm not sure how they'll solve this, but quite an engineering effort when upgrades are fairly seemless, the UT has the Verify option that we'll finally be able to play with, and we also have jQuery legacy options in the UI details. But I guess it does reduce the need for another server during regression testing...
#10 still not sure I get why people want to do this, but why not I guess.

Noticely absent: any mention of OracleJET. I thought they may take fuller advantage of such a rich product.

To me this list is another sign of APEX's maturity. 5.0 came with a lot of stuff to excite developers, and 5.1 ties a tidy knot in the bow (and introduces OracleJET...)
This list for 5.2 is mostly infrastructure stuff, right?

Time will tell.

Tuesday, 13 December 2016

In Memory Session State - Use Case

Many moons ago Anton Nielson posted about the different kinds of session state in Oracle APEX.

Different kinds?!

I remember session state was hard enough when learning, particularly coming from Oracle Forms. But how is there more than one, you may ask?

98% of the time we just need to worry about the persisted session state that we all know and love. The one we see when clicking "Session" in the developer toolbar. Essentially, this is just a normal table with a session ID, item name and value.

In Memory Session State (IMMS) is the 2% that comes and bites us occasionally. Recently I had a problem the scenario described by Anton as "2A Item Rendering". His description is a little tricky, but I think this visualisation of a simpler example will help.

IMSS Demonstration

Some page facts:

  • 'before region' has item P45_BEFORE, defaulted to A
  • Region 1 has condition P45_BEFORE = A 
  • Region 2 (after Region 1, not visible) has condition P45_AFTER = B 
  • 'after region' has item P45_AFTER, defaulted to B
  • The items have no source, and are just defaulted to those values shown.
  • Session state (persisted) remains empty before/after. During is another story. 

In this case, while it renders Region 1, IMSS will actually report a value of A for P45_BEFORE while rendering Region 1, hence it's displayed. Since P45_AFTER is rendered after Region 2, the value is null during the evaluation of the condition.

Therefore, the results differ by moving the location of the items relative to the component with the condition.

Unfortunately I couldn't find any support/explanation for this behaviour in the debug log, even at LEVEL9.

In my actual case, I had an item that stopped a report from being rendered until the user selected criteria and clicked submit. I did this by defining a hidden P1_SUBMITTED, defaulted to Y, and used that as the condition in the report.

This usually worked fine since the item is usually with other criteria in the Right Side region, but a new page had this item at the top of the page. So P1_SUBMITTED was treated as having a value Y while rendering my report, just like in the example above with P45_BEFORE.

Happy APEXing!

Wednesday, 7 December 2016

APEX Component Export

A common question when it comes to migrating APEX applications is "can I just export a page".

There are more technical posts on the topic of exporting APEX applications than this one out there, eg:
HÃ¥vard Kristiansen
Christoph Ruepprich
Alan Warren
John Scott
I just wanted to clarify the concept, perhaps for those learning the tool.

So the answer? Sure you can, but what about other components related to that page? A breadcrumb; menu list entry; LOV? What about application items? Are you sure you've got everything?

So with this in mind, when you start the process export an APEX application you have a side-option to do a Component Export, which might look like this:

From here we can not just nominate the page, but identify other components to add to the export. The exported SQL is just a subset of the entire application. (I wonder when this will become JSON?)

However, the only time I consider doing this is for a reporting application where I know the only components in a new report is the page itself and a breadcrumb. The menu is dynamic so the existence of the page is enough.

Otherwise, perhaps for an operational style application, I think the risk of missing changes it too high not to do a full export.

Build options can also help you find your bundle, but even then diligence needs to be high.