Skip to content

Show/Hide Multi-row Delete button for a Tabular Report

I have a standard tabular report with checkboxes on each row, and a multi-record delete button called MULTI_ROW_DELETE.

If the user clicks the button before selecting any records (or if there are no records), they get an error message. Instead, I’d rather hide the button and only show it when they have selected one or more records.

showhidemultirowdelete

To do this:

1. Edit the MULTI_ROW_DELETE button to have a Static ID (e.g. “MULTI_ROW_DELETE”).

2. Add this function to the page’s Function and Global Variable Declaration:

function ShowHideMultiRowDelete () {
  if ($("input[id^='f01_']:checked").length==0) {
    $x_Hide("MULTI_ROW_DELETE");
  } else {
    $x_Show("MULTI_ROW_DELETE");
  }
}

This looks to see if there are any checkboxes selected, if none are found it hides the delete button, otherwise it shows it.

3. Add this code to the page’s Execute when Page Loads:

ShowHideMultiRowDelete();
$("input[id^='f01_']").change(function(){ShowHideMultiRowDelete();});
$x_Hide("check-all-rows");

This does the initial check on form load (i.e. it initially hides the button, since none of the checkboxes will be selected yet), and adds a listener to the checkboxes so that if any of them are changed, the function is re-run to show or hide the button as needed.

Unfortunately this doesn’t work with the “all rows” checkbox that was generated by the tabular report, so I’ve added a step to hide that checkbox (“check-all-rows”) until I can find a solution for that.

Autoformat ANY amount item, anywhere

If you’re building a “finance-ey” application you probably have plenty of fields that should show and accept monetary amounts – and quite possibly these items may be implemented in a variety of ways – ordinary apex number items, edit fields in tabular reports, or even dynamically generated items using APEX_ITEM.text.

In my case I had all three, scattered throughout the application. Our users routinely deal with multi-million dollar amounts and they had trouble checking the amounts visually, especially when there are a lot of zeros, e.g. “10000010.00″ – so they asked for them all to be formatted with commas, e.g. “10,000,010.00″.

Step 1. SQL number format

So in my first release of the apex application I applied the “FM999G999G999G999G990D00″ format to all the money amount items, including in reports etc. The users were reasonably happy with this, but thought it wasn’t working in all cases – e.g. they’d type in a new amount, and the item wouldn’t get formatted until after they Saved the record. This is because the format is only applied when the Apex rendering engine is formatting the page for display – it doesn’t apply it dynamically as the items are changed.

Step 2. Dynamic Actions using SQL

So I started adding dynamic actions to all the apex items which would call the database to format the amount every time the item was changed. This was ok, but performance wasn’t that great – there was a visible sub-second delay while the page did an ajax call to the database just to do the formatting.

Step 3. Dynamic Actions using Javascript

So then I found a Javascript money formatter and modified my dynamic actions to call that instead. The only downside is that it is not internationally-aware. In my case this application’s target users are all here in Australia, are in the education industry, and they haven’t complained about the lack of international money-formatting support (yet).

Number.prototype.formatMoney = function(decPlaces, thouSep, decSep) {
  var n         = this
     ,decPlaces = isNaN(decPlaces = Math.abs(decPlaces)) ? 2 : decPlaces
     ,decSep    = decSep == undefined ? "." : decSep
     ,thouSep   = thouSep == undefined ? "," : thouSep
     ,sign      = n < 0 ? "-" : ""
     ,i         = parseInt(n = Math.abs(+n || 0).toFixed(decPlaces)) + ""
     ,j         = (j = i.length) > 3 ? j % 3 : 0;
  return sign
    + (j ? i.substr(0, j) + thouSep : "")
    + i.substr(j).replace(/(\d{3})(?=\d)/g, "$1" + thouSep)
    + (decPlaces ? decSep + Math.abs(n - i).toFixed(decPlaces).slice(2) : "");
};

That worked really well, there was no visible delay, and the users were pleased. But I wasn’t satisfied – this trick doesn’t work on the tabular reports or on my APEX_ITEM-generated items.

Step 4. jQuery to the rescue!

So I’ve gone back to the drawing table and decided that I don’t want to have to add Dynamic Actions to each and every item that needs it, which doesn’t work for the items that are generated dynamically (e.g. when the user adds a record to a tabular report). This formatting should be applied automatically to each item, and the only thing I’m going to add to each item is a CSS class. I needed to use some jquery to dynamically bind some javascript to every item that has a particular class, even if the item is added after the page has loaded.

This stackoverflow question came in useful. I added the following to my global javascript file:

$(document).ready(function() {
  $( document ).on('change', '.edit_money', function(){
    var i = "#"+$(this).attr("id")
       ,v = parseFloat($(i).val().replace(/,/g,''))||0;
    $(i).val( v.formatMoney() );
  });
});

All I have to do is add the “edit_money” class to all my money items. For ordinary Apex items, you put the class in the HTML Form Element CSS Classes attribute. For items in a tabular report, the same attribute is under Column Attributes, called Element CSS Classes.

For items generated using APEX_ITEM, I just had to add some extra parameters (p_attributes and p_item_id), e.g.

SELECT APEX_ITEM.text
  (p_idx        => 2
  ,p_size       => 16
  ,p_maxlength  => 22
  ,p_attributes => 'class="edit_money" style="text-align:right"'
  ,p_item_id    => 'f02_'||TO_CHAR(ROWNUM,'fm0000')
  )
...

So, that was a reasonably good couple of hour’s work, I think. I’m not the world’s expect on javascript or jquery by any stretch of the imagination, but I’m quite happy with the result so far. I’m sure there are even better ways of doing this, so if you know of a better way please comment.

APEX: Save a user’s checkbox selection on local PC

You want a checkbox item on a page which is a preference, you want it to be remembered for the user across login sessions, but you don’t want the overhead of storing it in a database table. You might choose to store the value in a cookie instead. It may be lost (e.g. when the user clears their cookies or changes to a different browser or another computer), but we don’t mind – it’s just a preference.

stayonpage

1. Create checkbox item, e.g. P1_STAY_ON_PAGE

Display As = Checkbox
Template = No Label
List of values definition = STATIC2:Stay on page;Y

2. Add dynamic action to the checkbox item to save it when it’s changed

Event = Change
Selection Type = Item(s)
Item(s) = P1_STAY_ON_PAGE
Condition = (none)
True Action = Execute JavaScript Code
Fire On Page Load = (No)
Code = SetCookie("APEX_P1_STAY_ON_PAGE",$v("P1_STAY_ON_PAGE"));

3. Add dynamic action on page load to load it

Event = Page Load
True Action = Execute JavaScript Code
Code = $s("P1_STAY_ON_PAGE",GetCookie("APEX_P1_STAY_ON_PAGE"));

Note that the cookie name (“APEX_P1_STAY_ON_PAGE” in this example) is up to you to choose. Probably best to try making it specific to your application so it doesn’t clash with anything else.

Too Much Validation is Too Much

I had built and was managing a web site that takes registrations from thousands of people around the state for a variety of sporting events. One of the goals of the site is to collect better quality data for the people running the events, i.e. they basically needed to get a better handle on how many people were actually attending.

One of the other goals of the site was to make it as easy and hassle-free as possible for anyone to register. This meant that requiring people to sign up for an account with usernames and passwords was undesirable, so make it possible for someone to just sign up with all their info in one session (i.e. never authenticated), then never return.

This also meant that if someone started to register one day, but abandoned their “shopping cart” (so to speak), and then came back the next day, they happily re-entered all their info again – which caused duplicate records to appear in the database. Someone accidentally closes their browser – another duplicate record. Someone signs up their friend on their behalf, not knowing their friend had already signed up – another duplicate record. Someone with very little computer experience gets an error (e.g. “Date of birth must be entered.”) and responds by closing the browser and restarting – and doing this multiple times – we got five duplicate records from this person.

