OraTransplant

Custom built Forms Migration Assistant

We’re currently migrating from Oracle Forms 6i (deployed using Citrix MetaFrame) to Forms 10g running over the web. For that, we have to migrate about 350 forms, libraries, menus and object libraries. In this blog entry I’ll try to explain why I ended up creating my own Forms Migration Assistant and what changes I’m making to all files during migration.

I started of with the Forms Migration Assistant that comes with Oracle Forms. It’s quite easy to run and it mainly focuses on all the PL/SQL code in your files. It makes some changes to replace obsolete built-ins and it issues some warnings for PL/SQL code that might need changes. In testing the forms, we found that we needed a lot more changes.

I tried to find the source code of the Migration Assistant or a way to extend its classes. Unfortunately that did not work. I then decided to create our own Migration Assistant to run after the one Oracle supplies. With the JDAPI that comes with Oracle Forms it’s not even that difficult. With the JDAPI you can open each module (Forms, Menu, Object Library or PL/SQL library) and get/set each property and add/remove objects.

Over a couple of weeks we tested the migrated application and kept adding more code to my own Migration Assistant to make even more changes to the files. In the end I concluded that there wasn’t much that the Oracle Migration Assistant was changing for me, so I even incorporated those fixes in my own Migration Assistant.

I’m just posting all automated changes I’m making in this blog. Perhaps they are useful to others in the same situation.

Forms, Menus and Object Libraries

• If an object is sub classed from another file, change the filename of this file to lowercase as we’re also moving to a case-sensitive platform (Linux)
• Change the filename of all attached libraries to lowercase
• When a canvas has a background color of “unspecified”, set it to white to keep the old look-and-feel
• When a window has a background color of “unspecified”, set it to white to keep the old look-and-feel
• Set the filename of the Menu property to lowercase
• Add date initialization code (plsql-date-format, builtin-date-format) to the pre-form triggers of older Forms (they originated in SQL*Forms 3.0)
• Set the filename of the icon of a MenuItem to lowercase and remove ICO extension if it is there
• Convert the PL/SQL code of a MenuItem (see below for PL/SQL conversion)
• Set the fill pattern of boilerplate items from transparent to none
• Set the edge fill pattern of boilerplate items from transparent to none
• Set the edge fill pattern of boilerplate items from solid to none
• Decrease the font-size of buttons by 1 to make the text fit in the new rounded Oracle look-and-feel buttons
• Set the filename of the icon of a button to lowercase and remove ICO extension if it is there
• When items have a background color of “unspecified”, set it to white to workaround (non-published) bug 4383847
• Some items had the Required property set to True and the Visible property set to False. Forms 6i did not enforce the required property for such an item, but Forms 10g does. As a dirty fix I clear the Canvas property of these items. That way, Forms 10g also doesn’t enforce the Required property. Do check all these changes carefully. We found some cases were the properties were changed at runtime and the change of the Migration Assistant was undesirable. I hard coded these exceptions in the Migration Assistant.
• Some items had Required=true, Visible=true and Enabled=false. Again, Forms10g enforces this required property whereas our old Forms environment didn’t. For these items I set the Required property to false. Again, check each change. I again made some hard coded exceptions to this rule.
• Forms 10g raises compilation warnings when some properties are set for subordinate (mirror) items. These properties are not uses for subordinate items, hence the warnings. So we cleared all MaximumLength, CopyValueFrom and InitialValue properties of subordinate items.
• Convert the PL/SQL code of all program units (see below for PL/SQL conversion)
• Convert the PL/SQL code of form, block and item level triggers (see below for PL/SQL conversion).

PL/SQL libraries

• Convert the PL/SQL code of all program units (see below for PL/SQL conversion)
• Set the filename of attached libraries to lowercase, because of our move to the case-sensitive Linux.
• Add two new custom build PL/SQL libraries to the core PL/SQL libraries of our application. That way the new custom build libraries are effectively attached to every form in our application. See below for the details of these libraries.

PL/SQL conversion

• Change the use of : PW to get_application_property(password)
• Change the use of BREAK to DEBUG.SUSPEND
• Change the use of CHANGE_ALERT_MESSAGE to Change_Alert_Message.Obsolete from our new custom build library (see below for details)
• Surround the first argument of open_form, call_form, new_form and replace_menu with lower() to handle the new case-sensitive Linux filesystem.

I created a new PL/SQL library to handle all sorts of obsolete calls. In stead of changing all the old PL/SQL code, I just left that code as it was and added some program units to a shared PL/SQL library that is attached to each and every form.

