OraTransplant

Forms 10g certified with Sun JPI

Oracle finally did it! They certified Forms 10g with Sun Java PlugIn. Last week they release patchset 9.0.4.2 for Developer Suite and Application Server. You can read about it in the updated version of the Client Platform Support Statement.

For us, this is great. We’ve got about 300 internal and 400 external users. The external users are in different hospitals and other medical institutions throughout Europe. Getting their sysadmins to install JInitiator can be a challenging task. We’ve found it to be much easier to have them install the well known Sun Java Plugin than the unknown Oracle JInitiator. We already took a risk by deploying based on Sun JPI before it was officially certified.

The only downside to this all is that Oracle only certifies Sun JPI v1.4.2. Certification for version 1.5 is foreseen for Forms 10.1.2 to be released in the second half of 2006.

Screenshots of JDeveloper plugins

About two weeks ago I wrote a blog entry about a presentation of Jules de Ruijter of Centraal Boekhuis. They decided to drop Designer as a tool and move all design information from the repository to flat ASCII files managed/edited by JDeveloper and versioned by CVS. They create some plug-ins for JDeveloper to edit/manage these files.

To my surprise the homepage of OTN had a link to this entry for about a week. The number of visitors to my site increased by some 800%!

The presentation was originally in Dutch, but with all the attention Jules was so kind to translate it to English. He also wrote a very informational comment at the previous blog entry explaining in more detail what the plug-ins do. He has now even send me some screenshots to demonstrate their use of JDeveloper. See below for the screenshots and some explanation.

Navigator and custom actions
The first screenshot show how Centraal Boekhuis organized all the ASCII files. They use a normal folder/subdirectory structure to organize the files by business process/unit (Technical, Business planning, Marketing and Sales, Process Development, etc.), Design Area (Business Model, Data Model, System Design, etc) and Application System (ASSBEH - Assortment- and stockmanagement). So far, that’s all default JDeveloper. The only thing they added are some custom actions; Create New Object, Rename Object, Delete Object, Show Versions, Navigate Back and Search Object. Some of these are for shortcomings of CVS (e.g. rename/delete objects), but there is also a very interesting one: Show Versions shows the actual installed version in the different databases (Production, Test, Development).

Custom tabs in the Editor
The second screenshots shows the extra tabs they created in JDeveloper using the SDK. You can see the normal Source tab of JDeveloper. Notice the little tag “--<cbcode>” at line 2. This seperates the documentation from the actual code. I personally found this a simple yet brilliant idea. By combining the documentation and the code in a single ASCII file they are always stored (and versioned!) together. The three new custom tabs Doc, Code and Test all show only that section of the overall text file. The user/developer can just edit these three parts separately in the tabs, yet it is still combined in one overall ASCII file visible on the first tab.

Network diagram in structure pane
The next two screenshots shows the relationships between all objects. Centraal Boekhuis found out that the relationships (Implements/Implemented By, Calls/Called By, Owns, Owned By, etc) between all Designer objects themselves are valuable information. It’s very hard to maintain this information in the ASCII files. That’s why they’ve extracted this information from Designer and stored in some simple database tables. Then they created another plug-in to JDeveloper to show and edit these relationships in the structure pane of JDeveloper. There is a popup menu with common actions; Delete Relationship, Change Relationship and Navigate Back.

Changing a relationship
The final screenshots show the dialog for changing a relationship

Full screenshots
All the pictures above where parts of the original screenshots Jules de Ruijter mailed me. You can click in the thumbnails below to show the original full-size screenshots. Be aware that they might be too large to show on your screen and your webbrowser might shrink them. If you’re using MS Internet Explorer look for an overlayed button at the righthand size of the image to restore it to its full size.

Improving my blog with WordPress plugins

Since I installed my blog, I’ve added some plugins. This weekend I played around with some more WordPress plugins to improve my blog. I’ve installed the following plugins:

• Get Recent Comments
  This plugin gives me a PHP function to show the recent comments in the sidebar. I’ve also added the most recent posts in the sidebar, but that one is possible with a builtin function from WordPress.
• Identify External Links
  I use this plugin to add a target=”_blank” attribute to all external links. This makes these links open in a new browser window. I also use the plugin to add a small icon to all external links indicating it will be opened in a new browser window.
• Intouch
  Thus plugin is used to create a contact form that emails all input to me.
• Google Sitemaps
  This plugin automatically creates a Google Sitemap and posts it to Google. This is supposed to help Google in indexing my site.
• StatTraq
  This plugin keeps statistics in a number of MySQL tables. Every page requested is stored in these tables enabling quite detailed statistics.
• Trackping Separator
  This plugin gives some functions to split the trackbacks/pings from the comments. WordPress doesn’t distinguish between visitor comments and trackbacks/pings. I wanted to separate them out in a posting page.
