APEX-O-MATIC – In a Blender with APEX and Web Services

Written by klee on November 1st, 2010

That’s the title of my accepted presentation at RMOUG 2011. I thought it was fun to come up with that one! Hope to see you there!

RMOUG Training Days 2011, February 15-17, in Denver, Colorado

See paper on Downloads page!


Converting .csv file to .hda for importing in Content Server

Written by klee on October 29th, 2010

One of my peers was talking to me about the pain he was having getting data from another system into UCM. I have done a lot of Java programming, so my thinking was to help out with a java solution. It works. Could this be done differently. Of course! I just picked one way that wouldn’t take up a lot of time. I did including a couple of third party libraries though.

Commons CLI


In the first iteration, I was given a sample csv file, the expected result hda file, and a quick mapping of the metadata fields that were defined in the csv file. The initial idea was that each csv might have a different structure, so having a map file that listed which metadata fields were in the file would solve this. I said, “uh huh.” I left to code this. I put on my headphones and had this working in less than an hour.

I’m laughing because I didn’t give the code to my peer for another three or four hours. Seems simple. Just compile the code, and email a class, and two jar files. Should be simple. Not! Well, maybe it is, but I couldn’t get this to run outside of my development environment (A directory with several sub-directories for src, classes, libs, build, etc.). I kept getting a class not found error. I felt like the dumbest smart person for a while, which can happen a lot, just doesn’t last long in most cases. Well here, the feeling lasted a while. Honestly, I still don’t know what I was doing wrong. I already had a compiled class that worked fine in my ANT environment defined in the build.xml file. I finally created a jar file for the class, and added information in the manifest to point to the main class, and added the two jar files in the classpath. Now it works.

So on with the story.

I send over the three jar files and a quick command line call. With the Commons CLI, I setup the call to include three parameters, -csv, -src, and -out. I was sitting at my desk feeling great for about eight seconds, which was the time it took for my peer to walk over to my desk. Okay, maybe a minute, as he had to run the program before coming over to my desk.

The conversation kind of went like this. “You forgot to set this up to allow default values to be added. Also, there needs to be a way to add another metadata field that actually points to data in the csv file.”

Welcome to the life of a programmer.

On to the next iteration. I added the changes and the code works fine. Not exactly what was asked for, but it gets the job done. There may be another interation or two or three… But the bulk of the code is stable. The file is on the Downloads page. It is only about 130 lines of code, and most of that was for setting up for specific parameters in a specific format. Always those requirements – everything is in the details!

If anyone figures out or knows how to make this work with just the third party jars and the class file, please let me know. Must be something obvious!

You will need to download the commons CLI and opencsv libraries, and add these two jar files. Happy coding!


JDeveloper 11g and Windows 7 64 bit – and a few rants

Written by klee on October 7th, 2010

Now I have three development environments: an Intel Mac mini, Fedora Linux on my Dell 630 laptop, and I just installed Windows 7 Professional 64bit on my desktop. I thought it would be fun to re-install all of my applications. Actually I have an old Foxpro application that I need to spend some time on, and decided to setup an actual windows machine. I’ve been using a VM for years. There are other reasons, but suffice it to say, I now have development environments setup using three different operating systems.

The biggest surprise I had was JDeveloper. It doesn’t work with a 64 bit JVM in Windows. Crazy, Insane, I say – but it is true! There is a post about it here. I hear complaints about Linux, but I’ve been running JDeveloper under a 64 bit JVM since I first installed 11g without a problem and love the speed.

I am still in shock, something really simple that Linux just does, and Windows can’t.

So… why not too many posts lately? I have been spending a lot of time converting an Oracle Portal website to use Oracle UCM with Site Studio. The original plan was to use WCI, but the price tag was too high. It has been quite an experience, to say the least. I wish I could say that I like developing with IDOCScript, but really can’t. I remember developing with Vignette, which at the time used tcl with a lot of controversy. Vignette seemed to throw in the towel and offered both a JSP and ASP option, but I’m not sure if that really helped. (back to UCM) I’m torn with IDOCScript. It is another language to work with. Why not use something more mainstream? Behind the scenes, all the code becomes Java, so why all the cloak and dagger?

In the end, I am getting paid to write lines of code. And with that said, I will write in any language when I am getting paid. Another day, another language.


Oracle Virtualbox appliance for Database App Development

Written by klee on September 27th, 2010

