Fame at last for my biggest Apex project to date

I'm very pleased to see that the Apex project I started and worked on for several years is now the subject of an entry under Customer Quotes on the OTN Apex page.

"At Northgate Revenues & Benefits, we have used APEX to replace our legacy Oracle Forms system comprising around 1500 Forms. Our user interface has 10,000 end users daily, across 172 clients, who this year sent out over 12 million Council Tax annual bills worth £15billion and will pay out benefits of over £13billion. Our clients now experience, on average, sub second response times across a working day. We are continuing to leverage our investment in Oracle Application Express by delivering citizen facing solutions as well as launching the conversion of our Social Housing application which will replace 3,500 Oracle Forms running at 100 clients worldwide, with a total of 15,000 end users managing circa 3,000,000 properties. Oracle Application Express has helped us to make the move away from Oracle Forms whilst delivering benefits to our clients and our business.."

-- Alan Powell, Director of Products and Services, Northgate Public Services

This started life as a demo I built (using HTMLDB 1.6) for an alternative, simplified interface for "power users" to work alongside the Forms application, but was so well received by customers that it quickly became the interface, with the Forms interface eventually retired.

Building a seriously large business application with Apex is a very different ball game from the typical Apex "use case", and required a different approach. We had to invent for ourselves some of the features that were built in to later versions of Apex (and some that still haven't been), such as:

• Nested regions
• A Forms to Apex migration tool
  
• Dynamic Actions - i.e. a declarative way for developers to implement Javascript and AJAX functionality, without all of them having to become Javascript experts.
• Declarative tabular forms based on Apex collections, with validation and dynamic actions
  

Why did we go for Apex and not ADF? I blogged about this a few years ago and stand by what I said then (though I haven't had the chance or need to go back and look at ADF 4 years later).

This must be one of the biggest Apex projects ever undertaken, and I'm proud to have played a major part in it.

SQL Overlap Test

Many times I come across SQL where the developer is trying to check for overlap between two ranges (usually date ranges, but sometimes numbers). For example, to meet the requirement "select all employees whose hired from and to dates overlap with the project start and end dates".

The developer sketches out all the possible overlap scenarios and finds four:

1) End of range A overlaps start of range B:
A----
    B---------

2) Start of range A overlaps end of range B:
    A--------
B----

3) Range A falls entirely within range B:
    A---
B--------------

4) Range B falls entirely within range A:
A--------------
    B---

This leads to SQL like this (assuming all values are not null):

where (a.start < b.start 
       and a.end between b.start and b.end)
or    (a.start between b.start
       and b.end and a.end > b.end)
or    (a.start between b.start and b.end
       and a.end between b.start and b.end)
or    (b.start between a.start and a.end
       and b.end between a.start and a.end)

If, as is often the case, the end dates are allowed to be null, meaning "forever", then the SQL becomes yet more complex. In some cases I have seen attempts at this where the developer has got it wrong and missed out one of the cases altogether.

In fact it is much easier to look at the cases where A and B do not overlap, because there are only two such cases:

1) Range A ends before range B starts:
A---
       B-----

2) Range A starts after range B ends:
       A-----
B---

This leads to the much simpler SQL:

where not (a.end < b.start or a.start > b.end)

which can be rearranged to the even simpler (though perhaps less intuitive):

where a.end >= b.start 
and   a.start <= b.end

Even if we have to allow for null end dates this is now very simple:

where nvl(a.end,b.start) >= b.start 
and   a.start <= nvl(b.end,a.start)

I don't claim that any of the above is original, I am sure this algorithm appears in many SQL and other books. But I see variants of the long-winded version (sometimes bug-ridden ones) so often I thought it worth documenting here so I can point to it in future.

Shameless boasting

I hate to boast but...

StackOverflow has become one of my favourite forums for reading and sometimes answering Oracle-related questions (though it covers all programming topics in fact).

Today I am the first person ever to be awarded the Oracle badge for having earned 1000 upvotes for my answers to questions with the Oracle tag:


Of course, this may just mean I have too much time on my hands...

Chris Date Seminar