• Click Counter
  This plugin keeps track of all external links that are clicked. It also enables the list of popular external links in the sidebar.
• Google Hilite
  This is not actually a WordPress plugin, but just a handy PHP function that will highlight the terms used for a Google search when arriving at a page via Google. I tweaked this one a bit to also show search terms from Yahoo, MSN and the internal WordPress search.
• Live Comment Preview
  This plugin shows a nice preview of your comment while your typing it! Should be easier for commenters to see if/what HTML is accepted.
• CJD Spam Nuke
  Comment spams are automatically recognized by Wordpress and are safely tugged away. This plugin enables me to review those spam comments and remove them completely from the database
• Update 12-jul-2005 PraColorizer
  Colorizes java, C, C++ and C# code
• Update 17-jul-2005 Search Everything
  Search postings, pages and comments and not just postings
• Update 21-jul-2005 Search Reloaded
  Significantly improve the relevance of WordPress’ built-in search engine

Why you need indexes on FK columns

I’ve been developing Oracle systems for about 10 years. Over the years all environments I worked had the rule to create indexes for each foreign key. I knew it had something to do with locking, but never knew the exact details. Until today….

I just started working at Eurotransplant last November, so I wasn’t involved in building the system. As it turns out quite a large number of foreign keys do not have indexes. Together with a quirk in Oracle Forms this led to a locking nightmare.

A note on metalink quite clearly describes the locking related to referential integrity. It was originally written for Oracle v7.0, but it includes notes where newer versions (v7.1.6, v9.0.1 and v9.2.0) changed their behavior. We’re still running v8.1.7.4 so I guess we’re using the v7.1.6 specs of this document.

When you Update or Delete a record in the parent table, a share lock is required on the child table. This lock is held until the end of the transaction (commit or rollback). Other users can also Update or Delete records from the same parent table and they also will acquire a shared lock on the detail table. The problems start when someone wants to make changes to the detail table. They cannot acquire the lock and will hang until the lock is freed.

Most of our transactions come from Oracle Forms. These applications tend to issue DML and follow it with a commit immediately. So, normally a user would not hold this locks for a considerable amount of time. However, we also have an ON-MESSAGE trigger that prevents a message from showing in the status bar but shows it in an actual alert box. So, when a user makes changes and presses Save, he/she gets an alert box stating “FRM-40400 Transaction complete: # records applied and saved”. As it turns out the changes haven’t been applied and saved yet. The DML statement have been sent by Oracle Forms, but the COMMIT is only issued after the user dismissed the alert.

Due to trigger logic changing some records can take a considerable amount of time. Users tend to press Save and then continue work in another application or will do something else. After 10-20 seconds the DML is completed and Forms presents the alert box to the user. By then, the user is doing something and who know how long it will take before he/she dismisses the alert box. All that time, he/she still holds the locks, including the shared table lock on the child table

Lesson learned: Create indexes on all foreign keys or at least for the ones where the parent table can be changed. For this locking problem, foreign keys to static tables (e.g. domain values) do not have to be indexed.

Oracle to offer JDeveloper for free?

InfoWorld is reporting that Oracle will announce at JavaOne that it will offer JDeveloper for free. That would be great news. This will only help JDev in gaining more market share, which is always good for us Oracle professionals.

Jonas Jacobi already told us that Oracle will announce pricing for ADF Faces at JavaOne. It sounds very reasonable that they will announce there entire pricing scheme for JDev related products.

Moving design info from Designer to JDeveloper

Yesterday I attended a seminar at IT-eye. Andrej Koelewijn was presenting about a project he did for us. Two of our own developers and he created a disconnected Swing client to report donor information to our central database. What really caught my attention was a presentation by a client of IT-eye on their choice to drop Designer and move all design information to JDeveloper.

They decided they wanted to drop the tool Designer, but not all the information they gathered in it since the mid 90’s. He had quite a number of arguments why they made this choice and I must say that most of them sounded quite reasonable. You might be in quite a different situation and decide to go with Designer for a couple of more years.

I guess we’re in the same situation; you’ve got tons of information in Oracle Designer. Unfortunately Oracle hasn’t really added new functionality to Designer for quite a while and their statement of direction sais they won’t be adding new features or enhancements. All the new things you’re developing (10g specifics, J2EE apps, etc) cannot be modelled in Designer. The 100% (re)generation goal you used to have becomes more and more difficult or impossible if you want to use these new features.

They decided to drop the generators in Designer and just continue to maintain the Forms and Reports in the respective Builders. But what about all the design information in the Designer repository? They looked at the large number of different objects in Designer. This leads to a huge number of properties you can set. Analysis shows that only a small number of these properties really hold valuable information. A lot of them are never used or are specific to the generators. The actual business information is stored in a relative small number of properties. The other valuable information is stored in all the relationships between the objects (owned by, called by, etc).

