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


Unique constraint WWV_FLOW_WORKSHEET_RPTS_UK violated

If your APEX application import log shows something like this:

...PAGE 73: Transaction Lines Report
declare
*
ERROR at line 1:
ORA-00001: unique constraint (APEX_040200.WWV_FLOW_WORKSHEET_RPTS_UK)
violated
ORA-06512: at "APEX_040200.WWV_FLOW_API", line 16271
ORA-06512: at line 6

(this is on an Apex 4.2.4 instance)

This is due to a Saved Report on an Interactive Report that was included in the export, which conflicts with a different Saved Report in the target instance. The log will, conveniently, tell you which page the IR is on.

The solution for this problem is simple – either:

(a) Export the application with Export Public Interactive Reports and Export Private Interactive Reports set to No;
OR
(b) Delete¬†the Saved Report(s) from the instance you’re exporting from.

You can find all Saved Reports in an instance by running a query like this:

select workspace
      ,application_id
      ,application_name
      ,page_id
      ,application_user
      ,report_name
      ,report_alias
      ,status
from APEX_APPLICATION_PAGE_IR_RPT
where application_user not in ('APXWS_DEFAULT'
                              ,'APXWS_ALTERNATIVE');

You can delete Saved Reports from the Application Builder by going to the page with the Interactive Report, right-click on the IR and choose Edit Saved Reports, then select the report(s) and click Delete Checked.


Email made Easier

an e-mail letter that has a @ sign on itSending emails from the Oracle database can be both simply deceptively braindead easy, and confoundingly perplexingly awful at the same time. Easy, because all you have to do is call one of the supplied mail packages to send an email:

UTL_MAIL.send
  (sender     => 'sender@host.com'
  ,recipients => 'recipient@example.com'
  ,subject    => 'Test Subject'
  ,message    => 'Test Message');
APEX_MAIL.send
  (p_from => 'sender@host.com'
  ,p_to   => 'recipient@example.com'
  ,p_subj => 'Test Subject'
  ,p_body => 'Test Message');

If you want more control over your emails you can use UTL_SMTP instead; this is what I’ve been using for the past few years because I like feeling in control (doesn’t everyone?). This is the package that APEX_MAIL is built on top of.

If you just don’t trust these high-level abstractions you can use UTL_TCP and interact directly with the mail server. I don’t know, maybe your mail server does some weird stuff that isn’t supported by the standard packages.

If you want to send attachments, you can build this yourself in UTL_SMTP or UTL_TCP, but it’s easier with APEX_MAIL which can send BLOBs. UTL_MAIL can send attachments but only up to 32K in size (half that for binary files which become¬†base64 encoded).

Let’s make up a checklist of features supported out of the box (i.e. without requiring you to write non-trivial code) and see how they stack up:

APEX_MAIL UTL_MAIL UTL_SMTP UTL_TCP
Attachments Yes Yes (<32K) No* No*
Asynchronous Yes No No No
Rate limited Yes No No No
Anti-Spam No* No* No* No*
SSL/TLS Yes No No* No*
Authentication Yes No No* No*

Features marked “No*”:¬†these are not natively supported by the API, but generic API routines for sending arbitrary data (including RAW) can be used to build these features, if you’re really keen or you can google the code to copy-and-paste.

(Note: of course, you can add the Asynchronous and Rate limiting features to any of the UTL_* packages by writing your own code.)

Asynchronous

Calls to the API to send an email do not attempt to connect to the mail server in the same session, but record the email to be sent soon after in a separate session.

This provides two benefits:

  1. It allows emails to be transactional – if the calling transaction is rolled back, the email will not be sent; and
  2. It ensures the client process doesn’t have to wait until the mail server responds, which might be slow in times of peak load.

Anti-Spam

Sending an email within an organisation is easy; internal mail servers don’t usually filter out internal emails as spam. Sending an email across the internet at large is fraught with difficulties, which can rear their ugly heads months or years after going live. One day your server tries to send 100 emails to the same recipient in error, and all of a sudden your IP is blocked as a spammer and NO emails get sent, with no warning.