So I built an automated alert system which would email the team coordinators a list of the duplicate records, based on a simple case-insensitive match on first name + surname (we did have one case last year where two different people happened to have the same name, but this is a very rare occurrence when you’re talking about only a few thousand people). I also built a de-duplicator which allowed me to compare two records side-by-side and delete one of them.

In the crunch week (the week before nominations close), we were getting 40+ registrations per day – and each day I was deleting 4 or 5 duplicate records. I thought there must be a better way.

So I (naïvely) quietly added a simple validation check to the signup page – if the player’s name was already registered it showed the error message “Sorry, a registration under you name has already been created. Please login to change your registration.” along with my contact details.

usererrorIt worked, kind of – immediately I got 3 emails and 1 phone call from people who had started their signup, having earlier ignored or missed the email with their login details, and tried to sign up again. I made sure they could login, and they were able to update their existing registration without creating duplicate records. I was quietly optimistic that it would work better now.

Unfortunately, I was wrong. A few days later (today, actually) I decided to do an audit to see if my change had actually made things better or not. I suspected that some people might ignore that error message and just put in a different name. My suspicion was warranted, as it turns out.

So far I’ve gone through over 800 records and found variances of “Bloggs, Joe” or “Bloggs, Joe B” or “Bloggso, Joe” or “Bloggs Is My Name, Joe” scattered throughout. All my validation had done was put a roadblock in front of the users, who simply drove around it by putting in a slightly different name (I saw a lot of them simply put in their middle name), and now (more importantly) my de-duplicator is useless because it only finds matches on exact given names and surnames.

I’ve removed the validation. The duplicate records are manageable, and the system is overall easier for everyone with less validation.

RETURNING RECORD INTO

This is an idea for an enhancement to the PL/SQL syntax.

If I have the following declaration:

DECLARE
  in_record mytable%ROWTYPE;
  out_record mytable%ROWTYPE;
BEGIN

I can do this:

  INSERT INTO mytable VALUES in_record;

I can also do this:

  UPDATE mytable SET ROW = in_record WHERE ...;

I can do this, as long as I list each and every column, in the right order:

  INSERT INTO mytable VALUES in_record
  RETURNING cola, colb, colc INTO out_record;

But I can’t do this:

  INSERT INTO mytable VALUES in_record
  RETURNING ROW INTO out_record;

Can we make this happen, Oracle?

Grassroots on Apex 5

Scott Wesley has started an excellent series of posts exploring the Early Adopter release of Apex 5.0.apex5

I’m excited about the changes in 5.0 but have been too busy both at work and at home to look into it personally – so for now reading other people’s reviews has had to suffice. I’m looking forward to hopefully hearing more about it at AUSOUG later this year.

My advice is, have a look and give it a go sometime. In other words, do as I say, not as I do :)

Don’t mess with my page, bro

One of my clients reported an issue – they were seeing “Waiting for 1.2.3.4″ and a blank screen when they tried to access the Apex web site I’d built for them. They were using Mozilla on a Windows PC, connecting via Vodaphone 3G – the problem was consistent, and it went away when they used their ADSL connection.

My initial response was “don’t use Vodaphone 3G” because the problem seemed to be outside of my area.  It appears to be a common issue, something that some mobile operators do to reduce image sizes – c.f. https://support.mozilla.org/en-US/questions/791180  and http://www.geekstogo.com/forum/topic/277895-suspected-issue-waiting-for-1234-in-firefox-on-at/

My client did a little more digging (he’s a techie as well) and found this: http://stackoverflow.com/questions/4113268/how-to-stop-javascript-injection-from-vodafone-proxy

After reading that I said I’d give it another go and see what could be done. As far as I could see, the only really viable solution is to add the “Cache-Control: no-transform” header to the responses. Since I’m using Apache, to do this I added the following to my apache config as per http://httpd.apache.org/docs/current/mod/mod_headers.html:

Header merge Cache-Control no-transform

That seemed to fix the problem. What this header does is instruct all intermediaries to not modify the content in any way – i.e. don’t try to recompress the images, don’t inject any extra CSS or javascript into the page, nothing. Adding this header does carry the risk that performance on some mobile networks may suffer (because they will no longer do the image compression), so it’s now up to me to make sure my pages and images are as small as possible.

