In my previous post, I demonstrated how to use APEX's Tree region for a quick view of things like Bills of Material (BOMs) in the Oracle E-Business Suite. One problem is that the tree region uses an older version of the jsTree plugin, and so the nodes of the tree are limited to a simple link. Often, however, you're going to want to display more than one piece of information to the user–and by manually including the current pre-1.0 release of jsTree, you can. Here's my demo page.

This was a fairly straightforward implementation–download the latest jsTree plug-in, upload it to my server, and (eventually) write the PL/SQL you'll find on the demo page. Of course, that third step is where all the magic is. My first step was to create an HTML region and manually type in the code to create a generic tree to prove that the plug-in was working as expected. I can't stress the importance of this enough; if you don't do this, and something doesn't work, you could waste hours trying to debug something as simple as getting the path wrong. Once the generic tree was working, I had to decide between providing the tree's data as HTML (nested ul tags), a JSON object, or one of two XML variants. I believe the APEX tree region uses JSON, but I decided that the “flat” XML would be the easiest to program–any of the others would work best in recursive PL/SQL, which seemed unnecessary in this instance.

The real fun I had was keeping track of the parent for the current node. I could have built my driving query to return the parent ID in addition to all of the child information, but I didn't want to require the user to do that; the current tree region doesn't, and I'm planning on building a plug-in around this, so consistency was a consideration. So, all I was allowing myself to work with was the current node's information–and its level. And since hierarchical SQL returns the nodes in a predictable manner (you never jump down levels–from 1 to  4, for instance–though you may jump up–say, from 4 to 2), I could use a VARRAY to track where I'd been in the tree. As you can see from the PL/SQL, whenever we go down a level, we store the current node in case it turns out to be a parent; whenever we go up–which could be many more levels–we traverse back up the VARRAY correspondingly. Since VARRAYs are bounded, my implementation is limited to 1,000 levels, but that should really be far more than enough (who wants to walk down 1,000 levels in a tree?)

Incidentally, the code on the demo page is guaranteed to be the code for the demonstration region; I'm querying the source from the APEX dictionary and loading it into a page item, and then using substitution to add it to the HTML region. That's probably only useful in this sort of circumstance, but I still thought it was a nifty little trick.