I use a lot of software, and lately almost all of it has been from Oracle. I had written a note to Todd Trichler of Oracle after last year’s Rocky Mountain Oracle Users Group Training Days, as almost every Oracle presenter used a similar image for presenting material, and I wanted to get a copy of the image. Well, it seems that the image has arrived, and you can download it here.


Keeping track of tasks

Written by klee on July 18th, 2010

At work, I have been using the tool manymoon for tracking todo lists that is well integrated with Google Calendar. It doesn’t matter if you are using gmail or google apps as I am, as the product uses openid to your google account to login. I don’t own stock and actually think the creators could have come up with a better name. I am simply a very satisfied customer!


JDev 10.1.2 Portlet that consumes RSS Feed

Written by klee on May 4th, 2010

Today, I created several servlets that generate RSS feeds from dynamic data in tables. In order to view the data in another page, it made sense to use a simple portlet. When creating the definition, just make sure that you allow for a single parameter, the feed URL, when defining the provider.

This seems to work fairly well, and I have already created several versions that display the data differently. When I get time, I hope to update that code using CSS to format the data, but that will wait for another day.

<%@page contentType="text/html; charset=UTF-8"%>
<%@page import="oracle.portal.provider.v2.ParameterDefinition"%>
<%@page import="oracle.portal.provider.v2.render.PortletRenderRequest"%>
<%@page import="oracle.portal.provider.v2.http.HttpCommonConstants"%>

  PortletRenderRequest pReq = (PortletRenderRequest) request.getAttribute(HttpCommonConstants.PORTLET_RENDER_REQUEST);
  ParameterDefinition params[] = pReq.getPortletDefinition().getInputParameters();
  String sURL = "";
  try {
    sURL = pReq.getParameter("p_url");
    if (sURL == null || sURL.equals("")) {
      System.out.println("sURL could not be determined");
      sURL = "http://www.weather.com/weather/today/Castle+Rock+CO+80104?cm_pla=city_page&cm_ite=cc&site=city_page&cm_ven=LWO&cm_cat=rss&par=LWO_rss";
  } catch (Exception e) {
      System.out.println("Error:" + e.getMessage());


You may be wondering where the java code is that generates the actual feed. I can post that later, but for those that want this now, here is a link from the site that I based my code from.

Creating an RSS Feed


Why I use Linux as a desktop

Written by klee on April 22nd, 2010

Penguins are cool!

I have two Macs, a high-end PC running Linux, my work laptop running Linux, three PCs running Windows XP, and one more laptop also running XP in my house. I have several PCs that are mostly used for parts. An upper end PC running Linux that is my main server that is my file server on the host level, and runs a web server, mail server, database server, test server all as virtual machines. Lastly, I have a left over PC that I am using as a special project database server for APEX applications that is also running Linux.

I have told the kids that as each of their PCs die, they will be replaced by a Mac Mini. The one that I currently have is about five years old, and the only thing I do is turn it off before leaving the house on vacations. I don’t think it has ever crashed. Ever!

So why not use a Mac as my primary platform. Cost for one. I can get much more horsepower for a lot less money with Linux. This year for my Christmas present to myself I built my own dream machine. I already had an LCD monitor, keyboard and mouse and was limited to about $500 dollars. It is amazing what you can purchase at Newegg! I was able to get a 3.2 GHz Quad core processor, 8 GB of high speed ram, and a fast SATA HD. I also replaced the stock fan, just in-case I ever decide to overclock the CPU to 4 GHz or higher. Sometimes just knowing you can is just as good as actually doing.

I still haven’t answered the question. I spend most of my time using ant to build java projects, JDeveloper as an IDE, and SQL Developer working with PL/SQL. Linux is simply so much faster, especially starting up SQL Developer or JDeveloper. Even on my work laptop (which is painfully slow to begin with), I can launch SQL Developer and have the IDE ready to go in 15 seconds, where my colleagues can wait more than a minute, sometimes two – just to see the IDE. I don’t even want to talk about my PPC Mac. Apple doesn’t have a version of Java that will run any of the current version of JDeveloper or SQL Server. Too bad, so sad!

If speed was the only issue, I probably would be running just about everything from the command line as well, but I do have my limits. I love the interface on the Mac. Plain and simple, it is just beautiful. A work of art! I just wish the delete key worked like the backspace key. Delete just doesn’t work right and is really my only complaint. If the Mac was faster and cheaper, I would replace all of my PC’s with them.

Windows. Well, besides re-imaging each PC yearly due to a variety of reasons, blue screens of death when playing games in my free time (ha ha), dealing with viruses that caused another round of re-imaging, etc… I like it. My dream includes buying Windows as a Windows manager that runs in X-Windows like Gnome or KDE. Yea, I would buy start buying Windows again if that happens.

I really don’t have any love for the Window managers in Linux. Just like I really don’t have any particular preference for a specific distribution. Typically, I run Fedora on my Desktops, and Ubuntu LTS for all of my servers and virtual machine servers. I have run Gentoo, and think that is a really great distribution. It just is a lot of work to setup, or at least I think it is. Don’t get upset, one of my favorite coffee mugs is my Gentoo Linux mug, and I don’t have any other Linux mugs.

And what’s wrong with Linux. I can’t run notepad++ or textpad natively. I really dislike vi and emacs even more so. Windows has the best text editors of all the platforms. That will hopefully be fixed one day! Until then, I’ll happily type away using gedit. And yes, I still use Windows running in VMWare for Visio and Microsoft Project. As long as these products run under XP, I will keep waiting for Windows as a Window Manager.


How to validate against a system table when checking in a document

Written by klee on April 22nd, 2010

One of the problems we continue to face is comboboxes that contain a lot of data. 40,000 rows of data. Just having a few of these on a form can really make the system slow enough to be unusable. One solution is to just validate the data on submit. The key is to not loose the data if there are any problems.

Using examples from the howtobundle of examples from Oracle and also using Bex’s book – Custom Java Components chapter, I have put together a quick example. First create a component in the ComponentWizard. I named mine EmployeeCheckinValidation. Once you have this created (Follow the directions in Bex’s book if you are having trouble) create a project in JDeveloper. For this to work, you have to use the same JDK that is used in your UCM instance. In my case, I created a Samba mount point, and was able to point to the correct JDK using this method. You also have to add UCM’s server.jar file to your project’s jar library. To make things easier, I also set the projects source directory and output directories to directories named src and classes in the component – in my example ucm/server/cusom/EmployeeCheckinValidation/src and ucm/server/cusom/EmployeeCheckinValidation/classes. The last change was to the hda file. I added the following line below the line containing the version:

Now when I rebuild the project, both the src and classes get updated automatically. Don’t forget to restart your server anytime you rebuild your file!

Here is the sample file that I have created. Basically, this will check that the title is not in the list of doctypes, which is one of the system tables. I’m sure there are other ways to accomplish this, but I thought I would share one way to do this. If you have ideas to make this better, please leave me a comment!

package testing;

import intradoc.common.ExecutionContext;
import intradoc.common.ServiceException;
import intradoc.common.SystemUtils;

import intradoc.data.DataBinder;
import intradoc.data.DataException;
import intradoc.data.DataResultSet;
import intradoc.data.ResultSet;
import intradoc.data.Workspace;

import intradoc.provider.Provider;
import intradoc.provider.Providers;

import intradoc.shared.FilterImplementor;

import intradoc.util.IdcMessage;

public class CheckinFilter implements FilterImplementor {
    public int doFilter(Workspace ws, DataBinder binder, ExecutionContext cxt) throws DataException, ServiceException {
        SystemUtils.trace("system", "Starting doFilter for EmployeeCheckinValidation");
        // Display the binder
        // System.out.println(binder);
        String dDocTitle = binder.getLocal("dDocTitle");
        SystemUtils.trace("system", "dDocTitle=" + dDocTitle);

        String SQL = "select count(*) counter from doctypes where upper(ddoctype) = '" + dDocTitle.trim().toUpperCase() + "'";
        String ResultSetName = "DDOCTYPECOUNT";
        SystemUtils.trace("system", "SQL=" + SQL);
        // Search database for ddoctitle in doctypes.ddoctype  Fail if
        // this this is true
        if (ws == null) {
            SystemUtils.trace("system", "ws is null, getting ws from call to getSystemWorkspace()");
            ws = getSystemWorkspace();
        String value = "";
        int ivalue=0;
        DataResultSet result = null;
        DataException error = null;
        SystemUtils.trace("system", "try-catch block to get ResultSet from SQL");
        try {
            ResultSet temp = ws.createResultSetSQL(SQL);
            result = new DataResultSet();
            binder.addResultSet(ResultSetName,result);  // Makes results avilable for other Java methods or IdocScript templates.
        } catch (DataException de) { 
            error = de;
        } finally {
            // ws.releaseConnection();  // do NOT uncomment this, unless you like broken code!

        try {
            // value = result.getStringValueByName("counter");
            value = result.getStringValue(0);
            SystemUtils.trace("system", "value(getStringValue(0)) = " + value);
            ivalue = Integer.parseInt(value);
            SystemUtils.trace("system", "ivalue=" + ivalue);
        } catch (NumberFormatException nfe) {
            SystemUtils.trace("system", "nfe.getMessage()=" + nfe.getMessage());

        if (ivalue > 0) {
            SystemUtils.trace("system", "Throw error since dDocTitle cannot be a dDocType");
            throw new ServiceException("dDocTitle cannot be a dDocType");
        if (error != null) {
            SystemUtils.trace("system", "Throw error since error condition exists.");
            throw error;

        SystemUtils.trace("system", "Ending doFilter for EmployeeCheckinValidation");
        return CONTINUE;
    public Workspace getSystemWorkspace() {
        Workspace workspace = null;
        Provider wsProvider = Providers.getProvider("SystemDatabase");
        if (wsProvider != null) {
            workspace = (Workspace)wsProvider.getProvider();
        return workspace;        


Written by klee on April 16th, 2010

I have enabled comments. Now don’t go crazy, as they are moderated – I have to read and approve them before they show up to everyone.

I hope to stick to a basic rule: Spam and inappropriate language goes to file 13, everything else stays.


Apache AXIS 2

Written by klee on April 16th, 2010

Two years ago (2009), a colleague of mine and I presented at RMOUG on using Oracle’s BPEL with Microsoft’s SQL Server to create Web services. Since we were using SQL Server instead of Oracle, we had to create xsd files to define our data. This took quite a bit of time, though not nearly as much time as dragging arrows to connect each of the items in the xsd file. In the end, we had a working deployment that realistically took about six months of duration and maybe 300 hours of work. Most of the problems were due to lack of available documentation at the time, and a difficult time getting Oracle’s BPEL product to play nice with SQL Server. The same service using an Oracle database could be done quite fast. Once you get the hang of it, we are talking minutes. Of course testing takes quite a bit more time.

Well, fast forward to the present. I was in a meeting and the topic of using Web services in Application Express was bantered about. After a few minutes, most of the team was looking at me. I am the one who experiments with products, and they were correct in assuming that I had the solution. Actually, there is a weather example available at Oracle. I was able to demo this example in a few minutes, and show how easy Web services are to integrate into APEX. The conversation then moved on to discussing whether the organization should use our purchased SOA suite to build a Web service library, but that is an entirely different story.

I found myself unable to stop thinking about the difficulty experienced using SQL Server and BPEL. I really couldn’t stop thinking about it. So, I did something about it. Several years ago, I had written a paper on Web services using AXIS. This had been several years ago and AXIS was now using a completely rewritten code base. What’s a guy to do? After downloading the Axis2 1.5.1 Release, I extracted and deployed the included war file to the Tomcat server running on my laptop. There is a samples directory, and one of the easiest ways to get started is the pojoguide example. Basically, there are four files that make up this sample. There is the service definition, src/META-INF/services.xml. In this file, you name the web service that is defined in the service directory.
In the data directory is a bean, src/pojo/data/Weather.java, which simply defines the output. For simple Web services in AXIS2, it seams easy to just create a class, and either return this class, or an array of the defined class as output. Believe me, this method is really easy to use! Okay, the rpcclient is more complicated. In my case, I replace this with a client directory, and created a Test.java file. I use this to test my Web service. After all, it is really just a Java class. In the end, I simply use a browser to test my Web service, displaying the results in my browser. The big deal is the data in the service directory. src/pojo/service/WeatherService.java contains the actual Web service. The class is the Web service, and all methods can be services that you call. Easy cheesy! For examples, I have just copied the pojoguide directory and simply renamed it, then updated the four classes described above. Of course, you have to update the build.xml file to point to the correct files and directories, but that isn’t too bad. Use ant to build the Web service to generate deployable file and test the service itself.

So back to my original point. After downloading the jtds SQL Server driver from SourceForge, I copied the jar file to the lib directory under axis2/WEB-INF and restarted Tomcat. I also had to add this to my build path in ant to get an error free compile. Now I could connect to my database. 15 Minutes later I had created my first AXIS2 Web service in years. Another ten minutes, and I was able to integrate this into APEX. Two great products working together. Technology is awesome!