OraTransplant

Eurotransplant Customer Snapshot online

Oracle Netherlands has published a (Dutch) Customer Snapshot about Eurotransplant on their site. It’s a short story explaining what Eurotransplant is and how we used Oracle products/techniques to tackle some challenges. It’s primarily about our move from client-server Forms 6i to web-deployed Forms 10g and our adoption of JDeveloper, ADF, JHeadstart and Swing. We’ve created some JHeadstart/UIX based web applications and we’ve build a Swing client that can run disconnected on a users laptop. It will synch its data with the central database through some web services running on Application Server 10g once a (UMTS) connection is available.

Ever since I have this weblog, I’ve been in touch with a number of people within Oracle. I think that’s one of the perks of having this weblog. Our close relation to Oracle has given me more insights in what’s happening within Oracle and what to expect in the coming period. The weblog also gave me the contacts that approached me for this Customer Profile. I think this close cooperation with Oracle can prove to be beneficial to both of us.

Update: Oracle Global has also released a Customer Snapshot on Eurotransplant. It’s even mentioned in the News sections on the Forms site of OTN and the J2EE for Forms/Designer Developers site on OTN.

Finally a proper domain name

Until now I was using a subdomain of the website of my wife for my weblog. Last week I decided to invest heavily (a whole 7 euros) in a separate domain-name for my weblog: http://www.oratransplant.nl.

I think I changed everything to use the new URL, but you never know. If you find anything that’s not working, please let it know by commenting on this blog entry. Also, if you’re one of the few that has a link to my site, would you be so kind to update it. I’ve managed to keep the old URLs working with some Apache rewrite rules, but I’m not sure if they will continue working for a longer period of time. Who knows when I need to drop the old subdomain.

Function key not allowed bug?

Ever since we migrated from Forms 6i to Forms 10g (9.0.4) we had user complaining about getting sporadic errors “FRM-41009 Function key not allowed. Press Ctrl+F1 for list of valid keys”. Once they’re in this situation every key pressed will raise this error. The only key that Forms responds to is Ctrl-Q to close the Form. When the form is closed, the application functions normally and all keys can be used again.

I already logged a TAR a couple of weeks ago but without a reproducable testcase there wasn’t much support could do for us. I was hoping they could find some known bug, but they couldn’t. The only thing we could do is inform our users of the issue and asking them if they could come up with the steps to reproduce.

Today we got a break. One of our users called she could reproduce the problem at will. I joined here and she was right. I wrote down the (quite a few) steps to reproduce and tried it at my own test-setup. Fortunately the problem also reproduced there.

I then figured out which forms, menus and libraries were needed to reproduce the case. It turned out I need 26 forms, libraries and menus for the smallest testcase. I doubt Oracle Support would be happy with such a testcase

I also did some searching on Google and Metalink and could find other users with the same problems, but none with a fix. I’ll try to trim down the testcase to a reasonable size in the coming days and will then open a TAR and have Support look at it. Let’s hope they can come up with a fix. I’ll update this blog entry when I have a solution.

Update 30-aug-2005: As it turns out, the error is caused by juggling around with the menus. First to give some more information on the testcase I provided to Oracle:

1. User starts the application with a form that has the menu property set.
2. User clicks a menu entry that does a CALL_FORM('form1', hide, do_replace);
   This form has the menu property set and so the menu is replaced. In our case this was the same menu so you cannot actually see this.
3. User clicks a menu entry that does a NEW_FORM('form2');
   This form does not have the menu property set
4. User clicks a menu entry that does a NEW_FORM('form1');
   This call causes the “FRM-41009 Function key not allowed”

Oracle and I found three workarounds:

1. Setting the menu property of form2 to the same menu
2. Use OPEN_FORM in stead of NEW_FORM when opening form2 (the one without the menu)
3. Do not use DO_REPLACE when calling the first form, but just use CALL_FORM(’form1′)

All of these workaround might change your application works, so please check which one suits your needs.

For anyone who wants to take this up with Oracle support. The original TAR is logged under number 4648156.992 and the support engineer promised to file a bug. As soon as I have the bug number, I’ll update this entry.

Update 10-oct-2005: Oracle found out what the issue was. It’s having a window with the name ROOT_WINDOW in one of your Forms. The Forms that started out in SQL*Forms 3.0 all have windows with this name. Windows named ROOT_WINDOW get a special treatment in Oracle Forms and that’s what’s causing the problem. Oracle published note 334210.1 on Metalink about the issue. The easy fix is to rename the windows to a different name.

Database agility

I wrote a blog entry about version controlling database objects last week. This started some interesting discussions with other Oracle professionals and made Robert Baillie write down some interesting entries at his own blog.

Since then I’ve continued my research on the internet and found some other interesting resources. I’ll just post them to my blog again for the ones that got interested in the subject.

It all started with an article about Evolutionary Database Design by Martin Fowler and Pramod Sadalage. That led my to the Yahoo group agileDatabases. You’ll have to register for a free Yahoo account to access the agileDatabases group, but it is really worth while. There are some very interesting discussions on the associated forum and there is a great powerpoint presentation titled “Database Agility” in the Files section. It’s 33 slides about how to use version control and project automation for databases.

