OraTransplant

Licensing policy change for multi-core processors

The IT-Eye weblog was just added to Orablogs. Unfortunately that was to late to pick up an entry about Oracle’s change of policy for multi-core processors. They refer to an interesting article in eWeek about this change.

I think it was about time that Oracle clarified/changed their policy on multi-core processors. The only thing that remains is a change in policy for grid computing. On the one hand Oracle is promoting use of multiple relatively small machines in a grid but on the other hand they keep charging by the processor. That forces us to minize the number of CPU’s and thus to not use grid.

Data on Windows clipboard slows down Forms

Ever since the migration from client/server forms 6i to web-deployed Forms 10g, some users have been complaining about performance. As it turns out this is related to having (large amounts of) data on the Windows clipboard.

Some users were complaining about bad performance when navigating to other fields. For testing I opened up a form with quite a number of fields and started navigating through them using the TAB key. I noticed that the delay was only on navigating to text items. Navigating to pop lists, checkboxes, radio buttons, push buttons and other GUI elements was not an issue.

Some users tend to suffer from this performance decrease more than others. Also, the problem magically disappeared sometimes. Further testing revealed it is due to having large amounts of data on your Windows clipboard. Some of our users are heavily involved in testing and writing manuals. They tend to make quite a number of screenshots using Print Screen. This places a screenshot on the Windows clipboard which can be quite a number of megabytes depending on resolution and color depth.

You can easily reproduce the issue yourself. Open your Windows task manager. On the processes tab, add the PF Delta column (View > Select Columns) to show the number of page faults (swapping). Now open your form and tab through the fields. In the task manager watch your IExplore.exe (or other browser) process. Notice the low number of page faults on navigating. In my case, this was typically below 10.

Now open a text editor and create a large amount (2-4 megabytes) of text. Select all text and copy it to the clipboard. Go back to your form and tab through the fields. When using 2 megabytes of text the PF Delta shot up to 2000 in my case. The larger the amount of data, the higher the paging/swapping will be. 4Mb of data raised the PF Delta to 4000. 8Mb of data raises the PF Delta to 6000. Also notice the high increase in Memory Usage for your process. After this last test it increased to a whopping 140Megabytes! Testing with 16Mb of data raises the PF Delta to 35000. Copying a small amount of data to the clipboard does remove the noticeable delay and reduces the PF Delta to normal figures. Unfortunately the memory usage of your webforms session does remain extremely high (more then 100Megabytes).

There is a limit to all this. Eventually, JInitiator might raise an OutOfMemory error which you can see in the JInitiator Console. When testing with Sun JPI your entire session would eventually crash with a FRM-92050.

Searching through MetaLink made me found bug 3130709. This describes exactly my problem, but is supposed to be solved in Forms 6.0.8.25 in February 2004. I opened an iTAR and that resulted in the same Oracle bug number and a link to Java bug 4790833. That Sun bug was fixed in Java 1.5, but my problem still reproduces in Java v1.5.0_03. If you read the bug text at Sun, you find a reference to bug 4287795 which goes on about having to get the entire content of the clipboard to just know if something is on the clipboard at all.

Today, the support engineer told me that the Forms applet is checking the status of the clipboard to determine whether the Paste entry in the Edit menu should be enabled or disabled. Apparently it is a Sun Java bug that the content of the clipboard is retrieved when just asking for the status of the clipboard. However, Oracle should have worked around that with the fix for bug 3130709. He found a reference to a parameter disableValidateClipboard in the bug description. He suggested adding a parameter disableValidateClipboard=true to my formsweb.cfg but that did not help. Oracle will investigate internally what happened to the bug(fix) and whether it dropped out of 9i/10g. I’ll update this blog entry with any news.

PS. If you found this blog entry because your having performance issues with webforms, you might also want to have a look at the DNS issues we had.

Update 26-jul-2005: Oracle just acknowledged that the fix of Forms 6.0.8.25 somehow was not included in the 10g version. We’ve asked for a one-off which means that development will try to provide a fix for this issue separately for the 10g version. Oracle promised to keep me informed. If they do, I will again update this blog entry.

Update 5-aug-2005: Oracle has openend a new bug 4520121 against Forms 9.0.4.1.0 to also include the fix in Forms 10g. The bug can be monitored through MetaLink. It appears that the final fix has slipped to Forms v11.x, but Oracle will release one off patches for 9.0.4.2.0 and 10.1.2.0.2 per our request.