• Change_Alert_Message package. Has two procedures to handle the overloading of both an alert_id and alert_name as first argument.
• Disable_Item, Enable_Item: try to find menu item by the supplied name. If found, enable/disable it
• Exit_Menu, Menu_Help, Previous_Menu, Set_Input_Focus, Show_Menu: completely obsolete built-ins that have just been replace with a null statement.
• Menu_Show_Keys: Just call the built-in show_keys.

I hope this blog entry can be useful for others who are also migrating a Forms application. If your just starting, this might be a helpful pointer of things to consider.

You can download the source code at your own risk. It was my very first Java coding, so looking back at it I’m not too proud of the product. But it might get you started.

Attended AMIS Query about ADF and JHeadstart

Last night I attended the AMIS Query (workshop) about ADF and JHeadstart. About 20 people showed up, which I think is quite good for a meeting that was only published on the Activities section of their site.

Fortunately most of the attendants already worked with JHeadstart and/or ADF. This meant we keep most of the overview stuff quite short and get down to the gritty (nice) details.

Lucas Jellema, who used to be part of the JHeadstart team at Oracle but now works for AMIS, started of with his view on ADF. I already knew most of it, but you always get new details from such talks.

Sandra Muller, part of the JHeadstart team at Oracle, continued with a presentation and demonstration of creating and consuming web services in ADF.

Peter Ebell, also part of the JHeadstart team, showed some of the new features of JHeadstart that will be released next month. They also described these on their blog. There are some great features/enhancements. They’ve implemented fine grained role/function based authorization for Struts actions, tab pages and even individual items. The nice thing is that all authorization questions are tunneled through one proxy class that you can override. By default it just resorts to JAZN/JAAS authorization but you can implement any authorization scheme you like.

Another very welcome feature is to make items read-only based on an EL expression. You can use this for the aforementioned authorization, but it is also very welcome to make items read-only based on the value of other items (e.g. Description can no longer be edited when the Status is Closed).

And yes they did it! A feature that repeatedly didn’t make a release was the generation of radio groups. It’s also something our users have requested a couple of times, and now it is there.

There were also a lot of other tweaks and changes, but you can read all about it on the JHeadstart blog. The new release will only be available for customers with the Supplementary Option and will not be released on OTN. This does reward paying customers (like us) and I can only encourage this. The next major version will most likely again be available as evaluation version on OTN.

It’s always nice to keep in touch with other people with the same interests. Looking forward to the next AMIS Query.

Settings for dynamic versioning with Sun JPI and Oracle Forms

Our Oracle Forms (10g) application will be used by a lot of remote/external users. Convincing their sysadmins to install Oracle JInitiator can be a tedious task. That’s why we’re aiming to use Sun’s JPI in stead of Oracle JInitiator. Unfortunately this is not supported by Oracle yet, but they’re targeting for the next patch to support Sun JPI.

Anyhow, there are four settings in your formsweb.cfg file that are relevant to the Sun JPI and to determine which version to use and where to download it:
jpi_classid – The ClassID of the Sun JVM to use (Internet Explorer specific)
jpi_codebase – Download location of the CAB file for the Sun JVM (IE specific)
jpi_mimetype – Used primarily for Netscape/Firefox but also passed on to IE
jpi_download_page – Download location of the JRE installer for Netscape/Firefox

I’ve been reading Sun’s Plug-In Developer Guide of both version 1.4.2 and version 1.5.0. The most interesting parts are the sections on Multi-Version support (v1.4.2 and v1.5.0), chapter 3 on using tags (v1.4.2 and v1.5.0) and Appendix 6 on Implementation-Version formats.

You have the option for static or dynamic versioning. Static versioning mandates an exact version of Sun JPI to be installed on the workstation, whereas Dynamic versioning just states a minimum version and every higher version will also do. We will go with dynamic versioning to give as much flexibility to our users as possible.

JPI_CLASSID should be set to clsid:8AD9C840-044E-11D1-B3E9-00805F499D93 for dynamic versioning or to clsid:CAFEEFAC-<major version>-<minor version>-<patch version>-ABCDEFFEDCBA for static versioning. For example, the classID for Sun JPI v1.4.2_04 would be clsid:CAFEEFAC-0014-0002-0004-ABCDEFFEDCBA. We will go with “clsid:8AD9C840-044E-11D1-B3E9-00805F499D93”

