A quick browse of the Beta APEX 18.1 documentation

Noticed a few additions, one one omission.

    • APEX_EXPORT – new package with supported methods for exporting APEX applications and other scripts into a CLOB: get_application, get_workspace_files, get_feedback, get_workspace
    • APEX_JWT – new package for JSON Web Tokens – encode, decode, validate
    • APEX_SESSION – new procedures: create_session, delete_session, attach, detach
    • Missing documentation: Legacy JavaScript APIs – not sure if anyone will miss this, they could just refer to the 5.1 documentation for this
    • I should mention also the new format for the APEX Javascript API reference. Lots of TODO’s 🙂

What I’m particularly looking for is a good reference for the interactiveGrid API. Hopefully they’ll add this soon. There is a reference for a “grid” widget but that is not the API for interactive grids. I notice there are APIs for actions and model which are relevant to interactive grids.

Any other changes I’ve missed?

Interactive Grid: Custom Select List on each row

I had a column in an editable interactive grid based on a Select List, which takes valid values from a table that supports “soft delete” – i.e. records could be marked as “deleted” without affecting existing references to those deleted records.

The SQL Query for the LOV was like this (my example is a view on a reference table of school “year levels”):

select name, id from year_levels_vw
where deleted_ind is null
order by sort_order

The problem is that if a year level is marked as deleted, the select list will not include it due to the where clause; since Display Extra Values is set to “Yes”, the item on the page will instead show the internal ID which is not very useful to the user. Instead, I want to show the name but appended with a string to show it has been deleted:

select name
       || case when deleted_ind = 'Y' then ' (DELETED)' end
       as disp_name, id
from year_levels_vw
order by deleted_ind nulls first, sort_order

So now the select list shows the name, even if it has been deleted. However, once users start using this system and they delete some year levels, each select list will include all the deleted values, even if they will never be used again. We’d prefer to only include a deleted value IF it is currently used by the record being viewed; otherwise, we want to omit it from the list.

If this was an APEX item in a single-record edit form, I’d simply change the SQL Query for the LOV to:

select name
       || case when deleted_ind = 'Y' then ' (DELETED)' end
       as disp_name, id
from year_levels_vw
where deleted_ind is null or id = :P1_FROM_YEAR_LEVEL_ID
order by deleted_ind nulls first, sort_order

This way, the select list will only include the deleted year level if the underlying item was already set to that deleted ID. But we are now using an Interactive Grid – there is no page item to refer to.

The method I’ve used to solve this is to take advantage of the Cascading LOV feature in order to allow the query to refer to the value of the column. The SQL Query for the LOV on my Interactive Grid is:

select name
       || case when deleted_ind = 'Y' then ' (DELETED)' end
       as disp_name, id
from year_levels_vw
where deleted_ind is null or id = :FROM_YEAR_LEVEL_ID
order by deleted_ind nulls first, sort_order

Now, we need to make sure that “FROM_YEAR_LEVEL_ID” is available to the query, so we need to put it in the Items to Submit attribute. To make this attribute available, however, we must set Cascading LOV Parent Column(s) to something; I set it to the PK ID of the table, or some other column which doesn’t get changed by the user and isn’t actually referred to in the LOV Query.

Now, records not referring to a deleted value show only valid values:

And records that refer to a deleted value include the deleted value in the list, as desired:

It should be noted that the design of the select list means that the user is allowed to save changes to the record while preserving the reference to the deleted year level. This is desired, in this case; if it wasn’t, however, I’d consider putting a validation on the page to stop the record being saved unless the user changes it to a valid value.

P.S. Concerned parents should note that this example was just testing the ability to delete a record from a database, and I’d just like to make it totally clear that there are no plans by the department to eliminate year 7 from schools. Honest!

ADDENDUM (19/3/2018):

There are two known issues:

  1. If the item is the child of a Cascading LOV, when the parent item is changed, APEX automatically clears out any value in the child before rendering the list of values – which means the column value submitted will be NULL – which means the “deleted” items disappear from the list immediately. This means the user will not be allowed to save the record with a reference to a deleted value from the list.
  2. The column filter list of values is empty – this is due to a known bug in APEX [Doc ID 2289512.1 FILTER NOT WORKING IN INTERACTIVE GRID WITH CASCADING LOV][thanks to Dejan for alerting me to this]

Convert an APEX Application to Multi-Tenant

So you’ve built an APEX application to solve a problem for one client, or one department, or just yourself – and you think it might be useful for others as well. How do you make that application available for other users, departments, or companies to reuse, while ensuring each sees only their own data and cannot mess around with others’ data?