Update 10-oct-2005: Oracle just released a one-off patch 4599915 for Forms 9.0.4.2.0 that fixes the issue.

Update 29-nov-2005: Oracle just released a one-off patch 4520121 for Forms 10.1.2.0.2 that fixes the issue.

Reset SSO password and send by email

We’re finishing up deploying Oracle Single Sign On. Our version of SSO (v9.0.4.1.0) comes with default pages to let a user reset his password when he forgot about it. These pages ask the user for a “secret question”. The user is allowed to change his password if he gives the correct answer to this question.

I want a page which will just generate a new random password for a user and sends it by email to the registered address. Because of our SSO policies, the user has to change this password again on the first login. Secure enough for me.

The documentation states that Oracle does not feel it is secure to send a new password by email and that’s why the chose for the option with the secret question. In my opinion having a week secret question is even more risky then sending a password by email. Who doesn’t remember the hack on Paris Hilton’s T-Mobile account? Besides that, it is quite a hassle to have all your users set up a secret question in the first place. We converted some 2000 users from other systems to SSO and how do I get them to setup a secret question?

I also asked Oracle support and there is no default option in Oracle Application Server to implement a reset-and-email-password page. Searching a bit more on the OTN forums resulted in a bit of code that inspired me to writing the page myself.

I changed the code from OTN to ask for a username and email address. It first validates if that combination exists in the Oracle Internet Directory. If so, the password is changed to a random generated password and this new password is sent by email to the user.

If you look at the code, you see I also used Context to look up environment settings. This is because I do not know the password of orcladmin for the production OID. Besides that, I do not want to hard code any username/password in this JSP. That’s why I’m using environment settings. You set these in the web.xml and the administrator of the application server can change these values after deployment. That makes it possible to use the same EAR file on both development, testing and production application servers with just different configurations.

The JSP is below. You would probably have to add things for a nicer form, error handling, etc. The code here is just and example and should be enough to get you started.

< %@ page contentType="text/html;charset=windows-1252"%>
< %@ page import="java.util.*,java.io.*, javax.naming.*,
                  javax.naming.directory.*"%>

< %
  // **********************************************************
  // forums.oracle.com/forums/thread.jsp?forum=47&thread=293082
  // for the original code
  // **********************************************************

  String username = new String();
  String email    = new String();

  // only process if the form is submitted
  if (request.getParameter("username") != null &&
      request.getParameter("email") != null) {
    // get settings from environment
    Context initial = new InitialContext();
    Context env = (Context) initial.lookup("java:comp/env");

    String searchBase  = (String) env.lookup("oidSearchBase");
    String oidAdminDN  = (String) env.lookup("oidAdminDN");
    String oidAdminPwd = (String) env.lookup("oidAdminPwd");
    String ldapServer  = (String) env.lookup("LDAPServer");
    String ldapPort    = (String) env.lookup("LDAPPort");
    String smtpHost    = (String) env.lookup("smtpHost");
    String smtpSender  = (String) env.lookup("smtpSender");
    String smtpSubject = (String) env.lookup("smtpSubject");
    String smtpContent = (String) env.lookup("smtpContent");

    // get parameters from HTTP request
    username=request.getParameter("username");
    email   =request.getParameter("email");
    String userDN = "cn=" + username + ", " + searchBase;

    // generate new random password
    StringBuffer newPassword= new StringBuffer("");
    Random rnd = new Random();
    final String alphabet =
                 "0123456789abcdefghijklmnopqrstuvwxyz";
    for (int i=0; i<10; i++) {
      newPassword.append(alphabet.charAt(
        rnd.nextInt(alphabet.length())));
    }
    // add two digits to be sure to pass password policy
    newPassword.append(alphabet.charAt(rnd.nextInt(10)));
    newPassword.append(alphabet.charAt(rnd.nextInt(10)));

    // setup OID connection
    DirContext ctx = null;
    Properties env = new Properties();
    env.put(Context.INITIAL_CONTEXT_FACTORY,
            "com.sun.jndi.ldap.LdapCtxFactory");
    env.put(Context.PROVIDER_URL,
            "ldap://" + ldapServer + ":" + ldapPort + "/");
    env.put(Context.SECURITY_PRINCIPAL, oidAdminDN);
    env.put(Context.SECURITY_CREDENTIALS, oidAdminPwd);
    try {
      ctx = new InitialDirContext(env);
    }
    catch (NamingException e) {
      response.sendError(500, "LDAP logon error");
    }

    // retrieve email address of user
    String ldapEmail = null;
    try {
      BasicAttributes currentAttr =
        (BasicAttributes) ctx.getAttributes(userDN);
      ldapEmail = (String) currentAttr.get("mail").get();
    }
    catch (NamingException e) {
      // user not found in OID
      response.sendError(500, "User not found");
    }

    if (email!=null && email.equalsIgnoreCase(ldapEmail)) {
      // email addresses match, reset password
      BasicAttributes newAttr = new BasicAttributes();
      // newAttr.put("userpassword", newPassword.toString());
      try {
        ctx.modifyAttributes(userDN,
          DirContext.REPLACE_ATTRIBUTE, newAttr);
        // send email to the user
        sendMail.setHost(smtpHost);
        sendMail.setSender(smtpSender);
        sendMail.setRecipient(ldapEmail);
        sendMail.setSubject(smtpSubject);
        sendMail.setContent(smtpContent.replaceAll("%password%"
          , newPassword.toString()).replaceAll("\\\\n","\n"));
        sendMail.sendMessage(pageContext);
      }
      catch (NamingException e) {
        response.sendError(500, "Error changing password");
      }
    } else {
      // supplied email address does not match the one in OID
      response.sendError(500, "User not found");
    }

  }