With static versioning JPI_CODEBASE should be a URL to the .CAB file to download this exact version. A list of possible URLs is available at Sun. For dynamic versioning the URL consists of two parts, separated by a #-sign. The last part specifies the minimum required Java version. An example is Version=1,4,2,4 for Java v1.4.2_04. The first part of the URL should point to the CAB file to download when the required (or a higher) version is not installed on the workstation. You could point to the CAB file of specific version (e.g. http://java.sun.com/update/1.5.0/jinstall-1_5_0_02-windows-i586.cab for Sun Java v.1.5.0_02). You can also just use the version number of a family to automatically download the latest version of this family (e.g. http://java.sun.com/update/1.5.0/jinstall-1_5-windows-i586.cab will now download v1.5.0_03). In our case, we’re running our application over HTTPS. We needed to change all external URLs in the HTML to also use HTTPS, otherwise Internet Explorer raises a warning about combining secure and non-secure items on the same page. Luckily Sun’s download sites are also available through HTTPS, so we can use https://java.sun.com/update/1.5.0/jinstall-1_5-windows-i586.cab#Version=1,4,2,4 to use at least v1.4.2_04 and if that’s not available the latest version of the v1.5 family will be downloaded and installed.

JPI_MIMETYPE is a mime-type in a format like “application/x-java-applet;<version_type>=<implementation_version>”. For static versioning, <version_type> should be set to “jpi-version”. For dynamic versioning, this should be set to “version”. For static versioning, the <implementation_version> should note the required Java version. For dynamic versioning, this should state the minimum required version. Appendix 6 of the Developer Guide shows the syntax of this <implementation_version>. You can just require a family or a very specific version/patch. Use “version=1.4.2_04” to require at least v1.4.2_04. This makes the entire mime-type “application/x-java-applet;version=1.4.2_04”. However, this did not seem to work with Firefox, so we want to the more generic “application/x-java-applet;version=1.4.2”.

JPI_DOWNLOAD_PAGE is the URL where Netscape/Firefox users can download the JRE. Sun’s examples show this as http://java.sun.com/j2se/1.5.0/download.html. Again, we changed this to use https to prevent warnings in your browser, making it https://java.sun.com/j2se/1.5.0/download.html

Conclusion is that the best settings for us are:

• jpi_classid=clsid:8AD9C840-044E-11D1-B3E9-00805F499D93
• jpi_codebase=https://java.sun.com/update/1.5.0/..
..jinstall-1_5-windows-i586.cab#Version=1,4,2,4
• jpi_mimetype=application/x-java-applet;version=1.4.2
• jpi_download_page=https://java.sun.com/j2se/1.5.0/download.html

Update 30-jun-05: I’ve changed the jpi_codebase to https://java.sun.com/update/1.4.2/..
..jinstall-1_4-windows-i586.cab#Version=1,4,2,4 This still ensures a minimal required JPI version of v1.4.2_04. However, when it’s not installed the latest version of 1.4.2 (currently 1.4.2_08) will be downloaded. We’ve switched back from 1.5 to 1.4.2 since we were having compatibility issues with JPI 1.5. Also, we started out with Forms 9.0.4.1.0 which wasn’t certified with Sun JPI. Since then, Oracle released patch 9.0.4.2.0 which does certify Sun JPI as client. However, it only certifies against v1.4.2. Certification for v1.5.x is foreseen for Forms v10.1.2.

Update 29-oct-2005: Perhaps you should change all references to JPI 1.4.2_04 with 1.4.2_06 for your own installation. Oficially 1.4.2_06 is the minimal version required by Oracle. The reason we went for 1.4.2_04 is that most of our installed base had 1.4.2_04 and we didn’t want to force an upgrade of Sun JPI on those users.

Update 2-may-2006: In the update of 30-jun-05 I mentioned the forthcoming support for Sun JPI v1.5.x in Forms 10.1.2. Last summer, we tested Sun JPI 1.5.x with Forms 9.0.4.2 and ran into some issues. We were in close contact with Oracle Support, Product Management and Development to get our heads around it. In the end we couldn’t really find what the problem was. It resulted in an (unpublished) bug being published and a mention of the issue in the Forms 10.1.2.0.2 release notes. Section 4.1.4.4 of the release notes now mention that Sun JPI 1.5.x might sometimes make your Internet Explorer hang when closing the browser on Windows XP. It looks like this issue is fixed in Sun JPI 1.6.x but that’s not expected to be released for a couple of months. So in the end, we decided to stick to our configuration parameters that try to the latest version of the 1.4.2.x family, although we do accept 1.5.x when this is installed on your workstation. That’s a downside of using dynamic versioning; we can specify a minimum version to use but not a maximum version.

Faulty DNS causes lousy performance WebForms