Chris Date is giving a 2 day seminar called SQL and Relational Theory - How to Write Accurate SQL Code in Edinburgh, 13th-14th May 2010. I really wish I could go to it myself. I attended one of his seminars back in about 1997 and it was both riveting and highly educational.

Apex 4.0 - no more dummy branches required

How many times do you see this error page while building an Apex page?

ERR-1777: Page 1 provided no page to branch to.
Please report this error to your application administrator.

To avoid that, I have a habit of creating an unconditional branch back to the same page in every page I build, with a sequence number higher than all the conditional branches. If I then add new conditional branches I have to ensure that I change the default sequence to a lower value.

A long time ago (in 2007) in this OTN forum thread I asked:

"I don't understand why we have to create these unconditional branches at all - why can't the default behaviour be to branch back to the same page if no other branch is taken? When is the "no page to branch to" error ever useful?!"

In Apex 4.0, this annoyance finally disappears:

"When no branches have been defined then branch page back to itself rather than showing an error."

Sometimes the little things make all the difference!

Trying Out Apex 4.0 Dynamic Actions

In my spare moments I am currently familiarising myself with the Apex 4.0 Early Adopter edition. One of the many exciting new features is Dynamic Actions. These allow you to add functionality to your pages that would previously have required writing Javascript, AJAX calls and On Demand PL/SQL processes, but can now be done declaratively.

The following very simple example shows 3 uses:
1) Enabling/disabling one field according to the value of another
2) Calculating and displaying a value when items are updated
3) Retrieving information from the database when an item is changed.

There is little documentation about Dynamic Actions yet, so I'm not sure I have always taken the best approach.

My example is a "Create Employee" page that inserts a row into the familiar EMP table:

I have created a Dynamic Action that makes the Commission item enabled only when the selected job is one of 'MANAGER' or 'SALESMAN'.

MANAGER selected:

CLERK selected:

These are "simple" actions, I merely had to specify which item values trigger the action like this:

... and then specify the item to be enabled/disabled.

Another dynamic action computes the "Total Package" as SAL+COMM when either of these two items are updated:

I did this using a Javascript fragment rather than PL/SQL, to avoid an unnecessary AJAX trip to the database:

(OK so I did have to write some Javascript! But not much.)

A third dynamic action fires when a manager is selected; this looks up the selected employee and gets his/her job title and department name and dislays them in 2 page items:

This last action isn't as efficient as I'd like because it results in 2 AJAX calls: one to get the job and one to get the department name. This could be a case of me just not knowing the right way to do it in one hit. I used 2 "true actions" that use SQL to set the value of an item. The first has this SQL:

select job from emp where empno = :p2_mgr

and the second:

select dname
from dept join emp using (deptno)
where empno = :p2_mgr

Flashback saves the day (again)

I'm posting this as much for my future reference as anything. This morning I accidentally dropped a package body from the schema I was working in - that is to say I intentionally dropped it, but then realised I shouldn't have. And since this package body was the work-in-progress of another developer who likes to build their code using Toad, directly into the database (a hanging offence if it was down to me...) there was no file containing the source that they could re-create it from.

I felt sure flashback would save me, but my first attempt failed:

SQL> select text from user_source as of timestamp (sysdate-1/24)
2 where name = 'MY_PACKAGE'
3 order by line;

no rows selected

A quick Google took me to this forums.oracle.com post where BluShadow said:

select obj# from sys.obj$ where name like 'PKG_MY_OVERWRITTEN_PKG%'

/*
Then, using the obj# run the following (as of 3 hours ago):
*/

select source from sys.source$ as of timestamp(sysdate-((1/24)*3)) where obj# = 1234567
order by line

/*
You can't look at sys.obj# as of any time previously.
These tables form the basis of the ALL_SOURCE database view.
*/

So that explains why my first attempt failed. But I still have a problem as I don't know the obj# for the dropped object. However, I was able to find it out by searching sys.source$ like this:

select * from sys.source$
as of timestamp (sysdate-1/24)
where upper(source) like '%MY_PACKAGE%';

Inspection of the results revealed the required obj#, and I was finally able to obtain the source of the dropped package body. Phew!