%>

  

  
  
Forgot Password



Username



        



  

Using self-signed SSL certificates with JInitiator

As you might know from previous blog entries we are using Sun JPI for running Oracle Forms. We are having a performance issue and I would like to test if it also exists using Oracle’s own JInitiator. I’ve set up a new configuration section in my formsweb.cfg to start the application with JInitiator in stead of Sun JPI.

However, we use SSL encryption on all our webservers including Forms. For all non-production servers these certificates are self-signed. Sun JPI raises a nice warning and asks the user what to do, but JInitiator just refuses to setup the connection and fails. Below are the steps to include a self-signed certificate in the store of JInitiator. Even better, I’ve included our self-signed root-certificate so that all development/test servers are accepted by JInitiator.

First of all, try to start the application. JInitiator v1.3.1.18 will start and the applet will fail with 'java.lang.ClassNotFoundException: oracle.forms.engine.Main'. Open the JInitiator console (right-mouse click on the JInitiator icon in the system tray) and it will show 'java.io.IOException: javax.net.ssl.SSLException: SSL handshake failed: X509CertChainInvalidErr'

Double-click on the yellow lock in the status bar of Internet Explorer. Navigate to the ‘Certification Path’ tab and see if the self-signed certificate has a root certificate. If that’s the case, select the root certificate and press the ‘View Certificate’ button. If there is no root certificate, just continue with the self-signed cerficate.

Go to the ‘Details’ tab and press the ‘Copy to File’ button. Save the certificate as Base-64 encoded. Open the base-64 encoded certificate in a text-editor. Copy the entire contents (including BEGIN CERTIFICATE and END CERTIFICATE lines).