For the last two years I’ve been battling this problem, because my site allows my clients to broadcast messages to their customers and partners via email and SMS. The SMS side worked fine, but emails frequently went AWOL and occasionally the whole site would get spam blocked. Most emails to hotmail went into a black hole and I was always having to apologise to anyone complaining about not getting their emails – “You’re not using a hotmail address by any chance? ah, that’s the problem then – sorry about that. Do you have any other email address we can use?”

I added some rate-limiting code to ensure that my server trickled the emails out. My server was sending about 2,000 to 3,000 per month, but sometimes these were sent in short spikes rather than spread out over the month. My rate-limiting meant a broadcast to 200 people could take several hours to complete, which¬†didn’t seem to bother anyone; and this stopped the “too many emails sent within the same hour” errors from the mail server (I was using my ISP’s mail server).

I managed to improve the situation a little by implementing SPF (Sender Policy Framework). But still, lots of emails went missing, or at least directly into people’s spam folders.

I looked into DKIM as well, but after a few hours reading I threw that into the “too hard basket”. I decided that I’d much prefer to outsource all this worry and frustration to someone with more expertise and experience.

Searching for an Email Gateway

I’ve been hosting my site on Amazon EC2 for a long time now with great results and low cost, and I’ve also been using Amazon S3 for¬†hosting large files and user-uploaded content. Amazon also provides an Email Gateway solution called SES¬†which seemed like a logical next step. This service gives 62,000 messages per month for free (when sent from an EC2 instance) and you just get charged small amounts for the data transfer (something like 12c per GB).

I started trying to build a PL/SQL API to Amazon SES but got stuck trying to authenticate using Amazon’s complicated scheme.¬†Just to make life interesting they use a different encryption¬†algorithm for SES than they do for S3 (for which I already had code from the Alexandria PL/SQL library). It was difficult because their examples all assumed you’ve installed the Amazon SDK.

It always rejected anything I sent, and gave no clues as to what I might be doing wrong. In the end I decided that what I was doing wrong was trying to work this low-level stuff out myself instead of reusing a solution that someone else has already worked out. A good developer is a lazy developer, so they say. So I decided to see what other email gateways are out there.

I looked at a few, but their costs were prohibitive for my teeny tiny business¬†as they assumed I am a big¬†marketing company sending 100,000s of emails per month and would be happy to pay $100’s in monthly subscriptions. I wanted a low-cost, pay-per-use transactional email service that would take care of the DKIM mail signing for me.

Mailgun

In the end, I stumbled upon Mailgun, a service provided by Rackspace. Their service takes care of the DKIM signing for me, do automated rate limiting (with dynamic ramp up and ramp down), it includes 10,000 free emails per month, and extra emails are charged at very low amounts per email with no monthly subscription requirement.

Other benefits I noticed was that it allows my server to send emails by two methods: (1) RESTful API and (2) SMTP. The SMTP interface meant that I was very quickly able to use the service simply by¬†pointing my existing APEX mail settings and my custom UTL_SMTP solution directly to the Mailgun SMTP endpoint, and it worked out of the box. Immediately virtually all our emails were getting sent, even to hotmail addresses. I was able to remove my¬†rate limiting code. Other bonuses were that I now had much better visibility of failed emails – the Mailgun online interface provides access to a detailed log including bounces, spam blocks and other problems. So far I’ve been using it for a few weeks, and of 2,410 emails attempted, 98.55% were delivered, and 1.45% dropped. The emails that were dropped were mainly due to incorrect email addresses in my system, deliberately “bad” test emails I’ve tried, or problems on the target mail servers. One email was blocked¬†by someone’s server which was running SpamAssassin. So overall I’ve been blown away by how well this is running.

Once I had my immediate problem solved, I decided to have a closer look at the RESTful API. This provides a¬†few intriguing features not supported by the SMTP interface, such as sending an email to multiple recipients with substitution strings in the message, and each recipient only sees their own name in the “To” field. My previous solution for this involved sending many emails; the API means that I can send the¬†request to Mailgun just once, and Mailgun will send out all the individual emails.

Another little bonus is that Mailgun’s API also includes a souped-up email address validator. This validator doesn’t just check email addresses according to basic email address formatting,¬†it also checks the MX records on the target domain to determine whether it’s likely to accept emails. For some domains (such as gmail.com and yahoo.com) I noticed that it even does some level of checking of the user name portion of the email address. It’s still not absolutely perfect, but it’s better than other email validation routines I’ve seen.

