OraTransplant

A very succesfull Agile Oracle project

Last year I wrote a couple of blog entries about agile programming in an Oracle world. I’m setting up the new development process for our development team of 20-25 developers. I want to make it more agile and with that comes version control and build automation.

Back then I found some articles by Robert Baillie who has been very succesfull in setting up an Agile Oracle/PHP project. Last week he presented about it at the UKOUG. He also wrote a very comprehensive article about it on his blog. A must read for anyone who wants to setup an agile development process for an Oracle shop!

Our patch and upgrade policy

Eurotransplant allocates organs available for donation in 6 European countries. Whenever an organ donor is available in any of these six countries, Eurotransplant is notified. We collect data about that donor and start the matching to all recipients on the waiting list. This process relies heavily on IT. It’s also a very time critical process and has to be available 24×7, as you can imagine. It can be a matter of life and death for the recipients on the waiting list. That’s why we cannot take a lot of risks with installing new patches and upgrades to our (Oracle) software.

We release a new version of our system every 1.5 to 2 months. During that period, changes to the system are continuously tested. Developers finish the release at least one week before the release date. That last week is used for final testing of all critical components.

With this in mind, we defined our policy for patches and upgrades to the Oracle software. Let’s start with one-off patches. Upgrade and patch sets have to be installed on the test and development environments before the start of the next development cycle. If all goes well, the upgrade or patch set is installed on production at the end of the release cycle, when the new release goes live. So, all tests and the final critical test are done with the patch set or upgrade in place.

Larger upgrades might take even longer and will have separate test environments. A good example was our recent upgrade from database 8.1.7 to 10.2.0. Developing and testing this upgrade was so much work that is was done outside the normal development cycles.

We’re a bit more flexible with installing one-off patches. These patches are limited in scope and thus less risky than full upgrade or patch sets. Also, these tend to be more time critical since they target specific bugs we’re suffering from. A one-off patch also gets installed on test/development first. It doesn’t really matter when it’s installed as long as it is before the start of the final week of critical tests. That way the one-off patch can be installed on the production environment with the next release.

In very exceptional circumstances we can decide to install a one-off patch on production outside normal release dates. But we would only do that if we’re hitting a truly critical bug.

Commit time business rules

This post will explain two things. First is how to setup a business rule that needs to be checked at commit time and no sooner. The other thing is preventing confusing error messages in Oracle Forms when you do so. By default, Oracle Forms will first inform the user that the records where applied and saved followed by an error message about a failing commit.

We wanted to implement a business rule that has to be valid at commit time and no sooner. A well known example for this is a master record that must have at least one detail record. You cannot check this in normal triggers. When inserting the master record there are no detail records yet. The only correct moment to check these kind of rules is at the commit moment.

You can use a trick with a deferred check constraint to accomplish this. You create a column in a table. On that column you place a deferred check constraint. Then you create some database triggers to maintain the value of that column. Make sure the initial value of that column violates the check constraint. Since the constraint is initially deferred it won’t be checked until commit time. Make sure that some database triggers modified the column to a valid value before the commit moment. Read on for a full example. You can also read about the concept in the Oracle Database Concepts guide

That wasn’t too hard, but it led to a problem in Oracle Forms. Oracle Forms would wrongfully notify the user that the records were saved. Immediately after that notification Forms would raise an error saying the commit has failed. That’s very confusing to a user, so we had to work around that too. Keep on reading for that full example.

Let us begin with setting up a simple example of a commit time business rule. In this case I’m creating a master and detail table and I want to make sure that each master record has at least one detail record. First create a Master and Detail table:

create table tab_master (
  id  number(10),
  constraint tab_master_pk
 primary key
 (id));

create table tab_detail (
  id         number(10),
  master_id  number(10),
  constraint tab_detail_fk
 foreign key (master_id)
 references tab_master (id));

Add a column to the master table which we will use to enforce the business rule:

alter table tab_master add (num_details number(10,0));

Now add appropriate triggers to both the master and detail tables to maintain the column in the master table:

-- Note: this is just a concept, and by no means production
-- grade code (e.g. you should also handle things like
-- updating the FK column)
create trigger tab_master_bir
before insert on tab_master for each row
begin
  -- always force the number of details to 0 on an insert
  -- even if the num_details is specified in the insert
  :new.num_details:=0;
end;
/

create trigger tab_detail_air
after insert on tab_detail for each row
begin
  -- increase the detail count in the master record
  update tab_master
  set num_details=num_details+1 where id=:new.master_id;
end;
/

create trigger tab_detail_adr
after delete on tab_detail for each row
begin
  -- decrease the detail count in the master record
  update tab_master
  set num_details=num_details-1 where id=:new.master_id;
end;
/

Put a deferred check constraint to check if the business rule is violated:

alter table tab_master add
constraint chk_num_details
check(num_details>0) initially deferred;

You can now test to insert a master row without a detail:

ENIS@dev01> insert into tab_master(id) values (1);

1 row created.

ENIS@dev01> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (ENIS.CHK_NUM_DETAILS) violated

Inserting a master row and a detail row in the same transaction does work:

ENIS@dev01> insert into tab_master(id) values (1);

1 row created.

ENIS@dev01> insert into tab_detail(id, master_id) values (2,1);

1 row created.

ENIS@dev01> commit;

Commit complete.

Now go to Oracle Forms to build a form on these tables. Create a default master-detail form using the Forms wizards. For table TAB_MASTER there is no need to include the NUM_DETAILS column in the Form. That’s just something for the database triggers and constraints. Make a relationship between the Master and Detail block. You should end up with a Form looking like this:

With this Form you can enter a new master record and some details and save without problems:

But now see what happens when we violate the rule and do not enter any detail records:

Forms first issues a FRM-40400 message informing the user that the record(s) were applied and saved. After dimissing that alert box, the user is confronted with a cryptic error message: “FRM-99999: Error 408 occured. See the release notes file (relnotes) for information about this error”. It looks like Forms is informing the user about a succesfull save before actually performing a COMMIT.

Unfortunately the release notes (of Forms 10.1.2.0.2) do not explain this error. It turns out that Forms wasn’t build to handle commit time errors. You can of course handle the FRM-99999 error in an ON-ERROR trigger and look at the dbms_error_code and dbms_error_text. Those will tell you that a deferred constraint was violated. You can then present a more informational message to the user.

However, this doesn’t change the fact that Forms first issues a FRM-40400 message to inform the user that the records were saved. If you then display an error after this, the user will be confused.

So, you really want to prevent the FRM-40400 message. Many thanks to the Oracle support Engineer that came up with the solution. You can fix this by overriding the ON-COMMIT trigger. In that trigger you check the deferred constraints. If that check fails you display an error. If the check does not fail, you can perform a normal forms_commit.

To check a deferred constraint, you can issue a:

set constraint constraint_name immediate;

So insert the followin code in the ON-COMMIT trigger of your Form:

declare
	v_dummy number;
begin
  -- check the constraint
  forms_ddl ('set constraint chk_num_details immediate');
  if not form_success and dbms_error_code=-2290 then
    -- constraint violated
    v_dummy := show_alert('master_detail_alert');
    raise form_trigger_failure;
  else
  	commit_form;
  end if;
end;

If you now enter a master without a detail and try to save:

If you acknowledge the error by clicking OK, the Forms commit is aborted. When you then enter some details and try to save again: