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.