We’re currently migrating from Forms 6i client/server (deployed via Citrix) to Forms 10g via the Application Server. Since the beginning we had issues with very slow performance. Initial startup of the application could take as long as 2-3 minutes. But even when you’re logged in, performance was far from optimal. The first time you opened a menu it took 10-20 seconds.

After days of puzzling we finally found it. A network trace of the workstations showed they were performing numerous DNS lookups. We connect to the application server using HTTPS via a normal web-proxy. So, the URL is something like https://appserver.example.com/forms90/f90servlet. A network trace showed that the workstation is doing a DNS query for “appserver.example.com”. This is not necessary, because contacting the application server is left to the proxy-server and the workstation never needs to make direct contact with the application server.

In our case, the DNS lookup caused huge delays. It turned out this was caused by a faulty DNS. Our internal network doesn’t heavily depend on DNS and so we never ran into this issue before. The DNS server is a standard out-of-the-box installation. This means it has the 13 default root servers configured. This is fine for an internet-connected DNS server, but does not work for internal DNS servers. Our internal-only DNS server cannot contact the 13 root servers.

Back to the workstation. It is sending a DNS request for “appserver.example.com” to our internal DNS server. That server cannot handle the request and will start asking the root-servers. Since it cannot contact these servers this will lead to considerable delays.

It could very well be caused by a bug in Sun’s Java or be related to a bug. If you search for “DNS lookup proxy” in Sun’s bug database you find a number of hits and not all of them have been solved.

The easiest way to correct the problem is making your DNS server respond to the query instantly. You can either do this by letting the DNS server contact the root-servers on the internet, or just add a record for your application server to the DNS server, or remove all root-servers from your DNS server. The last solution will make your DNS server respond to the request instantly. It will just respond with a “Non-existent domain” response, just as it would when you ask any DNS server for “example.invalid.domain”.

With all testing we also found another potential DNS issue. In our browser the proxy server is configured by IP address and not by hostname. For some reason, Sun’s JPI then performs a reverse DNS lookup for this IP address to get the hostname. It then performs another DNS lookup to get the IP address for this hostname. From information in Sun’s bug database I understand this is for extra security and to prevent DNS spoofing attacks. Just keep this in mind, and make sure the IP address does resolve to a hostname and the hostname resolves to an IP address again. It did in our case, so we didn’t have a real issue here.

When trying to resolve the issue, we’ve spend quite some time browsing OTN forums and MetaLink. I couldn’t really find an answer there, but did find numerous other Oracle users with similar problems. I hope this blog entry can help them.

Update: As stated before, if you specified your proxy server as an IP address (and not as hostname), Sun’s JPI performs a reverse lookup of this IP address to hostname and then resolves this hostname back to IP address. We just found out that when your DNS server responds to the initial reverse DNS lookup with a response like ‘Non-existent domain’, your Windows client will then revert to a Netbios lookup of the IP address which takes even more time. The lesson learned is that when you want to use Sun’s JPI and you specify the proxy server as an IP address, you have to make sure there is a proper PTR record setup in your DNS server and that the name resulting from the PTR record resolves back to a valid A record.

ADF-JHeadstart workshop last week

As mentioned earlier, I attended the ADF-JHeadstart workshop last week at Oracle in the Netherlands. Sandra Muller, one of the developers of JHeadstart at Oracle Consulting was giving the workshop and I must say she did a great job.

I attended the Oracle University class training on ADF a couple of weeks ago and was a bit disappointed. I think that was my own fault. Before attending the course, I already worked through the Oracle JDeveloper 10g Handbook from Oracle Press. The book got me started with most concepts related to ADF programming (J2EE basics, MVC, BC4J, etc). I then played around with JDeveloper for a couple of weeks before attending the ADF course. I guess the course targets students without any J2EE/ADF knowledge. This did mean a lot of it was already known to me and the course was not as effective as I hoped it to be.

But the ADF-JHeadstart workshop came to the rescue! First of all, it is targeted as an advanced course to people with the basic knowledge about ADF/J2EE programming. The other great thing is that it revolves around UIX programming, something (almost) not covered in the JDeveloper 10g handbook and the ADF course.

The ADF-JHeadstart workshop started of with setting up your environment (JDeveloper v10.1.2 and JHeadstart v10.1.2) and getting to know the business case. We then continued creating a UIX prototype mockup wizard without any data bindings. This was a great way to quickly build a (non-functioning) prototype to show to a customer. Day one ended with finishing the prepared database design and creating the database schema.