Architecting a Multi-Tenant Application

To make your application multi-tenant you have a few options.

Option #1. Copy the application to another workspace/schema, another Pluggable Database (in Oracle 12c+) or another database server entirely.

Option #2. Modify your data model to allow completely independant sets of data to co-exist in the same physical tables (e.g. a security_group_id column that allows the database to discriminate data for each tenant).

The desirable properties of a multi-tenant system are as follows:

a. Tenant isolation – no tenant sees data for another tenant; no tenant can affect the app’s behaviour for another tenant; protect against “noisy neighbours” (i.e. those which impact system capacity and performance).

Hadlow’s first law of multi-tenancy: A multi-tenanted application should not look like a multi-tenanted application.”

b. Administration – ability to backup/recover all data for a single tenant; ability to give a degree of control to each tenant (self service).

c. Maintainability – simplicity of deploying enhancements and bug fixes for all tenants, or for one tenant at a time (e.g. rolling upgrades).

d. Scalability – ability to easily add more tenants, ability to add more capacity for more tenants.

Some of these properties are more easily and effectively achieved with option #1 (separate servers or schemas for each tenant), such as Isolation and Administration. Other properties are more easily and effectively achieved with option #2 (discriminator column) such as Maintainability and Scalability. This is a gross generalisation of course; there are many solutions to this design problem each with many pros and cons.

Some inspiration may be gained from examining how Oracle Application Express achieves this goal: multi-tenant has been baked into the product, via its concept of Workspaces. Each tenant can be given their own workspace in APEX and are able to build and deploy applications in isolation from other workspaces. Internally, APEX maintains a unique security_group_id for each workspace. This works very well – a single Oracle database instance can serve thousands or tens of thousands of workspaces.

It should be noted that a benefit of pursuing Option #2 is that it does not necessarily preclude using Option #1 as well, should the need arise later on – for example, to provide more capacity or better performance in the presence of more demanding tenants. For this reason, plus the fact that it’s much easier to maintain and enhance an application for all users at once if they’re colocated, I prefer Option #2.
Continue Reading

APEX Plugin: Password Strength Estimator

I needed a simple password strength prompt for users when they need to create or change their password on my website. After a bit of Googling I found the “Low-Budget Password Strength Estimator” which is supposedly used by Dropbox, so you know it’s good 🙂

This simple javascript library runs entirely within the client’s browser, and when presented with a candidate password, gives a score from 0 (very poor) to 4 (very good). It can also return extra feedback, including a warning message for poor passwords, as well as suggestions for making a password more secure.


So I’ve created a very simple Dynamic Action plugin (try the demo here) that allows you to add this functionality to any item on your page. You can specify a minimum length for the password, and can override the default messages for each score. You can also select whether or not the feedback warnings or suggestions are shown.


It seems to catch a lot of poor passwords, including ones comprising common words and names, and ones involving a simple sequence or repetition.



Obviously it’s only really useful for password entry fields; but don’t use it on your Login page!

Download from: http://github.com/jeffreykemp/jk64-plugin-passwordstrength

APEX Reports: One Link, Multiple Destinations

Every Interactive Report has an optional set of “Link” attributes that allow you to specify the location where the user should be redirected if they click a link next to a record in the report. You can choose “Link to Custom Target” and use the Link Builder to easily specify the target application, page, item values to pass, and other attributes.


What if the report combines different entities, and you need to direct the user to a different page depending on the type of entity? Or, if you need to direct the user to a different page with different attributes depending on the status of the record?

One method is to generate the URL in the report query using apex_page.get_url (APEX 5+) or apex_util.prepare_url (APEX 4 or earlier), or (God forbid) you could generate the url yourself using string concatenation.

A more declarative solution is to instead use APEX page redirects. This solution involves the following:

  1. Add some hidden items to the page to store the parameters for each target page;
  2. Add a Branch to the page for each target page; and
  3. Add a Request to the link to signal the page that a redirect has been requested.

Here’s an example. My page 550 has an interactive report which combines TI records with TRQ records (both of which have a very similar structure). If the user clicks on a TI record they should be redirected to p510 with the corresponding TI_ID, and if they click on a TRQ record they should be redirected to p305 with the corresponding TRQ_ID.

Here’s the link attributes for this report:


Notice that the page now redirects back to itself with the request set to “GOTO_TARGET”, along with the IDs required. My report query has been constructed so that every record will only have a TI_ID or a TRQ_ID, never both at the same time; so the link will ensure that only one of the P550_GOTO_xxx_ID values will be set.