Don’t (always) call v()

Instead of calling a function, when you can get the same effect by accessing a documented PL/SQL variable, you should. For example:

v('APP_USER')    = APEX_APPLICATION.g_user
v('REQUEST')     = APEX_APPLICATION.g_request
v('APP_ID')      = APEX_APPLICATION.g_flow_id
v('APP_PAGE_ID') = APEX_APPLICATION.g_flow_step_id
v('DEBUG')       = APEX_APPLICATION.g_debug

(Note – g_debug is a boolean, unlike the v() equivalent)

There’s more here: documentation for the APEX_APPLICATION package

I suspect that the implementation of v() is something like this [EDIT: read the comments for more commentary on this, and a more accurate picture of what v() actually does]:

FUNCTION v (p_name IN VARCHAR2) RETURN VARCHAR2 IS
  res VARCHAR2(4000);
BEGIN
  CASE p_name
  WHEN 'APP_ID' THEN
    res := APEX_APPLICATION.g_flow_id;
  WHEN 'APP_USER' THEN
    res := APEX_APPLICATION.g_user;
  WHEN 'DEBUG' THEN
    IF APEX_APPLICATION.g_debug THEN
      res := 'YES';
    ELSE
      res := 'NO';
    END IF;
  WHEN 'REQUEST' THEN
    res := APEX_APPLICATION.g_request;
  ... etc. ...
  ELSE
    BEGIN
      SELECT s.item_value
      INTO res
      FROM wwv_<session-values-or-something> s
      WHERE s.item_name = p_name
      AND s.flow_id = APEX_APPLICATION.g_flow_id
      AND s.session_id = APEX_APPLICATION.g_instance;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    END;
  END CASE;
  RETURN res;
END v;

In addition, instead of calling v('APP_SESSION') / v('SESSION'), you could call the undocumented function APEX_APPLICATION.get_session_id instead, which is probably faster, or refer to the global variable APEX_APPLICATION.g_instance instead. I would suspect that the function normally just returns g_instance anyway, but it’s possible there’s some more logic behind the function.

Disclaimer: use undocumented bits at your own risk.

Some other undocumented goodies that may be useful include (and a lot of these are not available at all via v()):

APEX_APPLICATION.g_flow_alias = application alias
APEX_APPLICATION.g_flow_name = application name
APEX_APPLICATION.g_flow_version = application version string
APEX_APPLICATION.g_flow_status = app availability status code, e.g. AVAILABLE_W_EDIT_LINK
APEX_APPLICATION.g_build_status = app build status code, e.g. RUN_AND_BUILD
APEX_APPLICATION.g_base_href = the base URL for the site, not including the f?p=... bit
APEX_APPLICATION.g_printer_friendly = TRUE if the page was requested with Printer Friendly flag
APEX_APPLICATION.g_excel_format = TRUE if the page’s report is being rendered in CSV format
APEX_APPLICATION.g_date_format = Application default date format
APEX_APPLICATION.g_date_time_format = Application date time format
APEX_APPLICATION.g_timestamp_format = Application default timestamp format
APEX_APPLICATION.g_timestamp_tz_format = Application default timestamp with time zone format

You can have a peek at all the globals in this package with this query (but be warned, any undocumented ones may change, and may not necessarily be set to any meaningful value when your code is running):

select owner, trim(text)
from dba_source
where name = 'WWV_FLOW'
and type = 'PACKAGE'
and ltrim(text) like 'g%'
order by owner desc, line;

Proposed wrapper for APEX_UTIL.set_session_state

I decided to try using a wrapper procedure to isolate calls to APEX_UTIL.set_session_state in an autonomous transaction. I’m currently using it in a project and seeing how it goes in terms of performance.

DISCLAIMER: Don’t just throw this into your mission-critical system without at least testing it thoroughly first.

Since I had Morten Braten’s Alexandria library handy, I simply modified his APEX_UTIL_PKG. If you’re not using this library you can create your own wrapper quite simply:

create or replace procedure sv
  (p_name  in varchar2
  ,p_value in varchar2 := NULL) as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
  APEX_UTIL.set_session_state
    (p_name => p_name
    ,p_value => p_value);
  COMMIT;
end sv;

Since my system has many schemas (one for each application), I would compile this in a “common” schema and then grant execute on it to the schemas that need it, and create local synonyms in each one so that my applications just need to call sv.

ADDENDUM:

As Joel Kallman rightly points out, putting set_session_state in an autonomous transaction means that the new value will not be visible to the rest of the calling code, so for example the call to v() will not return ‘Joe’ here:

sv('P1_NAME', 'Joe');
x := v('P1_NAME'); -- will not be 'Joe'

Therefore, it is intended that sv() be used as the final step in any procedure, e.g.:

PROCEDURE p1_controller IS
  p1_name VARCHAR2(100);
BEGIN
  p1_name := v('P1_NAME');
  <business logic that does something with/to p1_name>
  sv('P1_NAME', p1_name);
END;

APEX_UTIL.set_session_state may or may not commit

When should you commit or rollback a transaction? As late as possible, I would have thought, based on most of the advice in the Oracle world. You certainly want this to be predictable and consistent, at least.

Unfortunately, if you use APEX_UTIL.set_session_state in your PL/SQL process, the result is not so predictable.

Thanks to Martin D’Souza who alerted me to this. I love learning new things, but occasionally you get a bad surprise like this and it’s not so pleasant.

Test case set up – create a table with a single row, and create a simple Apex application with one page, with one region, with an item (P1_N) and a Submit button.

CREATE TABLE test (N NUMBER);
INSERT INTO test VALUES (1);
COMMIT;

TEST CASE #1

Add an On Submit process to the page which fires when the Submit button is clicked, which executes the following:

BEGIN
  UPDATE test SET n = 3;
  COMMIT;
  APEX_UTIL.set_session_state('P1_N', 1);
  UPDATE test SET n = 2;
  APEX_UTIL.set_session_state('P1_N', 1);
  ROLLBACK;
END;

What value would you expect to see in the database table now? I would have expected that the table would hold the value 3 – and indeed, it does.

TEST CASE #2

Modify the process slightly – after the second update, set the item to something different:

BEGIN
  UPDATE test SET n = 3;
  COMMIT;
  APEX_UTIL.set_session_state('P1_N', 1);
  UPDATE test SET n = 2;
  APEX_UTIL.set_session_state('P1_N', 4); --changed here
  ROLLBACK;
END;

This time, the second update to the table has been committed before we issued our ROLLBACK. The new value 2 has been saved to the database. Why?

It’s because APEX_UTIL.set_session_state will issue a COMMIT – but only if the value of the item is changed. If you happen to call set_session_state with the value that the item already has, it does nothing, and does not COMMIT. I understand why a COMMIT is ultimately necessary (Apex session state is stored in a table) – but I disagree that it’s necessary for it to commit my (potentially partial) transaction along with it.

This means that if an exception is raised somewhere in my process, the resulting rollback may or may not rollback the entire transaction, depending on whether any prior calls to set_session_state happened to COMMIT or not. This is difficult to predict and therefore makes debugging harder. Not to mention the fact that it violates the general principle of “either the whole transaction succeeds and is COMMITted, or it fails and the whole transaction is rolled back”. I’m sorry, Apex, but you should not arbitrarily commit part of my transaction without at least telling me.

Mitigations for this? I’m not sure yet. One suggestion from this forum thread was to make the procedure use an autonomous transaction. This would align it more closely to what most developers would expect, I think. Unfortunately it appears the suggestion was rejected (or put on hold indefinitely).

I’m planning on refactoring my code to shift all calls to set_session_state to as late in the process as possible; in addition, I’m thinking that I would put an explicit COMMIT prior to these calls so that my code would have more predictable behaviour. But the idea of wrapping set_session_state in a wrapper procedure with an autonomous transaction seems good to try out as well.

Follow

Get every new post delivered to your Inbox.

Join 215 other followers