They decided to convert all relevant information to plain ASCII files. Each object (business function, table, etc) was stored in a separate text file. The files were placed in a directory tree related to their application system and object type. Each file contains both the design/business information and the actual code. For example the text file for a table not only contains the description from Designer, but also the actual CREATE statement.

The files are versioned using CVS and edited through JDeveloper. They created some small JDeveloper plugins to ease the editing of these files. The plugin added some tabs to the editor of JDeveloper. The normal Source tab shows the entire file. A custom tab Documentation showed only the documentation part of the file and a Code tab showed the SQL statements. A new tab Test was added to store test cases in the future. The tabs are just an easy way to edit separate sections of the text file, but they do make it a lot easier.

They also created a custom plugin to JDeveloper to show the “network diagram”. That’s all the relations (owned by, called by) between the different objects. This shows a tree structure in the structure pane of JDeveloper.

I loved their KISS (Keep It Simple Stupid) approach. Storing it in plain text files and building on top of great functionality already available in tools (CVS + JDeveloper). Perhaps I have to let the idea rest for a while and look at it again in a couple of weeks/months. Who knows where this may lead us?

Update: To my surprise, this entry has been linked from the OTN homepage! I also noticed in Andrej Koelewijn’s post, that the (Dutch) presentation is available for download. I wasn’t really sure if they wanted me to use the client name in this blog entry. Since Andrej did it, I guess it is okay to say it was Jules de Ruijter of Centraal Boekhuis and to thank him for a very interesting presentation. Centraal Boekhuis is a very well known Oracle shop in the Netherlands. Toon Koppelaars of Centraal Boekhuis wrote the award winning paper “A Database-centric Approach to J2EE Application Development” last year.

Update: Since this article has been linked from the OTN homepage it is getting quite a number of hits. Jules de Ruijter was so kind to translate his presentation to English.

Update 10-jul-2005: This blog entry is now linked from the Chinese OTN homepage. See the screenshot to prove it. I’m sorry but my Chinese is a bit rusty and I cannot offer a translation of this page

How to configure Forms to use Sun JPI

I just got a question from a blog visitor how they can get Forms to use Sun JPI and not Oracle JInitiator. I’ll try to explain the exact steps in this post. First of all, I have to warn you that Sun JPI is not (yet) certified. This is also stated in the Oracle Client Platform Support statement. However, they are targeting for the next patchset to support it.

We have a lot of external users in Europe. We found that getting their sysadmins to accept Oracle JInitiator can be a challenging task. Getting the to install the well known Sun JPI is much easier. I do not always agree with them, but that’s just the situation we have to live with.

Here are the changes we made:

• In formsweb.cfg change baseHTML, baseHTMLjinitiator and baseHTMLie to “basejpi.htm”. You could also make a copy of basejpi.htm to create your own custom base file and point to that file.
• The setting of IE=native or IE=JInitiator is no longer relevant. Basically, what this does is determine if the setting for baseHTMLjinitiator (IE=JInitiator) or baseHTMLie (IE=native) should be used. Since we changed both base-parameters to point to the same file, this is no longer relevant.
• The basejpi.htm uses several variables from the formsweb.cfg in the EMBED and OBJECT tags. These are: jpi_download, jpi_classid, jpi_codebase and jpi_mimetype. I’ve played around with these parameters for quite a while. I’ve explained in a previous blog posting about these settings and which values we eventually used.

Use color scheme colors in Oracle Forms

I just found out how to set Color properties for objects in Oracle Forms that refer to the colors of the currently used ColorScheme.

The colorScheme is something you can set in the webforms.cfg or set in the URL calling Oracle Forms. There are 7 different documented colorSchemes available in Oracle Forms and one undocumented one (blaf). But what if you give your user the freedom to select their own colorScheme. What color would you then use for items in your Forms? Wouldn’t it be nice if you can just refer to the colors from the colorScheme? Well you can!

We’re having some issues with different color palettes in Oracle Forms. It messes up the background color of items which have no background color set. I’m working on that with Oracle Support. When searching for more information on MetaLink I stumbled across one document that refers to some undocumented colors.

It appears that the undocumented colors “OLAFVeryLight”, “OLAFLight”, “OLAFDark” and “OLAFVeryDark” exist. You cannot select them from a list in Oracle Forms, but you can just type them in the properties palette. This must be a very unknown feature. None of these colors give any hit on Google and only 4 forum entries on MetaLink refer to these colors.

Works like a charm although I bet it is not officially supported by Oracle. Also, you never know if they will keep this feature in a future version or perhaps officially document it.

Update: A binary search over all files of Oracle Forms revealed that the colors “automatic”, “OLAF0″, “OLAF1″, “OLAF2″, “OLAF3″ and “OLAF4″ are also supported. Do mind that all these names are case sensitive.