If you’re interested in the subject, you definitely have to go to the Yahoo group and sign up!

OraTransplant is back

Last Saturday morning my webhoster decided to upgrade the version of PHP. That completely broke my Wordpress installation. I searched the internet for the problems I had and quickly found that they only upgraded PHP and not the associated ionCube loader.

Somehow they do have the time to upgrade PHP on a Saturday morning but no time to look at any of their support calls during the weekend. It’s now Monday evening and they still haven’t replied to any of my support calls. They only managed to fix the problem Monday afternoon. This is not the first time I had problems with them, so I guess it’s time to find a different host.

Apologies to the readers that wanted to visit my blog the last couple of days.

Version control of database objects?

In the coming months I will be introducing Subversion to Eurotransplant for version control of all our software. This is part of a bigger plan to learn from 3GL development. Using Subversion is pretty straightforward for things like PLL libraries and FMB files, but what about database objects?

Stored PL/SQL objects (packages, function, procedures and triggers) are also quite straightforward. All these DDL script have CREATE OR REPLACE commands and will replace any previous version of the PL/SQL object.

But what about tables? You initially create these with a CREATE TABLE and during the rest of its live it might have numerous ALTER TABLE statements to change the table. How to keep these in version control?

I came up with a couple of things to consider:

Track end state of the object or modifications?
I’m struggling with this one. On the one hand I would like to store only the table structure as I want it to be in the database. This could be a XML document as created by the DBMS_METADATA package. I would then need something to analyze this XML file and compare it to a current version in the database. It would then need to make any necessary changes to the table in the database to match the structure in the XML file.
The other possibility would be to have the developer record just any ALTER TABLE statements that are necessary to change the table. This gives the developer more freedom in complex data-conversions. What about adding a new column to a table, filling this column with a concatenation of two other columns, drop the two other columns, change the new column to NOT NULL. This could be a real world conversion, but I do not see me expressing these sort of conversions in XML and then deploy them to a database automatically.
So I guess I’ll go for the ALTER TABLE history in one file.

Support for automatic builds
To improve our quality and productivity I want to be able to automatically release a version from subversion to a database. We constantly refresh our development/test databases with (made anonymous) data from our production database. I would like to be able to reset a development database to the production version and then run all changes that are made in Subversion against this database. This automatic build might even include a number of automated tests.
If I go for a SQL file with lots of ALTER TABLE history (see first point), then how would I have this automatic build detect the current state of the database. If there are 10 ALTER TABLE statements in the SQL file, how do I detect which ones have already been run against this database and which still have to be run.
One solution would be to convert the original SQL file from version control to a (set of) SQL file(s) with some extra commands. They could log each executed ALTER TABLE in a sort of dictionary-view. The script could then detect which statement have already been run and can be skipped.
One other way would be to store snapshots of the table state in the original SQL file. I could again use the XML documents as created by the DBMS_METADATA package. After each (series of) ALTER TABLE statement(s) a XML document is included with the state of the table at that moment. When deploying the SQL file to a database I could first get the current state of the table, search for it in the SQL file and start from there.

Support for branching and merging
We do not deliver our software to external customers. We have only one production database running and a couple of development/test environments. This makes live a bit easier. Let’s look at a scenario. We’re approaching the next release. We create a branch of the mainline in Subversion to prepare for release 2.5. Developers can then continue development on the mainline to prepare for version 2.6. Version 2.5 is released to production from its branch. Then a bug report comes in that requires an immediate fix. This fix is developed on the 2.5 branch is labeled as version 2.5.1.
We now have a case where developers have already made changes to a table for version 2.6 and another developer has made changes to the same table on the 2.5(.1) branch. They 2.5.1 developer must be able to merge his changes back in to the mainline.

Special care is needed for merging these changes back to the mainline and still have the automatic build script be able to deploy it to a database. It has to be able to deploy the SQL file to a database which already has the 2.5.1 patch installed as this will be the case on the final deployment to production. It would also be very nice if the automatic build is able to deploy to a database that doesn’t have the 2.5.1 patch yet, but does have some of the 2.6 changes. This can be the case on development/test environments that you do not want to refresh completely as there might have been entered a valuable test-case to that database.

I’m slowly getting there, but I guess it will take some more thinking before I have my final solution. I’ve also been googling for this, but couldn’t really find information how others have solved these issues. Tomorrow I will be attending a mini-seminar at Amis about software development for both Oracle and Java, so let’s hope I can find some answers there. There must be readers of the OraBlogs community that have experienced these same issues? If so, please leave a comment to explain how you solved it.

Update 5-aug-2005: If you’re interested in this subject, you have to checkout the blog of Robert Baillie. Especially his two latest postings on the subject (here and here) are very very very interesting.

Update 5-aug-2005: Googling for the subject resulted in some more perhaps interesting links: here, here and here.