Note: Mailgun supports maximum message size of 25MB.

Email Validation Plugin

Mailgun also provide a jQuery plugin for their email address validator which means you can validate user-entered email addresses on the client before they even hit your server. To take advantage of this in Oracle APEX I created the Mailgun Email Validator Dynamic Plugin that you can use and adapt if you want.

PL/SQL API

If you follow me on twitter you are probably already aware that I’ve started building a PL/SQL API to make the Mailgun RESTful API accessible to Oracle developers. You can try it out for yourself by downloading from here if you want. The WIKI¬†on Github has detailed installation instructions (it’s a little involved) and an API reference.

The API supports the following Mailgun features:

  • Email validation – does the same thing as the jQuery-based plugin, but on the server
  • Send email
    • Attachments (CLOB or BLOB)
    • Inline images
    • Mailgun tags
    • Custom mail headers
  • Query Mailgun logs – bounces, unsubscribes, spam complaints

e.g.

MAILGUN_PKG.send_email
  (p_from_email => 'sender@host.com'
  ,p_to_email   => 'recipient@example.com'
  ,p_subject    => 'Test Subject'
  ,p_message    => 'Test Message'
  );

The API Reference has a lot more detailed examples.

My implementation of the Send Email API so far supports the following features:

MAILGUN_PKG
Attachments Yes
Asynchronous Yes
Rate limited Yes*
Anti-Spam Yes*
SSL/TLS Yes, required
Authentication Yes

Features marked “Yes*”: these are provided by the Mailgun service by default, they are not specific to this PL/SQL API.

I’m planning to add more features to the API as-and-when I have a use for them, or if someone asks me very nicely to build them. I’ll be pleased if you¬†fork the project from Github and I¬†welcome your¬†pull requests to merge improvements¬†in. I recommend reading through the Mailgun API Documentation for feature ideas.

If you use either of these in your project, please let me know as I’d love to hear about your experience with it.

Link: Oracle PL/SQL API for Mailgun

This article was not solicited nor paid for by Mailgun. I just liked the service so I wanted to blog about it.

UPDATE 26/4/2016: release 0.4 now uses Oracle AQ to enable asynchronous calls.

UPDATE 5/11/2016: release 0.6 adds APIs for querying your Mailgun logs. Also, added a note about UTL_MAIL’s attachment size limit.

UPDATE 4/10/2017: release 1.0 fixes some bugs and adds a few small features.


Declarative Tabular Form dynamic totals

A common APEX project is to take a customer’s existing spreadsheet-based solution and convert it more-or-less as is into APEX. I’ve got one going at the moment, a budgeting solution where users need to enter their budget requests. They currently enter their requests into an XLS template file which generates subtotals and totals for them.

To do this in APEX I’m going to use a tabular form, and to do the subtotals I’ll use jQuery in a way not too dissimilar to that I described earlier.

Here is a mockup of the screen so far:

apex-grid-sheet

There are column totals that need to be added up and updated dynamically (indicated by the green arrows) as well as subtotals within each row (indicated by the red arrows).

I started by looking at the generated items, getting their ids (e.g. “f09_0001” etc) and writing the jQuery code to detect changes, add them up, and put the totals in the relevant items. I then started repeating this code for each column, and thought “hmmm”.

There were two problems with this approach that I could foresee:

  1. The generated ids in a tabular form can change if the structure of the query changes  Рe.g. what was f08 + f09 => f10 might change to f09 + f10 => f11
  2. I was aware of another form that I would need to build, with a similar structure except that there will be two sets of “Jan-Jun” + “Jul-Dec” columns, each with their own subtotal.

I wanted a more declarative solution, so that the heavy lifting will be done in one set of generic javascript functions, and I simply need to put attributes in the relevant columns to activate them. This is how I’ve approached this:

  • Create the tabular form as usual (mine is based on an APEX Collection) and remove the standard DML processes, replaced with my own that calls APEX_COLLECTION instead.
  • Create a standard report that generates the total items by calling APEX_ITEM.text, with p_attributes=>'data-total="x"' (with a different “x” for each column, e.g. year1).
  • Set the Static ID on the tabular form region (e.g. tabularform).
  • Set Element Attributes on the Jan-Jun column to data-cell="year1" data-col="year1_jan_jun", similarly for the Jul_Dec column.
  • Set Element Attributes on all the Year columns in the tabular form to data-col="yearx", where x is 1..5.
  • Set Element Attributes on the total for the first year to data-subtotal="year1".