Now open the certdb.txt file in the lib/security directory of your Oracle JInitiator (in my case C:\Program Files\Oracle\JInitiator 1.3.1.18\lib\security\certdb.txt). Add the copied certificate to this file and prepend it with some comment lines (beginning with #) to explain what the certificate is.

Close any open web browsers to close the associated JVM’s and try to start the application again. It should work.

Update 5-sep-2005: Also see http://www.oratransplant.nl/2005/09/05/importing-self-signed-certificates-in-sun-jpi/ on how to import your self-signed certificate in Sun’s JPI/JRE.

Applying best practices from 3GL development

I’ve been looking at the tools used for 3GL development with some envy. There are tons of very productive tools available for Java, C++ and other 3GL developers. Wouldn’t it be great if we can use some of those for our Oracle Forms/Reports/Database development.

I’m planning on implementing some of those for our Oracle development in the rest of 2005 (and probably 2006). Hopefully this also closes the gap between our Oracle and Java developers. This is nice way for the Oracle developers to get to know some best practices from the Java development. Hopefully this makes a transition to Java a bit easier. I’ll keep reporting on this blog on my findings and perhaps publish a number of papers on how we implemented and integrated some tools.

The things I’m hoping to investigate (and perhaps implement) are:

Issue management
We could track bugs, features, tasks and improvements in a more professional way than we are doing now. This would also enable us to implement some workflow. I’m currently thinking about implementing JIRA.

Version control
Currently we do have some version control in place, but it is not as professional as I would like it to be. I will be evaluating both CVS and Subversion and we’ll probably implement one of these two. I tend to like Subversion a bit more but JDeveloper doesn’t support it yet.

Automatic builds
I would like to create automated builds as much as possible but at least daily. Refresh a database and an application server and apply all changes checked in to the version control system. During this automated process lots of checks and other tasks can be performed and monitored (also see other points). We might build this based on Ant and CruiseControl, since we’re already using these for our Java development.

Automatic tests
During the automatic builds I would like to perform as much automated tests as possible. This reduces the load on testers and can warn a developer early on that his change broke some tests. I’m looking at DBUnit and utPLSQL first.

Powerful editors
Currently we develop our PL/SQL code in Designer. The editor in Designer does offer color coding, but is not as powerful as other editors. Perhaps we should make JDeveloper or TOAD our primary editor. This would also enable us to offer all sorts of productivity enhancements to developers by using (custom build) plugins, templates and code snippets.

Formatting
If we would switch to another editor (JDeveloper or TOAD) I would also like to investigate code formatters. Using a uniform formatting of code makes it easier for developers to look at each others code. Also I found during Java development that Jalopy can save you a lot of time when writing code. It’s so nice to not bother about things like indentation and just run your code formatter to take care of it.

Quality Assurance
We have a lot of standards and guidelines for development. Wouldn’t it be nice if most of them could automatically be checked/enforced in your editor and during automatic builds/checks?

Documentation
If you’ve ever programmed Java, you must know JavaDoc. It’s a predefined way of adding documentation to every class and method and can generate quite useful HTML documentation for all your code automatically. Something similar exists for PL/SQL: PLDoc. It’s not much work for a developer to add comments using a fixed format but the generated HTML documentation can be a real treat. The editor should be able to help you here.

Logging
I want to have a look at the Log4PlSql framework. It’s designed after the Apache Log4J framework as being used by Java developers. It offers a way to add logging statements to your code which you can just leave in there for your production code. You can enable it at runtime and write the logging info to different outputs (database table, file, standard output, etc). No more hassling with adding dbms_output all the time and removing it again.

If you have any experience with these tools or methods and have some handy tips, please leave them as comment.

Background of textitems black or pink

We’ve been strugling with a Forms issue for quite a while now. We’ve converted about 300 forms from version 6i to 10g and found out that some of them use a very dark gray (almost black) or pink color as background for some textitems. It occurs for textitems that have no background color set. This shows as “Unspecified” in Forms Builder. When you initially start the Form all looks ok, but after opening and closing some other forms in the application the items suddenly change to this darkgray or pink color.

Quite a number of our Forms originated in SQL*Forms 3.0 and were converted to 4.5, 6i and now to 10g. As it turns out these have a different color pallete than the ones that were created in 6i straight away. It looks like Oracle changed their default color palette along the way. The old forms have exactly the same palette as is now available in the CDE1.PAL file still in the Developer Suite distribution.

I filed a TAR at MetaLink and that resulted in a new bug (4383847). As it turns out, Forms deployed over the web cannot cope with different color palettes in the same session. Oracle says it has something to do with Java restriction. The answer is that you should not use custom palettes. I tried to argue that my palettes aren’t custom but just the default ones of 10 years ago, but that didn’t help.

The steps to reproduce the issue are:

• Start Forms Builder v9.0.4.1.0
• Edit > Preferences
• Set Color Mode to Editable
• Restart Forms Builder
• Save the new form (module1) under the name DEFAULT.FMB
• In the Forms Object Navigator create a new Canvas
• Also from the navigator, create a new Block (do not use wizard)
• Set block property “Database Data Block” to No
• Navigate to the Items node and press the green plus three times
• Select all three new items and set the Canvas property to the created canvas
• Set the Item Type of the second item to “Display Item”
• Set the Item Type of the third item to “Push button”
• Add a When-Button-Pressed trigger to the Push Button with the code call_form('cde1');
• Open the layout editor
• Shift the three items around to fix the overlap
• Save the Form
• Copy the DEFAULT.FMB to CDE1.FMB
• Open the CDE1.FMB in Forms Builder
• Change the Name property of the form to CDE1
• Open the layout editor for this Form
• Select Edit > Import > Color Palette
• Browse to ORACLE_HOME\tools\common90 and import CDE1.PAL
• Change the When-Button-Pressed trigger to call_form('default');
• Save the Form
• Compile both forms using Forms Builder
• Start DEFAULT from Forms Builder
• Notice the correct colors for the text item and display item
• Press the button to start the CDE1 form
• Notice the CDE1 form to open with pink items
• Press the button to start the DEFAULT form
• Notice the DEFAULT form now also opens with the pink items
• Close your webbrowser and go back to Forms Builder
• Now start the CDE1 form first
• Again notice that the first form shows with the correct colors
• Press the button to start the DEFAULT form
• Notice the DEFAULT form opens with very dark gray (almost black) items
• Press the button to start the CDE1 form
• Notice the CDE1 form now also opens with the dark gray items

I already mentioned this problem in a previous blog posting about our Forms conversion. That raised some questions with people, so I decided to document it in this separate blog entry. Also in my quest for a solution to this problem I found several forum questions with the same behaviour of items changing to dark gray, black or pink. Hopefully this blog entry can be helpful to these people.

The only solution provided by Oracle was to manually import the default palette into each and every form. To us, that was not a workable solution. We’re planning on running Forms 6i client/server and WebForms 10g in parallel for a couple of months. Developers continue to work in 6i only and the Forms are converted to 10g when deploying. This means our conversion process has to be repeatable and manually importing a palette is not an option. Unfortunately the JDAPI does not support changing the palette, so we couldn’t automate it that way. The solution we choose was to use the JDAPI to set the background color property of all items where it is not set. I’m setting the background color to “r100g100b100″. In effect, this is just white but with a different name. This makes it possible to recognize the items that were changed by the JDAPI. Whenever Oracle fixes the palette-bug, I might decide to change these items back to Unspecified to get really transparent items. Now I’m having white items on a non-white canvas, which isn’t what the users ask for.

If you’re struggling with white background of readonly items (both textitems without modification rights and display items), please read note 305563.1 on MetaLink. It describes a applet parameter readOnlyBackground. You can set this to TRUE to force all read-only items to use the canvas color as background. A more usefull setting is AUTO which uses the canvas color as background for readonly items that do not have a specific background set. If a readonly item does have a background color set (e.g. a current record visual attribute) then that color is used when using AUTO.

Update 29-jul-2005: Also see this blog entry of Duncan Mills which describes changing the color palette of Forms and in his introduction mentions the issue with black-on-black items.

Database 10gR2 up for download

We all knew it was coming, but Oracle just release the next version of their flagship: Database 10g Release 2 (v10.2.0.1.0). For now its just available for Linux, but I guess they will be adding other major platforms very soon.

I couldn’t find any certification information about combinations with the different AppServers yet.

New weblog of IT-Eye

As you might have noticed, Andrej Koelewijn of IT-Eye has been working at Eurotransplant for about a year. His main task was leading a small team in creating a disconnected ADF Swing client to register donor-information and submit it to a web service as soon as a (mobile) network connection is available.

Andrej has had his own weblog for over 2 years now. Last week his company, IT-Eye, started a weblog of their own. I guess Andrej will be one of the main authors, at least for the beginning. I hope his colleagues will take up the challenge and help him in building a successful weblog.

I think it’s great to have a company wide weblog. Just look at the weblog of Amis and the number of interesting articles on there.

Andrej and IT-Eye: Good luck to you!

Generating N rows from dual

Sometimes in a query you just need a list of numbers. With a neat trick you can select these from DUAL:

select * from (
    select level lvl
    from dual
    connect by level <= N
)

I found this useful tip on Eddie Awad’s blog. He left a comment on mine and that’s how I found him. He’s an Oracle application developer and runs quite an interesting blog. Shouldn’t he be on OraBlogs?

PS. Orablogs still seems to be having DNS problems and can be reached by http://83.170.75.145/orablogs/

Upgraded to WordPress v1.5.1.3 due to security issue

I’ve just upgraded my WordPress installation to version 1.5.1.3. A security issue was discovered and the makers of WordPress urge all users to upgrade to v1.5.1.3.