Show greyscale icon as red

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

greyscale-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"/>
  </filter>
</defs></svg>

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:

greyscale-colorize

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.

example-report-quickpick

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

SELECT ...
      ,APEX_ITEM.textarea(5,x.ytd_comments
         ,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>'
          end
       as edit_ytd_comments
FROM my_report_view x;

This results in the following HTML code being generated:

html-report-quickpick

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:

html-quickpick-bad

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

example-quickpick-bad

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) {
  $(id).val(v).trigger('change');
}

And modified the report query as follows:

SELECT ...
      ,APEX_ITEM.textarea(5,x.ytd_comments
         ,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>'
          end
       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:

10**3

jspower

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.


Report Link: Save before Navigation

“We always click ‘Apply Changes’, then we click the button we actually wanted” – a user

Typically an Apex report with an “Open” or “Edit” icon will simply do an immediate navigation to the target page, passing the ID of the record to edit. When the user clicks this link, the page is not submitted and the user is instead redirected.

It’s easy to quickly build large and complex applications in Apex but it’s also very easy to build something that confuses and disorients your users. They only need one instance when something doesn’t work how they expected it to, and they will lose trust in your application.

For example: if most buttons save their changes, but one button doesn’t, they might not notice straight away, and then wonder what happened to their work. If you’re lucky, they will raise a defect notice and you can fix it. More likely (and worse), they’ll decide it’s their fault, and begrudgingly accept slow and unnecessary extra steps as part of the process.

You can improve this situation by taking care to ensure that everything works the way your users expect. For most buttons in an Apex page, this is easy and straightforward: just make sure the buttons submit the page. The only buttons that should do a redirect are those that a user should expect will NOT save the changes – e.g. a “Cancel” button.

For the example of an icon in a report region, it’s not so straightforward. The page might include some editable items (e.g. a page for editing a “header” record) – and if the user doesn’t Save their changes before clicking the report link their changes will be lost on navigation.

To solve this problem you can make the edit links first submit the page before navigating. The way I do this is as follows (in this example, the report query is on the “emp” table:

    1. Add a hidden item P1_EDIT_ID to the page
      • Set Value Protected to No
    2. Add something like this to the report query (without the newlines):
      'javascript:apex.submit(
        {request:''SAVE_EDIT_ROW'',
         set:{''P1_EDIT_ID'':''' || emp.rec_id || '''}
        })' AS edit_link
      • Set this new column to Hidden Column
    3. Modify the edit link Target:
      • Type = URL
      • URL = #EDIT_LINK#
    4. Add a Branch at point “After Processing”
      • Set the Target to the page to navigate to
      • Set the item for the record ID to &P1_EDIT_ID.
      • Set the Condition to the following PL/SQL Expression:
        :REQUEST='SAVE_EDIT_ROW' AND :P1_EDIT_ID IS NOT NULL
      • Make sure the branch is evaluated before any other branches (at least, any others that might respond to this request)
    5. Modify any existing Processing so that the request SAVE_EDIT_ROW will cause any changes on the page to be saved.

You can, of course, choose different item names and request names if needed (just update it in the code you entered earlier). For example, to make it work with the default Apex DML process you might need to use a request like “APPLY_CHANGES_EDIT_ROW”.

Now, when the user makes some changes to the form, then clicks one of the record Edit links, the page will first be submitted before navigating to the child row.

Adding buttons to Apex pages is easy. Making sure every last one of them does exactly what the user expects, nothing more, and nothing less, is the tricky part!


File Upload Improvements in APEX 5.1

Updated 10/10/2017 now that APEX 5.1 has been out for a while.

file_upload_5_1_ea

The standard File Upload item type is getting a nice little upgrade in Apex 5.1. By simply changing attributes on the item, you can allow users to select multiple files (from a single directory) at the same time.

In addition, you can now restrict the type of file they may choose, according to the MIME type of the file, e.g. image/jpg. This file type restriction can use a wildcard, e.g. image/*, and can have multiple patterns separated by commas, e.g. image/png,application/pdf.

file_upload_5_1_ea_demo

Normally, to access the file that was uploaded you would query APEX_APPLICATION_TEMP_FILES with a predicate like name = :P1_FILE_ITEM. If multiple files are allowed, however, the item will be set to a colon-delimited list of names, so the suggested code to get the files is:

declare
  arr apex_global.vc_arr2;
begin
  arr := apex_util.string_to_table(:P1_MULTIPLE_FILES);
  for i in 1..arr.count loop
    select t.whatever
    into   your_variable
    from   apex_application_temp_files t
    where  t.name = arr(i);
  end loop;
end;

You can play with a simple demo here: https://apex.oracle.com/pls/apex/f?p=UPLOAD_DEMO&c=JK64 . (UPDATE 10/10/2017: recreated demo on apex.oracle.com) If you want to install the demo app yourself, you may copy it from here.

If you want to support drag-and-drop, image copy&paste, load large files asynchronously, or restrict the maximum file size that may be uploaded, you will probably want to consider a plugin instead, like Daniel Hochleitner’s DropZone.


Interactive Grids (APEX 5.1 EA) and TAPIs

DISCLAIMER: this article is based on Early Adopter 1.

event_types_ig

I’ve finally got back to looking at my reference TAPI APEX application. I’ve greatly simplified it (e.g. removed the dependency on Logger, much as I wanted to keep it) and included one dependency (CSV_UTIL_PKG) to make it much simpler to install and try. The notice about compilation errors still applies: it is provided for information/entertainment purposes only and is not intended to be a fully working system. The online demo for APEX 5.0 has been updated accordingly.

I next turned my attention to APEX 5.1 Early Adopter, in which the most exciting feature is the all-new Interactive Grid which may replace IRs and tabular forms. I have installed my reference TAPI APEX application, everything still works fine without changes.

I wanted my sample application to include both the old Tabular Forms as well as the new Interactive Grid, so I started by making copies of some of my old “Grid Edit” (tabular form) pages. You will find these under the “Venues” and “Event Types” menus in the sample application. I then converted the tabular form regions to Interactive Grids, and after some fiddling have found that I need to make a small change to my APEX API to suit them. The code I wrote for the tabular forms doesn’t work for IGs; in fact, the new code is simpler, e.g.:

PROCEDURE apply_ig (rv IN VENUES$TAPI.rvtype) IS
  r VENUES$TAPI.rowtype;
BEGIN
  CASE v('APEX$ROW_STATUS')
  WHEN 'I' THEN
    r := VENUES$TAPI.ins (rv => rv);
    sv('VENUE_ID', r.venue_id);
  WHEN 'U' THEN
    r := VENUES$TAPI.upd (rv => rv);
  WHEN 'D' THEN
    VENUES$TAPI.del (rv => rv);
  END CASE;
END apply_ig;

You may notice a few things here:

(1) APEX$ROW_STATUS for inserted rows is ‘I’ instead of ‘C’; also, it is set to ‘D’ (unlike under tabular forms, where it isn’t set for deleted rows).

(2) After inserting a new record, the session state for the Primary Key column(s) must be set if the insert might have set them – including if the “Primary Key” in the region is ROWID. Otherwise, Apex 5.1 raises No Data Found when it tries to retrieve the new row.

(3) I did not have to make any changes to my TAPI at all 🙂

Here’s the example from my Event Types table, which doesn’t have a surrogate key, so we use ROWID instead:

PROCEDURE apply_ig (rv IN EVENT_TYPES$TAPI.rvtype) IS
  r EVENT_TYPES$TAPI.rowtype;
BEGIN
  CASE v('APEX$ROW_STATUS')
  WHEN 'I' THEN
    r := EVENT_TYPES$TAPI.ins (rv => rv);
    sv('ROWID', r.p_rowid);
  WHEN 'U' THEN
    r := EVENT_TYPES$TAPI.upd (rv => rv);
  WHEN 'D' THEN
    EVENT_TYPES$TAPI.del (rv => rv);
  END CASE;
END apply_ig;

Converting Tabular Form to Interactive Grid

The steps needed to convert a Tabular Form based on my APEX API / TAPI system are relatively straightforward, and only needed a small change to my APEX API.

  1. Select the Tabular Form region
  2. Change Type from “Tabular Form [Legacy]” to “Interactive Grid”
  3. Delete any Region Buttons that were associated with the Tabular form, such as CANCEL, MULTI_ROW_DELETE, SUBMIT, ADD
  4. Set the Page attribute Advanced > Reload on Submit = “Only for Success”
  5. Under region Attributes, set Edit > Enabled to “Yes”
  6. Set Edit > Lost Update Type = “Row Version Column”
  7. Set Edit > Row Version Column = “VERSION_ID”
  8. Set Edit > Add Row If Empty = “No”
  9. If your query already included ROWID, you will need to remove this (as the IG includes the ROWID automatically).
  10. If the table has a Surrogate Key, set the following attributes on the surrogate key column:
    Identification > Type = “Hidden”
    Source > Primary Key = “Yes”
  11. Also, if the table has a Surrogate Key, delete the generated ROWID column. Otherwise, leave it (it will be treated as the Primary Key by both the Interactive Grid as well as the TAPI).
  12. Set any columns Type = “Hidden” where appropriate (e.g. for Surrogate Key columns and VERSION_ID).
  13. Under Validating, create a Validation:
    Editable Region = (your interactive grid region)
    Type = “PL/SQL Function (returning Error Text)”
    PL/SQL = (copy the suggested code from the generated Apex API package) e.g.

        RETURN VENUES$TAPI.val (rv =>
          VENUES$TAPI.rv
            (venue_id     => :VENUE_ID
            ,name         => :NAME
            ,map_position => :MAP_POSITION
            ,version_id   => :VERSION_ID
            ));
        
  14. Under Processing, edit the automatically generated “Save Interactive Grid Data” process:
    Target Type = PL/SQL Code
    PL/SQL = (copy the suggested code from the generated Apex API package) e.g.

        VENUES$APEX.apply_ig (rv =>
          VENUES$TAPI.rv
            (venue_id     => :VENUE_ID
            ,name         => :NAME
            ,map_position => :MAP_POSITION
            ,version_id   => :VERSION_ID
            ));
        

I like how the new Interactive Grid provides all the extra knobs and dials needed to interface cleanly with an existing TAPI implementation. For example, you can control whether it will attempt to Lock each Row for editing – and even allows you to supply Custom PL/SQL to implement the locking. Note that the lock is still only taken when the page is submitted (unlike Oracle Forms, which locks the record as soon as the user starts editing it) – which is why we need to prevent lost updates:

Preventing Lost Updates

The Interactive Grid allows the developer to choose the type of Lost Update protection (Row Values or Row Version Column). The help text for this attribute should be required reading for any database developer. In my case, I might choose to turn this off (by setting Prevent Lost Updates = “No” in the Save Interactive Grid Data process) since my TAPI already does this; in my testing, however, it didn’t hurt to include it.

Other little bits and pieces

I found it interesting that the converted Interactive Grid includes some extra columns automatically: APEX$ROW_SELECTOR (Type = Row Selector), APEX$ROW_ACTION (Type = Actions Menu), and ROWID. These give greater control over what gets included, and you can delete these if they are not required.

Another little gem is the new Column attribute Heading > Alternative Label: “Enter the alternative label to use in dialogs and in the Single Row View. Use an alternative label when the heading contains extra formatting, such as HTML tags, which do not display properly.”.

Demo

If you’d like to play with a working version of the reference application, it’s here (at least, until the EA is refreshed) (login as demo / demo):

http://apexea.oracle.com/pls/apex/f?p=SAMPLE560&c=JK64

I’ve checked in an export of this application to the bitbucket repository (f9674_ea1.sql).


Monitoring AWS Costs

I’ve been running my APEX sites on Amazon EC2 for many years now, and I’ve gone through a number of infrastructure upgrades and price changes over time. I have some alerts set up, e.g. if a server starts getting very busy or if my estimated charges go over a threshold. Today I got an alert saying my estimated monthly bill will be over $100 which is unusual.

One of the most useful reports in AWS is the Instance Usage Reports (found under Dashboard > Reports > EC2 Instance Usage Report). I tell it to report Cost, grouped by Instance Type, which gives me the following:

aws_instance_usage_report

As you can see, my daily cost was about $1.58 per day, and this shot up on the 16th (note: these rates are for the Sydney region). I was running Oracle on an m1.medium SUSE Linux instance until June 16, when I upgraded it to an m3.medium instance. I have a Reserved Instance (RI) for m1.medium, but not for m3.medium, which is why the cost has shot up. That RI will expire soon; I will purchase an m3.medium RI which will bring the cost of that instance back down to about $1 per day. Until I do that, I will be charged the “On Demand” rate of $4.63 per day.

I’m also running two t2.nano Amazon Linux instances as my frontend Apache servers. Even though they are the smallest available instance type (nano), they barely register over 1% CPU most of the time. I’ve moved all the DNS entries across to one of those nano instances now, so I will soon decommission one which will save me a few extra dollars per month.

As an APEX developer, outsourcing the hardware-related worries to AWS has been the best decision I’ve made. I’ve only suffered a couple of significant outages to date, and in both instances all my servers were still running without issue when connectivity was restored. I can spin up new instances whenever I want, e.g. to test upgrades (you might notice from the graph that I did a test upgrade on an m3.medium instance on June 14).

In case you’re wondering, the total time I needed to take down my Apex instance, take a snapshot, spin up the new instance, and swap the IP address across to it, was about 30 minutes. And that included about 10 minutes lost because I accidentally picked an incorrect option at one point. Not only that, but my upgrade also included changing from magnetic disk to SSD, which seems a bit faster. Overall I’m pretty happy with all that.


Checkbox Item check / uncheck all

If you have an ordinary checkbox item based on a list of values, here is a function which will set all the values to checked or unchecked:

function checkboxSetAll (item,checked) {
 $("#"+item+" input[type=checkbox]").attr('checked',checked);
 $("#"+item).trigger("change");
}

For example:

checkboxSetAll("P1_ITEM", true); //select all
checkboxSetAll("P1_ITEM", false); //select none

It works this way because a checkbox item based on a LOV is generated as a set of checkbox input items within a fieldset.

Note: If it’s a checkbox column in a report, you can use this trick instead: Select All / Unselect All Checkbox in Interactive Report Header