The following is the query for the totals report region:

select APEX_ITEM.text(1, TO_CHAR(SUM(year1_jan_jun),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1_jan_jun"') as year1_jan_jun
      ,APEX_ITEM.text(2, TO_CHAR(SUM(year1_jul_dec),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1_jul_dec"') as year1_jul_dec
      ,APEX_ITEM.text(3, TO_CHAR(SUM(year1_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1"') as year1_total
      ,APEX_ITEM.text(4, TO_CHAR(SUM(year2_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year2"') as year2_total
      ,APEX_ITEM.text(5, TO_CHAR(SUM(year3_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year3"') as year3_total
      ,APEX_ITEM.text(6, TO_CHAR(SUM(year4_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year4"') as year4_total
      ,APEX_ITEM.text(7, TO_CHAR(SUM(year5_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year5"') as year5_total
from budget_collection_vw

So, to summarise: all the data-cell items get totalled to the data-subtotal item in the same row; and all the data-col items get totalled to the data-total item below the tabular form.

To do all the hard work, I’ve added the following code to my page’s Function and Global Variable Declaration:

function getSum (qry) {
  //get the sum over all items matching the given jQuery search criterion
  var t = 0;
  $(qry).each(function() {
    t += parseFloat($(this).val().replace(/,/g,''))||0;
  });
  return t;
}

function updateSubTotal (item) {
  // update a row-level subtotal
  // the items to add up are identified by data-cell="x"
  // the item to show the total is identified by data-subtotal="x"
  var cell = $(item).data("cell") //get the data-cell attribute
     ,rn = $(item).prop("id").split("_")[1]
     ,t = getSum("input[data-cell='"+cell+"'][id$='_"+rn+"']");

  // we need to temporarily enable then disable the subtotal
  // item in order for the change event to fire
  $("input[data-subtotal="+cell+"][id$='_"+rn+"']")
    .val(t.formatMoney())
    .prop("disabled",false)
    .trigger("change")
    .prop("disabled",true);
}

function updateTotal (item) {
  // update a column total
  var col = $(item).data("col") //get the data-col attribute
     ,t = getSum("input[data-col='"+col+"']");

  $("input[data-total="+col+"]")
    .val(t.formatMoney())
    .trigger("change");
}

In case you’re wondering, I’m re-using the formatMoney function here.

There’s a number of things happening here. On page load, we add a listener for changes to any input item that has a data-cell attribute; this calls updateSubTotal, which detects the row number for the triggering item, adds up all the values for any input item that has the same data-cell value; and puts the total in the input item with a matching data-subtotal attribute.

We also have a listener for changes to any item with a data-col class; when these are changed, updateTotal adds up any item with the same attribute, and puts the total in an item with attribute data-total.

The jQuery selector [id$='_"+rn+"'] makes sure that the row-level code only finds items ending with the given row number (i.e. '*_0001').

The benefit of this declarative approach is that it is much easier to re-use and adapt.

EDIT: fixed the change trigger so that I¬†don’t need to call updateTotal from updateSubTotal.


BIG checkboxes

Getting older, it’s getting harder to see and click those tiny checkboxes…

checkboxestoosmall

csscheckboxes

input[type=checkbox] {
/* Double-sized Checkboxes */
-ms-transform: scale(2); /* IE */
-moz-transform: scale(2); /* FF */
-webkit-transform: scale(2); /* Safari and Chrome */
-o-transform: scale(2); /* Opera */
}

checkboxesbig

CAN YOU SEE THEM NOW? Ah, good. That’s all right then.

Brought to you by dept-of-coding-by-copy-and-paste.


Google Map APEX Plugins

I’ve published two¬†three APEX Region Plugins on apex.world that allow you to incorporate a simple Google Map region into your application. They’re easy to use, and you don’t need to apply for a Google API key or anything like that (although you can plug your key in if you have one, which enables a few additional features).

1. Simple Map

plugin-simplemap-preview

This allows you to add a small map to a page to allow the user to select any arbitrary point. If you synchronize it with an item on your page, it will put the Latitude, Longitude into that item. If the item has a value on page load, or is changed, the pin on the map is automatically updated.

Source

2. Report Map

plugin-reportmap-preview.png

This allows you to add a map to a page, and based on a SQL query you supply, it will render a number of pins on the map. Each pin has an ID, a name (used when the user hovers over a pin), and an info text (which can be almost any HTML, rendered in a popup window when the user clicks a pin).

If the user clicks a pin, the ID can be set in a page item.

Source

3. GeoHeatMap

Visualise a large set of data points on the map using the Google Maps “Heatmap” visualisation. All you need to do is supply a SQL Query that returns the data points to show, and the visualisation library does the rest.

plugin-heatmap-preview

Your SQL Query must be in the following format:

select lat, lng, weight from mydata;

You can set the Map Style (e.g. to the light blue/greyscale style you see above) easily on this plugin; just copy-and-paste the style codes from a site like snazzymaps.com.

Source

I’m very open to feedback, issues and contributions on all¬†of these. Best way is to raise an issue on the associated github page. Have fun!

Refer to my Plugins page for future updates.


APEX API for Tabular Forms

grid-edit
Ever since I started exploring the idea of using a TAPI approach with APEX, something I was never quite satisfied with was Tabular Forms.

They can be a bit finicky to work with, and if you’re not careful you can break them to the point where it’s easier to recreate them from scratch rather than try to fix them (although if you understand the underlying mechanics you can fix them [there was an article about this I read recently but I can’t find it now]).

I wanted to use the stock-standard APEX tabular form, rather than something like Martin D’Souza’s approach – although I have used that a number of times with good results.

In the last week or so while making numerous improvements to my TAPI generator, and creating the new APEX API generator, I tackled again the issue of tabular forms. I had a form that was still using the built-in APEX ApplyMRU and ApplyMRD processes (which, of course, bypass my TAPI). I found that if I deleted both of these processes, and replaced them with a single process that loops over the APEX_APPLICATION.g_f0x arrays, I lose a number of Tabular Form features such as detecting which records were changed.

Instead, what ended up working (while retaining all the benefits of a standard APEX tabular form) was to create a row-level process instead. Here’s some example code that I put in this APEX process that interfaces with my APEX API:

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

The process has Execution Scope set to For Created and Modified Rows. It first calls my TAPI.rv function to convert the individual columns from the row into an rvtype record, which it then passes to the APEX API apply_mr procedure. The downside to this approach is that each record is processed separately – no bulk updates; however, tabular forms are rarely used to insert or update significant volumes of data anyway so I doubt this would be of practical concern. The advantage of using the rv function is that it means I don’t need to repeat all the column parameters for all my API procedures, making maintenance easier.

The other change that I had to make was ensure that any Hidden columns referred to in my Apply process must be set to Hidden Column (saves state) – in this case, the VERSION_ID column.

Here’s the generated APEX API apply_mr procedure:

PROCEDURE apply_mr (rv IN VENUES$TAPI.rvtype) IS
  r VENUES$TAPI.rowtype;
BEGIN
  log_start('apply_mr');

  UTIL.check_authorization('Operator');

  IF APEX_APPLICATION.g_request = 'MULTI_ROW_DELETE' THEN

    IF v('APEX$ROW_SELECTOR') = 'X' THEN
      VENUES$TAPI.del (rv => rv);
    END IF;

  ELSE

    CASE v('APEX$ROW_STATUS')
    WHEN 'C' THEN

      r := VENUES$TAPI.ins (rv => rv);

    WHEN 'U' THEN

      r := VENUES$TAPI.upd (rv => rv);

    ELSE
      NULL;
    END CASE;

  END IF;

  log_end;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END apply_mr;

The code uses APEX$ROW_STATUS to determine whether to insert or update each record. If the Delete button was pressed, it checks APEX$ROW_SELECTOR to check that the record had been selected for delete – although it could skip that check since APEX seems to call the procedure for only the selected records anyway. The debug logs show APEX skipping the records that weren’t selected.

Now, before we run off gleefully inserting and updating records we should really think about validating them and reporting any errors to the user in a nice way. The TAPI ins and upd functions do run the validation routine, but they don’t set up UTIL with the mappings so that the APEX errors are registered as we need them to. So, we add a per-record validation in the APEX page that runs this:

VENUES$APEX.val_row (rv =>
  VENUES$TAPI.rv
    (venue_id   => :VENUE_ID
    ,name       => :NAME
    ,version_id => :VERSION_ID
    )
  ,region_static_id => 'venues');
RETURN null;

As for the single-record page, this validation step is of type PL/SQL Function (returning Error Text). Its Execution Scope is the same as for the apply_mr process – For Created and Modified Rows.

Note that we need to set a static ID on the tabular form region (the generator assumes it is the table name in lowercase – e.g. venues – but this can be changed if desired).

The val_row procedure is as follows:

PROCEDURE val_row
  (rv               IN VENUES$TAPI.rvtype
  ,region_static_id IN VARCHAR2
  ) IS
  dummy            VARCHAR2(32767);
  column_alias_map UTIL.str_map;
BEGIN
  log_start('val_row');

  UTIL.pre_val_row
    (label_map        => VENUES$TAPI.label_map
    ,region_static_id => region_static_id
    ,column_alias_map => column_alias_map);

  dummy := VENUES$TAPI.val (rv => rv);

  UTIL.post_val;

  log_end;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END val_row;

The pre_val_row procedure tells all the validation handlers how to register any error message with APEX_ERROR. In this case, column_alias_map is empty, which causes them to assume that each column name in the tabular form is named the same as the column name on the database. If this default mapping is not correct for a particular column, we can declare the mapping, e.g. column_alias_map('DB_COLUMN_NAME') := 'TABULAR_FORM_COLUMN_NAME';. This way, when the errors are registered with APEX_ERROR they will be shown correctly on the APEX page.

Things got a little complicated when I tried using this approach for a table that didn’t have any surrogate key, where my TAPI uses ROWID instead to uniquely identify a row for update. In this case, I had to change the generated query to include the ROWID, e.g.:

SELECT t.event_type
      ,t.name
      ,t.calendar_css
      ,t.start_date
      ,t.end_date
      ,t.last_updated_dt
      ,t.version_id
      ,t.ROWID AS p_rowid
FROM   event_types t

I found if I didn’t give a different alias for ROWID, the tabular form would not be rendered at runtime as it conflicted with APEX trying to get its own version of ROWID from the query. Note that the P_ROWID must also be set to Hidden Column (saves state). I found it strange that APEX would worry about it because when I removed* the ApplyMRU and ApplyMRD processes, it stopped emitting the ROWID in the frowid_000n hidden items. Anyway, giving it the alias meant that it all worked fine in the end.

* CORRECTION (7/11/2016):¬†Don’t remove the ApplyMRU process, instead mark it with a Condition of “Never” – otherwise APEX will be unable to map errors to the right rows in the tabular form.

The Add Rows button works; also, the Save button correctly calls my TAPI only for inserted and updated records, and shows error messages correctly. I can use APEX’s builtin Tabular Form feature, integrated neatly with my TAPI instead of manipulating the table directly. Mission accomplished.

Source code/download: http://bitbucket.org/jk64/jk64-sample-apex-tapi


Dumb triggers? Let’s make ’em a bit smarter

Some time back, Connor rightly pointed out that triggers that modify data can get in the way when you need to do out-of-the-ordinary data maintenance, e.g. when you need to fix up a row here or re-insert a row over there. You can’t just disable the trigger or else make your users suffer down-time.

Now, the only purpose for which I use triggers is to do common things like setting audit columns and incrementing a VERSION_ID column, and in certain special cases for carefully implementing cross-row constraints; also, I use them to populate a journal table with all changes to the table. Mind you, in recent times features have been added and improved in the Oracle database (such as Flashback Query and Flashback Data Archive) to the point where I’m almost ready to stop doing this. However, there are still some minor use-cases where having a separate “journal” table can be useful. Any argument about that assertion is ruled “out of scope” for this article! ūüôā

So, assuming we’re sticking with triggers that might change data, a solution to this problem is already built-in to the journal triggers and Table APIs (TAPI) that my PL/SQL code generator creates. This allows me to disable the trigger on any table, just for my current session without affecting any other concurrent activity – and no DDL required.

UPDATED 16/2/2016: now uses a context variable (thanks Connor for the idea)

In the trigger I have this code:

create or replace TRIGGER EMPS$TRG 
  FOR INSERT OR UPDATE OR DELETE ON EMPS 
  COMPOUND TRIGGER 
 
  BEFORE EACH ROW IS 
  BEGIN 
    IF SYS_CONTEXT('SAMPLE_CTX','EMPS$TRG') IS NULL THEN 
      ...etc... 
    END IF; 
  END BEFORE EACH ROW; 
 
  AFTER EACH ROW IS 
  BEGIN 
    IF SYS_CONTEXT('SAMPLE_CTX','EMPS$TRG') IS NULL THEN 
      ...etc... 
    END IF; 
  END AFTER EACH ROW; 
 
END EMPS$TRG;

The trigger takes advantage of some extra code that is generated in the Table API:

create or replace PACKAGE EMPS$TAPI AS 
/***********************************************
 Table API for emps 
 10-FEB-2016 - Generated by SAMPLE
***********************************************/ 

... 
 
-- Use these procedures to disable and re-enable the
-- journal trigger just for this session (to disable for
-- all sessions, just disable the database trigger 
-- instead). 
PROCEDURE disable_journal_trigger; 
PROCEDURE enable_journal_trigger; 
 
END EMPS$TAPI; 

The package body code is quite simple:

create or replace PACKAGE BODY EMPS$TAPI AS 
/***********************************************
 Table API for emps 
 10-FEB-2016 - Generated by SAMPLE
***********************************************/ 
 
...

-- may be used to disable and re-enable the journal trigger for this session 
PROCEDURE disable_journal_trigger IS 
BEGIN 
  log_start('disable_journal_trigger'); 
 
  SECURITY.disable_journal_trigger('EMPS$TRG');
 
  log_end;
EXCEPTION 
  WHEN OTHERS THEN 
    UTIL.log_sqlerrm; 
    RAISE; 
END disable_journal_trigger; 
 
PROCEDURE enable_journal_trigger IS 
BEGIN 
  log_start('enable_journal_trigger'); 
 
  SECURITY.enable_journal_trigger('EMPS$TRG');
 
  log_end; 
EXCEPTION 
  WHEN OTHERS THEN 
    UTIL.log_sqlerrm; 
    RAISE; 
END enable_journal_trigger; 
 
END EMPS$TAPI;

A context variable is set with the name of the trigger to disable it – the default state for a new session (i.e. the context variable not set) means the trigger is enabled.

create or replace PACKAGE BODY SECURITY AS

...

PROCEDURE disable_journal_trigger
  (trigger_name IN VARCHAR2
  ,client_id    IN VARCHAR2 := NULL) IS
BEGIN
  -- set the context to any non-null value
  DBMS_SESSION.set_context 
    (namespace => 'SAMPLE_CTX'
    ,attribute => trigger_name
    ,value     => 'DISABLED'
    ,client_id => NVL(client_id, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')));
END disable_journal_trigger;

PROCEDURE enable_journal_trigger
  (trigger_name IN VARCHAR2
  ,client_id    IN VARCHAR2 := NULL) IS
BEGIN
  -- clear the context
  DBMS_SESSION.clear_context 
    (namespace => 'SAMPLE_CTX'
    ,attribute => trigger_name
    ,client_id => NVL(client_id, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')));
END enable_journal_trigger;

END SECURITY;

So now, to run some data maintenance, I can simply call the TAPI to disable, then re-enable, the trigger:

BEGIN EMPS$TAPI.disable_journal_trigger; END;
/

... do the data maintenance...

BEGIN EMPS$TAPI.enable_journal_trigger; END;
/

Unless the data maintenance is doing something very unusual, this script should be safe to run while the system is still up and running for users.

Also, it would be a trivial exercise to write a routine which disables or enables all the journal triggers at once.

The point of this, of course, is that you should be able to do all this sort of thing without writing a lot of code for each table in your schema – solve it for one table, and then generate the code for all your tables.

Source code/download: http://bitbucket.org/jk64/jk64-sample-apex-tapi