Using apex_item.checkbox2 with multiple identifiers

Normally, in a report you can add a checkbox to select records like this:

select apex_item.checkbox2(1,x.id) as sel
      ,x.mycol
      ,...
from mytable x

And process the selected records using a process like this:

for i in 1..apex_application.g_f01.count loop
  l_id := apex_application.g_f01(i);
  -- ... process ...
end loop;

Since we have set the value of the checkbox to the record ID we can just get that ID from the resulting g_f01 array. What if we need multiple columns in our processing? There are a few approaches we could use:

Option 1. Re-query the table to get the corresponding data for the record ID

This is possible as long as the record ID is a unique identifier for the results in the report. A downside is that this involves running an extra query to get the corresponding data which might add a performance problem. An advantage is that the query can bring back as much data as we need – so if we need more than, say, 6 or 7 columns, this would be a reasonable approach.

Option 2. Concatenate the extra data into the checkbox value

For example:

select apex_item.checkbox2(1, x.col1 || ':' || x.col2) as sel
      ,x.mycol
      ,...
from mytable x

This requires parsing the value in the process, e.g.:

for i in 1..apex_application.g_f01.count loop
  l_buf := apex_application.g_f01(i);
  l_col1 := substr(l_buf, 1, instr(l_buf,':')-1);
  l_col2 := substr(l_buf, instr(l_buf,':')+1);
  -- ... process ...
end loop;

Option 3. Add extra hidden items to hold the data

select apex_item.checkbox2(1,rownum)
       || apex_item.hidden(2,rownum)
       || apex_item.hidden(3,col1)
       || apex_item.hidden(4,col2)
       as sel
      ,x.mycol
      ,...
from mytable x

Note: using “rownum” like this allows selecting the data from the row in the report, even if the underlying view for the report has no unique values that might be used.