The page then just needs two Branches: one for each target. Conditions on each branch ensures they only get activated if the request has been set, and the branch is selected based on which “GOTO ID” item has been set:



For a normal navigation to this report (e.g. from another page), the request should be blank (or some other value) so none of the Branches should be triggered.

For a relatively simple scenario like this, I like the declarative approach. Each branch can take advantage of the full range of APEX features such as the link builder, security conditions, build options, etc.

Note: this method works just as well for Classic report links as well.

The thing to be mindful of is that the order of the branches, and the condition on each branch, must be carefully selected so that the correct branch is activated in each situation. I’ve shown a simple example which works because I have ensured that only one of the ID parameters is set at the same time. If a record has both IDs, the condition on the first branch “GOTO_TARGET (TI_ID)” will evaluate to True and it will be used, regardless of what GOTO_TRQ_ID was set to.

If there were numerous potential destination pages, with a large number of parameters to pass, I might choose the apex_page.get_url method instead.


Show greyscale icon as red

I have an editable tabular form using APEX’s old greyscale edit link icons:


The users complained that they currently have to click each link to drill down to the detail records to find and fix any errors; they wanted the screen to indicate which detail records were already fine and which ones needed attention.

Since screen real-estate is limited here, I wanted to indicate the problems by showing a red edit link instead of the default greyscale one; since this application is using an old theme I didn’t feel like converting it to use Font Awesome (not yet, at least) and neither did I want to create a whole new image and upload it. Instead, I tried a CSS trick to convert the greyscale image to a red shade.

I used this informative post to work out what I needed: http://css-tricks.com/color-filters-can-turn-your-gray-skies-blue/

WARNING: Unfortunately this trick does NOT work in IE (tested in IE11). Blast.

Firstly, I added a column to the underlying query that determines if the error needs to be indicated or not:

select ...,
       case when {error condition}
       then 'btnerr' end as year1_err
from mytable...

I set the new column type to Hidden Column.

The link column is rendered using a Link-type column, with Link Text set to:

<img src="#IMAGE_PREFIX#e2.gif" alt="">

I changed this to:

<img src="#IMAGE_PREFIX#e2.gif" alt="" class="#YEAR1_ERR#">

What this does is if there is an error for a particular record, the class "btnerr" is added to the img tag. Rows with no error will simply have class="" which does nothing.

Now, to make the greyscale image show up as red, I need to add an SVG filter to the HTML Header in the page:

<svg style="display:none"><defs>
  <filter id="redshader">
    <feColorMatrix type="matrix"
      values="0.7 0.7 0.7 0 0
              0.2 0.2 0.2 0 0
              0.2 0.2 0.2 0 0
              0   0   0   1 0"/>

I made up the values for the R G B lines with some trial and error. The filter is applied to the buttons with the btnerr class with this CSS in the Inline CSS property of the page:

img.btnerr {filter:url(#redshader);}

The result is quite effective:


But, as I noted earlier, this solution does not work in IE, so that’s a big fail.

NOTE: if this application was using the Universal Theme I would simply apply a simple font color style to the icon since it would be using a font instead of an image icon.

IRs with Subscriptions that might not work

If you have an Interactive Report with the Subscription feature enabled, users can “subscribe” to the report, getting a daily email with the results of the report. Unfortunately, however, this feature doesn’t work as expected if it relies on session state – e.g. if the query uses bind variables based on page items to filter the records. In this case, the subscription will run the query with a default session state – Apex doesn’t remember what the page item values were when the user subscribed to the report.

This is a query I used to quickly pick out all the Interactive Reports that have the Subscription feature enabled but which might rely on session state to work – i.e. it relies on items submitted from the page, refers to a bind variable or to a system context:

select workspace, application_id, application_name,
page_id, region_name, page_items_to_submit
from apex_application_page_ir
where show_notify = 'Yes'
and (page_items_to_submit is not null
or regexp_like(sql_query,':[A-Z]','i')
or regexp_like(sql_query,'SYS_CONTEXT','i')

For these reports, I reviewed them and where appropriate, turned off the Subscription feature. Note that this query is not perfect and might give some false positives and negatives.

Quick Pick in APEX Report

I have an Interactive Report that includes some editable columns, and the users wanted to include some “quick picks” on these columns to make it easy to copy data from a previous period. The user can choose to type in a new value, or click the “quick pick” to quickly data-enter the suggested value.


Normally, a simple page item can have a quick pick by setting the Show Quick Picks attribute on the item. This is not, however, available as an option when generating APEX items in a report.

To do this, I added code like the following to my report query: NOTE: don’t copy this, refer to ADDENDUM below

         ,p_rows => 1
         ,p_cols => 30
         ,p_item_id => 'f05_'||to_char(rownum,'fm00000'))
       || case when x.prev_ytd_comments is not null
          then '<a href="javascript:$(''#'
            || 'f05_' || to_char(rownum,'fm00000')
            || ''').val('
            || apex_escape.js_literal(x.prev_ytd_comments)
            || ').trigger(''change'')">'
            || apex_escape.html(x.prev_ytd_comments)
            || '</a>'
       as edit_ytd_comments
FROM my_report_view x;

This results in the following HTML code being generated:


In the report definition, the EDIT_YTD_COMMENTS column has Escape Special Characters set to No. This runs a real risk of adding a XSS attack vector to your application, so be very careful to escape any user-entered data (such as prev_ytd_comments in the example above) before allowing it to be included. In this case, the user-entered data is rendered as the link text (so is escaped using APEX_ESCAPE.html) and also within some javascript (so is escaped using APEX_ESCAPE.js_literal).

So, if the data includes any characters that conflict with html or javascript, it is neatly escaped:


And it is shown on screen as expected, and clicking the link copies the data correctly into the item:


This technique should, of course, work with most of the different item types you can generate with APEX_ITEM.

Recommended further reading:

ADDENDUM 6/2/2017

A problem with the above code causes this to fail in Internet Explorer (IE11, at least) – when clicking on the quickpick, the user is presented with a page blank except for “[object Object]”. After googling I found this question on StackOverflow and fixed the problem by moving the jQuery code to a function defined at the page level.

I added this to the page Function and Global Variable Declaration:

function qp (id,v) {

And modified the report query as follows:

         ,p_rows => 1
         ,p_cols => 30
         ,p_item_id => 'f05_'||to_char(rownum,'fm00000'))
       || case when x.prev_ytd_comments is not null
          then '<a href="javascript:qp(''#'
            || 'f05_' || to_char(rownum,'fm00000')
            || ''','
            || apex_escape.js_literal(x.prev_ytd_comments)
            || ')">'
            || apex_escape.html(x.prev_ytd_comments)
            || '</a>'
       as edit_ytd_comments
FROM my_report_view x;

Powerless Javascript

I was writing a small javascript function, part of which needed to evaluate 10 to the power of a parameter – I couldn’t remember what the exponentiation operator is in javascript so as usually I hit F12 and typed the following into the console:



Wrote and tested the code, checked in to source control. Job done.

A few days later we deployed a new release that included dozens of bug fixes into UAT for testing. Soon after a tester showed me a screen where a lot of stuff wasn’t looking right, and things that had been working for a long time was not working at all.

Developer: “It works fine on my machine.”

After some playing around on their browser I noted that it seemed half of the javascript code I’d written was not running at all. A look at their browser console revealed two things:

  1. they are using Internet Explorer 11
  2. a compilation error was accusing the line with the ** operator

The error meant that all javascript following that point in the file was never executed, causing the strange behaviour experienced by the testers.

A bit of googling revealed that the ** operator was only added to javascript relatively recently and was supported by Chrome 52 and Edge browser but not IE. So I quickly rewrote it to use Math.pow(n,m) and applied a quick patch to UAT to get things back on track.

I think there’s a lesson there somewhere. Probably, the lesson is something like “if you try drive-by javascript coding, you’re gonna have a bad time.”

Target=_Blank for Cards report template

cardsreport.PNGI wanted to use the “Cards” report template for a small report which lists file attachments. When the user clicks on one of the cards, the file should download and open in a new tab/window. Unfortunately, the Cards report template does not include a placeholder for extra attributes for the anchor tag, so it won’t let me add “target=_blank” like I would normally.

One solution is to edit the Cards template to add the extra placeholder; however, this means breaking the subscription from the universal theme.

As a workaround for this I’ve added a small bit of javascript to add the attribute after page load, and whenever the region is refreshed.

  • Set report static ID, e.g. “mycardsreport”
  • Add Dynamic Action:
    • Event = After Refresh
    • Selection Type = Region
    • Region = (the region)
  • Add True Action: Execute JavaScript Code
    • Code = $("#mycardsreport a.t-Card-wrap").attr("target","_blank"); (replace the report static ID in the selector)
    • Fire On Page Load = Yes

Note: this code affects all cards in the chosen report.