The morning of day 2 was spent creating the Business Components and some business rules. The afternoon of day 2 was used to use actual data in the wizard created at day 1. A good part of day 3 was used to build several UIX pages for the application. The last part of day 3 was used to start of with JHeadstart generation of some other (UIX) pages.

Day 4 was used to complete the JHeadstart generation and to cover some advanced JHeadstart topics, including customization of the JHeadstart templates. The last day was used for J2EE security and the use of DataSources.

The workshop was very useful and much more realistic then the exercises used in any course. The exercises in traditional courses seem to be created to fit the tool being used for 100%. It’s when you’re back at your own desk with a real-world application when you find it is not all that easy as it was in the course. The workshop is much more realistic and also opens your eyes to things that do not work as easy as they appear in JDeveloper. You find a lot of small things that might have cost you a day or two to figure out on your own.

If you have the basic knowledge about ADF/J2EE programming and want to learn more about UIX and/or JHeadstart, this workshop is definitely for you! The good news is Oracle will be running the same workshop again in September in the Netherlands or at any other location/date on request.

Slow performance of *_SOURCE views

We experienced awful performance querying the data dictionary view DBA_SOURCE on our v8.1.7.4 database. Some investigation revealed the following query for this view:

SELECT u.NAME, o.NAME,
       DECODE (o.type#,
               7, 'PROCEDURE',
               8, 'FUNCTION',
               9, 'PACKAGE',
               11, 'PACKAGE BODY',
               13, 'TYPE',
               14, 'TYPE BODY',
               'UNDEFINED'
              ),
       s.line, s.SOURCE
  FROM SYS.obj$ o, SYS.source$ s, SYS.user$ u
 WHERE o.obj# = s.obj#
   AND o.owner# = u.user#
   AND o.type# IN (7, 8, 9, 11, 13, 14)
UNION
SELECT u.NAME, o.NAME, 'JAVA SOURCE', s.joxftlno, s.joxftsrc
  FROM SYS.obj$ o, x$joxfs s, SYS.user$ u
 WHERE o.obj# = s.joxftobn
   AND o.owner# = u.user#
   AND o.type# = 28;

Do you see the problem?

It’s the UNION. Apparently, Oracle changed the view in version 8.1.6 to include the Java source code. I don’t know what they were thinking when using a UNION and not a UNION ALL. That’s one of the beginner mistakes!

Our database has a couple of million lines of PL/SQL. Using the UNION each query on this view first forces a sort to get rid of any duplicate rows in the two sub-selects. But, they can never return duplicate rows. The second view always returns a type of ‘JAVA SOURCE’ whereas the other view can and will never return this type. So, there is no reason to use a UNION.

I was a bit surprised that Oracle made this mistake in the first case. I was even more surprised that they never fixed it. I opened a TAR at MetaLink and the support engineer found two (non-published) bugs for this. They indicate it was fixed in version 9.2.0.3 and was never back ported to 8.1.7.

Oracle does indicate you can safely change the view definition of USER_SOURCE, ALL_SOURCE and DBA_SOURCE to use a UNION ALL in stead of a UNION. Perhaps this is a useful tip to make this change on your pre-9.2.0.3 databases.

ADF Toystore on OTN

The long awaited ADF version of Steve Muench’s toystore demo is available on OTN! The previous (non-ADF) version of the toystore demo was a great way to get to know JDeveloper and J2EE development. It is a tutorial in which some real-world problems are tackled. This is so much more valuable then most tutorials that only show the very basics. Once you start developing your own applications, you’ll notice that it is not that simple as the tutorial made you believe.

I’m looking forward to go through the ADF Toystore demo step-by-step.

Oracle.com reinstalled?

It looks like Oracle is reinstalling their Application Server Click on the image for a full screen snapshot.

ADF-JHeadstart workshop next week

I will be attending the ADF-JHeadstart workshop next week at Oracle in the Netherlands. It’s the same workshop that Oracle ran internally for their own EMEA employees. Steve Muench was very enthousiastic about the workshop in four postings on his own blog: day one, day two, day three and day four.

I’m hoping this workshop has some more real-world (more challenging) examples on BC4J and Struts than the normal instructor led classes from Oracle University. They tend to be a bit basic. Also, the workshop should cover UIX and JHeadstart. Two topics that are almost completely ignored in the normal classes from Oracle University.

As former Forms/Designer users, we’re planning on using JHeadstart/UIX/Struts/BC4J as our technology stack. This should make this workshop be spot on!

There is more info on the workshop at http://www.oracle.com/global/nl/education/specials/sp278.htm