Processing involves getting the selected rownums from the checkbox, then searching the hidden item (#2) for the corresponding rownum. This is because the g_f01 array (being based on a checkbox) will only contain elements for the selected records, whereas the g_f02, g_f03, etc. arrays will contain all elements from all records that were visible on the page.

for i in 1..apex_application.g_f01.count loop
  for j in 1..apex_application.g_f02.count loop
    -- compare the ROWNUMs
    if apex_application.g_f02(j) = apex_application.g_f01(i)
    then
      l_col1 := apex_application.g_f03(j);
      l_col2 := apex_application.g_f04(j);
      -- ... process ...
    end if;
  end loop;
end loop;

This approach helped when I had a report based on a “full outer join” view, which involved multiple record IDs which were not always present on each report row, and I had multiple processes that needed to process based on different record IDs and other data, which would have been too clumsy to concatenate and parse (as per option #2 above).


Many happy birthdays

Last year I *(not pictured) celebrated my 42nd circuit around the sun. In accordance with time-honoured tradition, it has been celebrated some time around the same day each September with variations on the following theme:

  • a get-together with friends and/or family
  • my favourite meal (usually lasagne)
  • my favourite cake (usually a sponge coffee torte, yum)
  • a gift or two
  • the taking of photographs, to document how much I’ve grown since the last one

Each year, determining the date this anniversary should fall on is a simple calculation combining the current year with the month and day-of-month. So, in the example of the special, but somewhat disadvantaged, people whose birthday falls on Christmas day (if you are among this select group, you have my sympathies), we could calculate their birthdays using a simple SQL expression like this:

with testdata as (
select date'2000-12-25' as d1
from dual)
select rownum-1 as age
      ,extract(day from d1)
       || '-' || to_char(d1,'MON')
       || '-' || (extract(year from d1)+rownum-1) as d1
from testdata connect by level <= 12;
AGE D1
0   25-DEC-2000
1   25-DEC-2001
2   25-DEC-2002
3   25-DEC-2003
4   25-DEC-2004
5   25-DEC-2005
6   25-DEC-2006
7   25-DEC-2007
8   25-DEC-2008
9   25-DEC-2009
10  25-DEC-2010
11  25-DEC-2011

Of course, as you should well know, this is wrong. It assumes that every year has every day that the anniversary might fall on. If a person is in that very special group of people who were born on the leap day of a leap year, our algorithm produces invalid dates in non-leap years:

with testdata as (
select date'2000-12-25' as d1
      ,date'2000-02-29' as d2
from dual)
select rownum-1 as age
      ,extract(day from d1)
       || '-' || to_char(d1,'MON')
       || '-' || (extract(year from d1)+rownum-1) as d1
      ,extract(day from d2)
       || '-' || to_char(d2,'MON')
       || '-' || (extract(year from d1)+rownum-1) as d2
from testdata connect by level <= 12;
AGE D1          D2
0   25-DEC-2000	29-FEB-2000
1   25-DEC-2001	29-FEB-2001 **INVALID**
2   25-DEC-2002	29-FEB-2002 **INVALID**
3   25-DEC-2003	29-FEB-2003 **INVALID**
4   25-DEC-2004	29-FEB-2004
5   25-DEC-2005	29-FEB-2005 **INVALID**
6   25-DEC-2006	29-FEB-2006 **INVALID**
7   25-DEC-2007	29-FEB-2007 **INVALID**
8   25-DEC-2008	29-FEB-2008
9   25-DEC-2009	29-FEB-2009 **INVALID**
10  25-DEC-2010	29-FEB-2010 **INVALID**
11  25-DEC-2011	29-FEB-2011 **INVALID**

This is because we are constructing a string which may or may not represent a real date in our crazy calendar system. So any self-respecting Oracle developer will know that the “correct” way of calculating this sort of thing is to use the ADD_MONTHS function, generously gifted to all of us for free:

with testdata as (
select date'2000-12-25' as d1
      ,date'2000-02-29' as d2
from dual)
select rownum-1 as age
      ,add_months(d1,12*(rownum-1)) as d1
      ,add_months(d2,12*(rownum-1)) as d2
from testdata connect by level <= 12;
AGE D1          D2
0   25-DEC-2000	29-FEB-2000
1   25-DEC-2001	28-FEB-2001
2   25-DEC-2002	28-FEB-2002
3   25-DEC-2003	28-FEB-2003
4   25-DEC-2004	29-FEB-2004
5   25-DEC-2005	28-FEB-2005
6   25-DEC-2006	28-FEB-2006
7   25-DEC-2007	28-FEB-2007
8   25-DEC-2008	29-FEB-2008
9   25-DEC-2009	28-FEB-2009
10  25-DEC-2010	28-FEB-2010
11  25-DEC-2011	28-FEB-2011

Hurrah, we now have valid dates (this is guaranteed by ADD_MONTHS), and those poor souls born on a leap day can still celebrate their birthday, albeit on the 28th of the month in non-leap years. Some of the more pedantic of these might wait until the following day (the 1st of March) to celebrate their birthday, but for the purpose of our calculation here the 28th is a little simpler to work with.

We package up our software and deliver it to the customer, it passes all their tests and it goes into Production where it works quite fine – for about a week or so.

Someone notices that for SOME people, whose anniversary did NOT fall on a leap day but were born on the 28th of February, are being assigned the 29th of February as their day of celebration in every leap year. However, not everyone has this problem: other people whose birthday is also on the 28th of February are being correctly calculated as the 28th of February whether it’s a leap year or not.

Obviously there’s a bug in Oracle’s code, somewhere. Maybe. Well, not a bug so much, this is due to the way that ADD_MONTHS chooses to solve the problem of “adding one month” when a “month” is not defined with a constant number of days. ADD_MONTHS attempts to satisfy the requirements of most applications where if you start from the last day of one month, the result of ADD_MONTHS will also be the last day of its month. So add_months(date'2000-06-30', 1) = date'2000-07-31',add_months(date'2000-06-30', 1) = date'2000-07-30', and add_months(date'2000-05-31', 1) = date'2000-06-30'.

Let’s have a look at those dates. There’s one person whose birthday was 28 Feb 2000 and our algorithm is setting their anniversary as the 28th of February regardless of year. That’s fine. There’s another person who was born a year later on 28 Feb 2001, and our algorithm is setting their “gimme gimme” day to the 29th of February in each subsequent leap year. That’s not what we want.

with testdata as (
select date'2000-12-25' as d1
      ,date'2000-02-29' as d2
      ,date'2000-02-28' as d3
      ,date'2001-02-28' as d4
from dual)
select rownum-1 as age
      ,add_months(d1,12*(rownum-1)) as d1
      ,add_months(d2,12*(rownum-1)) as d2
      ,add_months(d3,12*(rownum-1)) as d3
      ,add_months(d4,12*(rownum-1)) as d4
from testdata connect by level <= 12;
AGE D1          D2          D3          D4
0   25-DEC-2000	29-FEB-2000 28-FEB-2000 28-FEB-2001
1   25-DEC-2001	28-FEB-2001 28-FEB-2001 28-FEB-2002
2   25-DEC-2002	28-FEB-2002 28-FEB-2002 28-FEB-2003
3   25-DEC-2003	28-FEB-2003 28-FEB-2003 29-FEB-2004 **D4 is INCORRECT**
4   25-DEC-2004	29-FEB-2004 28-FEB-2004 28-FEB-2005
5   25-DEC-2005	28-FEB-2005 28-FEB-2005 28-FEB-2006
6   25-DEC-2006	28-FEB-2006 28-FEB-2006 28-FEB-2007
7   25-DEC-2007	28-FEB-2007 28-FEB-2007 29-FEB-2008 **D4 is INCORRECT**
8   25-DEC-2008	29-FEB-2008 28-FEB-2008 28-FEB-2009
9   25-DEC-2009	28-FEB-2009 28-FEB-2009 28-FEB-2010
10  25-DEC-2010	28-FEB-2010 28-FEB-2010 28-FEB-2011
11  25-DEC-2011	28-FEB-2011 28-FEB-2011 29-FEB-2012 **D4 is INCORRECT**

Edge cases. Always with the edge cases. How shall we fix this? We’ll have to pick out those especially special people who were born on the 28th of February in a non-leap year and add some special handling.

with
  function birthday (d in date, age in number) return date is
  begin
    if to_char(d,'DD/MM') = '28/02'
    and to_char(add_months(d,age*12),'DD/MM') = '29/02'
    then
      return add_months(d,age*12)-1;
    else
      return add_months(d,age*12);
    end if;
  end;
select * from (
  with testdata as (
  select date'2000-12-25' as d1
        ,date'2000-02-29' as d2
        ,date'2000-02-28' as d3
        ,date'2001-02-28' as d4
  from dual)
  select rownum-1 as age
        ,birthday(d1,rownum-1) as d1
        ,birthday(d2,rownum-1) as d2
        ,birthday(d3,rownum-1) as d3
        ,birthday(d4,rownum-1) as d4
  from testdata connect by level <= 12
);

LiveSQL

AGE D1          D2          D3          D4
0   25-DEC-2000 29-FEB-2000 28-FEB-2000 28-FEB-2001
1   25-DEC-2001 28-FEB-2001 28-FEB-2001 28-FEB-2002
2   25-DEC-2002 28-FEB-2002 28-FEB-2002 28-FEB-2003
3   25-DEC-2003 28-FEB-2003 28-FEB-2003 28-FEB-2004
4   25-DEC-2004 29-FEB-2004 28-FEB-2004 28-FEB-2005
5   25-DEC-2005 28-FEB-2005 28-FEB-2005 28-FEB-2006
6   25-DEC-2006 28-FEB-2006 28-FEB-2006 28-FEB-2007
7   25-DEC-2007 28-FEB-2007 28-FEB-2007 28-FEB-2008
8   25-DEC-2008 29-FEB-2008 28-FEB-2008 28-FEB-2009
9   25-DEC-2009 28-FEB-2009 28-FEB-2009 28-FEB-2010
10  25-DEC-2010 28-FEB-2010 28-FEB-2010 28-FEB-2011
11  25-DEC-2011 28-FEB-2011 28-FEB-2011 28-FEB-2012

Now that’s what I call a happy birthday, for everyone – no matter how special.

p.s. that’s a picture of Tony Robbins at the top of this post. He’s a leap day kid. I don’t know when he celebrates his birthdays, but as he says, “the past does not equal the future.”