Accessible Apex

I am often asked whether Apex can be used to build applications that comply with web content accessibility guidelines. It can, as this demo Apex application aims to prove.

It is a very simple application consisting of 3 pages:

• Login
• Report
• Form

The report page shows some records from a table, and has a link on each row to open the form page for editing, and a Create link in the report header to open the form page to create a new record.

The form page has a Submit button to save the changes and a Cancel link to return without saving changes.

I have tested all 3 pages with the Total Validator tool and they all reach Conformance Level "Triple-A", i.e. all Priority 1, 2 and 3 checkpoints are satisfied (according to the tool).  

Perhaps the most important point is that the application works even when Javascript is disabled in the browser.   It is often thought that Apex applications cannot work without Javascript enabled, but this isn't so; however, many Apex components such as tabs do require Javascript, so they can't be used.  That doesn't mean an accessible application can't have tabs, it just means you have to build your own tab functionality (which I haven't done in this demo application).

I don't have access to a screen reader like Jaws so I have not tested the application with one yet.

UKOUG - Apex SIG

I was lucky enough to be able to attend the UK Oracle User Group's Application Express SIG on Friday - my first experience of any UKOUG event. The event was a "sell out", with all available places taken up well in advance. This in itself is great news, because it shows that APEX really is taking off, which means I can be fairly confident I haven't backed the wrong horse career-wise.

David Peake, the APEX Product Manager, gave the first two presentations:

• The Latest and Greatest from Development


• APEX @ Oracle

The first of these was a tantalising glimpse at what will be in 3.2 (coming soon) and 4.0 (coming later this year hopefully). These are described in the APEX Statement of Direction, but we were able to see some of them in a live demo. I am particularly looking forward to 4.0 for its improved tabular forms, and for Dynamic Actions - i.e. a "rich client" experience without having to write any Javascript.

Dimitri Gielis and John Scott from Apex Evangelists both gave interesting presentations, on Charting in Apex and Dispelling Myths about APEX respectively. Dimitri's piqued my interest to go back and look at charts again, and John's provided a lot of useful ammo for fighting against the usual "Apex is just a power user toy" kind of myths.

There was also a presentation from Matt Nolan and Vincent Migue from e-DBA called "Using Apex to Expose your Business to the Web" - and their website is a fine example of that.

After a short Q&A forum, we all went to the local pub, which was a great opportunity to get to know some of these people a little better.

All in all, a great day out (good grief, how sad do I sound?!) and I'd definitely like to attend the next one, should there be one.

Apex Impact Analysis script (v2)

After receiving useful comments from Patrick Wolf on my previous post, here is an enhanced version of the script that also checks condition expressions. I'm not including condition_expression2 because I don't think that ever contains anything other than literal values (am I right?)

column obj_name format a50

undef search_text

accept search_text prompt "Enter search text: "

select application_id, page_id, 'Region' objtype, region_name obj_name, 'Source' usage_type
from   apex_application_page_regions
where lower(region_source) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Item' obj_type, item_name obj_name, 'Source' usage_type
from   apex_application_page_items
where  lower(item_source) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Process' obj_type, process_name obj_name, 'Source' usage_type
from   apex_application_page_proc
where  lower(process_source) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Branch' obj_type, TO_CHAR(process_sequence) obj_name, 'Source' usage_type
from   apex_application_page_branches
where  lower(branch_action) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Region' obj_type, region_name obj_name, 'Condition' usage_type
from   apex_application_page_regions
where lower(condition_expression1) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Item' obj_type, item_name obj_name, 'Condition' usage_type
from   apex_application_page_items
where  lower(condition_expression1) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Item' obj_type, item_name obj_name, 'Read Only' usage_type
from   apex_application_page_items
where  lower(read_only_condition_exp1) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Process' obj_type, process_name obj_name, 'Condition' usage_type
from   apex_application_page_proc
where  lower(condition_expression1) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Branch' obj_type, TO_CHAR(process_sequence) obj_name, 'Condition' usage_type
from   apex_application_page_branches
where  lower(condition_expression1) like  '%&search_text.%'
ORDER BY 1,2,3,4
/

Apex: impact analysis script

Ever wondered where a particular package is being used by your Apex application(s)? This simpleSQL Plus script may be of use:

column obj_name format a50

undef search_text

accept search_text prompt "Enter search text: "

select application_id, page_id, 'Region' objtype, region_name obj_name
from   apex_application_page_regions
where lower(region_source) like '%&search_text.%'
UNION ALL
select application_id, page_id, 'Item' obj_type, item_name obj_name
from   apex_application_page_items
where  lower(item_source) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Process' obj_type, process_name obj_name
from   apex_application_page_proc
where  lower(process_source) like  '%&search_text.%'
UNION ALL
select application_id, page_id, 'Branch' obj_type, TO_CHAR(process_sequence) obj_name
from   apex_application_page_branches
where  lower(branch_action) like  '%&search_text.%'
ORDER BY 1,2,3,4
/

For example:

Enter search text: my_package.my_fun

APPLICATION_ID    PAGE_ID OBJTYPE OBJ_NAME
-------------- ---------- ------- ------------------------------------------
           101        123 Process Call my function
           101        127 Branch  30
           102        128 Region  My Report
           102        129 Region  My Query
           103         21 Item    P21_MY_ITEM

Feel free to tell me if (a) I have missed anything, or (b) I have re-invented the wheel and should be using some built-in Apex utility!

Apex 3.0

I have finally got access to the Apex 3.0 evaluation instance, after some problems reading the email containing my login credentials. It looks good. The "What's New in Apex 3.0" document details lots of interesting new features; however there a couple of things on my "wish list" that are not mentioned there, but have been added. These are not very exciting, but assist the developer:

• Region Static ID: now you can give your regions an identifier of your choice, rather than having to refer to Apex region IDs like R17346384974630405 in your code. Not only can your own identifiers be easier to read and remember, they won't change when you copy the page.
• #BUTTON_ID#: this new substitution variable means you can reference the button ID in the button template, and so enable manipulation of the button via Javascript.

Up to now I have devised my own work-arounds for these, but these will make life simpler in future.

No doubt I'll stumble across other goodies as I spend more time playing with, er I mean "evaluating", 3.0

Why I Love HTMLDB

Over the last 3 years or so I have got to know HTMLDB well, and have become a big fan of it - as a development tool, not as a "power user toy". I have successfully built and deployed a number of HTMLDB applications. I find it appeals to me in a way that .Net and J2EE never have. Why is this? Here are some reasons:

• all programming is in PL/SQL, my favourite programming language


• all the "boring bits" like pagination of record sets are built-in


• integration with the Oracle database is seamless


• you don't need a big application server to support it


• there is no question of reinventing the wheel by caching data, locking data, implementing business rules in the "middle tier" (there is NO "middle tier"!)

There are, I must admit, some shortcomings:

• it was not designed with large applications and large development teams in mind, so code and build management is a challenge (but one that we are learning to overcome)


• some of the functionality is not yet quite as robust as it could be (hopefully this will improve as the product matures)

However, HTMLDB doesn't seem to be to everyone's taste. Some hardened J2EE fanatics think it is a toy that is not good enough to be used by "programming professionals" - see for example this exchange I had on Oracle WTF.

I guess one of the key comments from the J2EE fan was this: "and as an ex Forms programmer NOTHING is a backward step from oracle forms!"

Now, I will admit that Oracle Forms is now getting rather old and tired - but in its heyday (around 1990-1995) it was a great product. What it did was integrate seamlessly with the database and do all the drudgery of paginating record sets etc. while you got on with implementing the business requirements. In fact, all the reasons I gave above for liking HTMLDB apply equally to Oracle Forms.

But for some years now, Forms has been looking like a "legacy system" - shoe-horned into the ubiquitous browser interface as WebForms but never looking quite at home there. And the alternatives I saw seemed to be a step backwards in terms of data management:

• ASP/.Net: write lots of VB code and hand-crank things like record pagination


• J2EE: write lots of Java code (which looks a LOT like C!), build your own classes to wrap around database objects etc. And cope with the "standards" that came and went like pop idols (JSF is it currently?)

Then I came across HTMLDB, promoted by Tom Kyte on his AskTom website. You could try it out for free, without even downloading anything, on Oracle's hosted HTMLDB development website. Of course, as with anything, there was a learning curve to get over, which probably took me a few months (elapsed: I was only playing with HTMLDB in my spare time.) But what was very soon apparent to me was that this could be the successor to Oracle Forms that I had been waiting for.

However, in their wisdom Oracle have decided to position HTMLDB as a "power user toy" to replace Excel and Access for very small databases - which it certainly can do. But this marketing undermines HTMLDB as a tool for "serious" application developers. It has been suggested to me that this may be because HTMLDB didn't come out of the Oracle Tools group, which of course has JDeveloper to promote.

Once I had built some small-scale applications with HTMLDB to replace aging Forms applications used only internally, the people I work for were sufficiently impressed to consider using it for something a lot bigger. I built a prototype which was received favourably, but we knew we ought to at least look at JDeveloper with ADF, since that is the approach Oracle recommend for moving away from Forms. So we got Oracle to send us someone to demonstrate JDeveloper to us. It all looked very slick, knocking up an "emp and dept" application in a few minutes - but nothing that couldn't have been done just as easily with HTMLDB. And when he tried to customise the application at our request, he quickly came unstuck. Soon afterwards, a colleague went to an Oracle "Developer Day" (or some such title) where JDeveloper was demoed agan. This time, the compilation process failed with a fatal error, and the demonstrator gave up and moved on to something else. What with these unimpressive demonstrations and the fact that our business logic already exists in the form of PL/SQL packages, it was very clear that HTMLDB was the only realistic option to replace the system within the timescale and budget allowed. (My impression is that JDeveloper might be really good in about 3 years time!)

But even if we were building a brand new system, my philosophy would be to build the business logic as PL/SQL packages and then build a light UI application on top of that: I really don't buy the whole J2EE way of doing things. It implies that the application is king, rather than the data. As Tom Kyte often says: applications come and go, but the data lasts "for ever".

So, for now at least, I am staking my future on HTMLDB rather than J2EE. Maybe I'll regret that one day, but I doubt it.

PL/SQL So Doku Solver

So Doku has taken over the UK, if not the world, in the last year. I am now addicted, but at first I couldn't see the point in these puzzle and prefered to write a program to solve them for me. What was the point in that? Absolutely none! But in case you ever feel the need to get a Su Doku puzzle solved without actualy doing it yourself, here is the code - think of it as a late Christmas present!

First, we need to create a table:

create table cells
  ( x number(1,0) not null
  , y number(1,0) not null
  , z varchar2(9)
  , done varchar2(1) not null
  , constraint cells_pk primary key (x, y)
  )
/

Then a package:

create or replace package cell_pkg as
  procedure solve(p_state in varchar2);
  procedure print;
end;
/

create or replace package body cell_pkg as
  procedure solve(p_state in varchar2)
  is
    v varchar2(1);
    cnt integer;
    processed integer;
    it_failed exception;
  begin
    -- Set up a clean board
    delete cells;
    for r in 1..9 loop
      for c in 1..9 loop
        insert into cells (x,y,z,done) values (r,c,'123456789','N');
      end loop;
    end loop;
    -- Apply initial state
    for r in 1..9 loop
      for c in 1..9 loop
        v := substr(p_state,(r-1)*9+c,1);
        if v between '1' and '9' then
          update cells
             set z = v
           where x = r
             and y = c;
        end if;
      end loop;
    end loop;
    -- Start processing
    loop
      processed := 0;
      -- Process cells that are solved but not yet marked as "done":
      for rec in (select * from cells where length(z)=1 and done='N')
      loop
        -- Remove that cell's value from the possible values for other cells
        -- in same row, column or square
        update cells
           set z = replace(z,rec.z)
         where (  x = rec.x
               or y = rec.y
               or (   floor((x-1)/3) = floor((rec.x-1)/3)
                  and floor((y-1)/3) = floor((rec.y-1)/3)
                  )
               )
           and (x <> rec.x or y <> rec.y); -- Exclude self!
        -- Now mark this cell as done
        update cells
           set done = 'Y'
         where x = rec.x and y = rec.y;
        -- Note how many cells processed on this pass
        processed := processed+1;
      end loop;
      -- Look for cells that are not solved, but where they are the only cell
      -- in their row, column or square containing a given value
      for i in 1..9 loop
        for rec in (select * from cells c1
                    where  length(z) > 1
                    and    z like '%'||i||'%'
                    and    (  not exists
                               (select null
                                from   cells c2
                                where  c2.x = c1.x -- Same row
                                and    (c1.x <> c2.x or c1.y <> c2.y) -- Exclude self!
                                and    c2.z like '%'||i||'%'
                               )
                           or not exists
                              (select null
                               from   cells c2
                               where  c2.y = c1.y -- Same column
                               and    (c1.x <> c2.x or c1.y <> c2.y) -- Exclude self!
                               and    c2.z like '%'||i||'%'
                               )
                           or not exists
                              (select null
                               from   cells c2
                               where  floor((c2.x-1)/3) = floor((c1.x-1)/3) -- Same
                               and    floor((c2.y-1)/3) = floor((c1.y-1)/3) -- square
                               and (c1.x <> c2.x or c1.y <> c2.y) -- Exclude self!
                               and c2.z like '%'||i||'%'
                              )
                           )
                   )
        loop
          update cells
             set z = i
           where x = rec.x
             and y = rec.y;
          -- Note how many cells processed on this pass
          processed := processed+1;
        end loop;
      end loop;
      -- Have we solved it yet?
      select count(*) into cnt from cells where length(z) > 1 and rownum = 1;
      exit when cnt = 0;
      -- No.  If we didn't achieve anything on this pass then give up
      if processed = 0 then
        raise it_failed;
      end if;
    end loop;
    print;
    dbms_output.put_line('SUCCESS!!!');
  exception
    when it_failed then
      print;
      dbms_output.put_line('FAILED!!!');
  end;

  procedure print
  is
  begin
    for rec in (select * from cells order by x,y)
    loop
      dbms_output.put(rec.z||' ');
      if rec.y = 9 then
        dbms_output.put_line('');
      end if;
    end loop;
  end;
end;
/

Now, how to use it. Let's take the following Su Doku puzzle as an example:

.6.1.4.5.
..83.56..
2.......1
8..4.7..6
..6...3..
7..9.1..4
5.......2
..72.69..
.4.5.8.7.

We can solve this as follows:

begin
cell_pkg.solve(' 6 1 4 5   83 56  2       18  4 7  6  6   3  7  9 1  45       2  72 69   4 5 8 7 ');
end;
/

(Enter all the grid values reading from left to right, top to bottom, including ALL spaces).
The output will look like this:

9 6 3 1 7 4 2 5 8
1 7 8 3 2 5 6 4 9
2 5 4 6 8 9 7 3 1
8 2 1 4 3 7 5 9 6
4 9 6 8 5 2 3 1 7
7 3 5 9 6 1 8 2 4
5 8 9 7 1 3 4 6 2
3 1 7 2 4 6 9 8 5
6 4 2 5 9 8 1 7 3
SUCCESS!!!

But perhaps you can do better? If you can write a better version, please let me know!

UPDATE 20 April 2006: Bill Magee has picked up the challenge and run with it, and his improved version is here.