Friday, September 30, 2005

Stupid Rollover Tricks

One neat little trick which I have found that users like is the ability to highlight a cell as you mouse over it. In HTML DB 1.6, the Highlight Row feature was introduced as a part of Themes. With just a couple of small changes to your report template, you can take things up a notch, and provide your users with the ability to change the cell color as they mouse around an HTML DB Report.

First of all, create a report based on any table or view. Be sure to use the "Standard" Report Template, not the one with "Alternating Color Rows." If you want to use an Alternating Color Row template, you’ll have to make the changes below for both Odd and Even rows.

Once you have created your report, edit the Report Template which is associated with it. If you edit the page that your report is on, you should be able to see a link to your Report Template under the Shared Components: Templates section of the Application Builder.

Scroll down to the Before Each Row section and replace what is there with the following:

<tr style="cursor: pointer">

We need to remove the #HIGHLIGHT_ROW# link in order for cell highlighting to work. Also, we’re going to change the mouse cursor from the standard “I-Bar” to the “pointer” as the user hovers over the report. No good reason for this, other than to illustrate how it can be done. A list of other valid mouse cursor options can be found here:

Next, let’s edit the Column Templates section. Replace what is in Column Template 1 with the following:

<td class="t12data"#ALIGNMENT#
onMouseover="'#0f0'" onMouseout="'#fff'">#COLUMN_VALUE#</td>

You can choose which color to use as a highlight color by replacing #0f0 with your color’s hex code.

Save your changes, and give it a try! You can view a working example here:

Thursday, September 29, 2005

A Better View of Your Trees

I’ve been playing around with Trees over the last couple of days. One of the requirements dictates that I render a Tree based on a sub-set of a larger hierarchy of data. Luckily, the data is already in the correct format (ID, PARENT_ID, DESCRIPTION) for an Oracle HTML DB Tree, so I don’t have to make any changes to the data structure.

Due to the large size of the table, it was much more efficient to create an HTML DB Collection based on a subset of the data, and then use that Collection in my Trees. Sounds simple enough, but you cannot easily create a Tree based on a Collection, as the view HTMLDB_COLLECTIONS does not show up in the Select List of Tables/Views that you can base a Tree on.

The solution to this was almost too simple: Create your own View for HTMLDB_COLLECTIONS. This killed two birds with one stone: I could now select my view from the Select List when I created a new Tree, and I could also rename the columns to reflect those of the base table, rather than be forced to use the C00X nomenclature that HTML DB Collections use.

Here’s the code that I used to create my view on the HTMLDB_COLLECTIONS table:

create or replace view my_collection_view
(collection_name, seq_id, id, name, address)
select collection_name, seq_id, c001, c002, c003
from htmldb_collections
where collection_name = 'MY_COLLECTION';

Now that I have my View, I had better create a Collection so that my View will have some data. I chose to create a named procedure to create the Collection. This way, all of my code is managed in the database, making it easier to edit and debug. I simply call this procedure when the user clicks on the corresponding button to create the Collection:

procedure create_my_collection
l_sql varchar2(32767);
l_collection_exists boolean;
l_sql := 'select id, name, address
from my_collection_view';
l_collection_exists :=
htmldb_collection.collection_exists(p_collection_name =>
if l_collection_exists = true then
htmldb_collection.delete_collection(p_collection_name =>
end if;
p_collection_name => 'MY_COLLECTION',
p_query => l_sql);

Of course you can edit the SQL statement which I stuff into l_sql in the above code to reflect user-specified or other parameters. In my actual application, that’s exactly what I do. Remember to use the v('ITEM_NAME') syntax to refer to HTML DB Item values while in a named PL/SQL unit.

There you have it – a much more “civilized” way to work with HTML DB Trees and Collections.

Thursday, September 22, 2005

Using an eBusiness Suite Responsibility in HTML DB

In a previous post, I mentioned that I was using membership in an eBusiness Suite Responsibility as the basis for an HTML DB Authorization Scheme. It’s really quite simple to set up, and even easier to manage.

Note: This assumes that you have already integrated HTML DB with the Oracle Single Sign On server. See this How To for detailed steps on how to do that.
  1. Create a new Responsibility in the eBusiness Suite

  2. Once created, note the RESPONSIBILITY_ID. You can obtain this via SQL*Plus or from the Help : Examine : Item menu in the eBusiness Suite.

  3. In HTML DB, create a new Authorization Scheme. Select Exists SQL Query as the Scheme Type, and enter the following as Expression 1:

    select 1 from apps.fnd_user_resp_groups ur, apps.fnd_user u
    where u.user_name = :APP_USER and u.user_id = ur.user_id
    and ur.responsibility_id = 50280

  4. Be sure to use your RESPONSIBILITY_ID in place of mine (50280).

  5. Associate this new Authorization Scheme with an Application by editing the Application Attributes and selecting your Authorization Scheme in the Authorization section.

That’s it! Now that your Authorization Scheme is associated with an eBusiness Suite Responsibility, only those users who have that Responsibility will be able to access your application!

As this project moves forward, I’ll be posting more ways to hook HTML DB up to the eBusiness Suite.

First Day at the New Job

It’s Wednesday night, and I’m already ½ way through the first week of the new job. If I had $1 for each time I said “we” – meaning Oracle – I would be able to take the rest of the week off! I guess it will take some time before I can refer to Oracle as “them”.

Being on the outside is very different. No longer can I pop into MetaLink to look up a TAR or Bug. I can’t access any of the internal sites which I used to do on a daily, if not more frequent basis. Call it separation anxiety, but I guess daily habits take some time to break. It was especially weird not being at OpenWorld this week. From what I heard, it was going well with a ton of interest in HTML DB, as well as BPEL & Fusion.

Speaking of OpenWorld, I have also been reading about Oracle Raptor on Brian Duff & Wilfred’s blog. I’ve known about this project for some time, but obviously couldn’t comment until it was made public. I think that it’s about time that Oracle entered into this market. I remember back when I first started at Oracle, I was surprised that I had to rely on TOAD for basic schema maintenance & development.

Anyways, back to the new job. So far, so good. Aside from getting lost in the parking garage, which is more akin to medieval catacombs than a logically built parking structure, things have been going well. The new team has been quite busy, so aside from a couple of hours of orientation on Monday, I’ve been left alone to explore the application which I will be working on.

Once I proved that I knew how not to blow up a server, I was given all of the passwords to the development instance so that I could install HTML DB. I must say that it went quite fast on the 6-way HP machine. The only issue that I encountered was that some process had placed a lock on DBMS_LOCK, and the install couldn’t proceed. Thanks to Joel from the HTML DB team, I was able to resolve this as per a MetaLink Note.

After creating a new entry in the file, I ran into the same issue that someone on the Forums was having. Luckily, something sparked a distant memory of encountering this issue on another instance of the eBusiness Suite where I installed HTML DB, and I was able to recall how I solved it quickly.

Once HTML DB was installed, I integrated it with their Single Sign On server, so that we could use the same usernames from the eBusiness Suite. I then added a Responsibility and created an HTML DB Authorization Scheme based on it: If your user has this Responsibility, then you can access my HTML DB application.

Tomorrow, I’m going to create my first PL/SQL package to generate some sort of exception report. I’ve already been given the green light to do this in HTML DB, so I’m looking forward to that.

On another note, I watched jetBlue flight 292 land safely with a disabled nose gear at LAX live tonight. The pilots & crew of that flight should be commended, as it sounds like they treated this incident as nothing more than an “inconvenience”, all the while keeping the passengers informed and reassured every step along the way. As usual, the media made utter fools of themselves, asking the droves of aviation experts the most ridiculous questions via phone. My two favorites: 1) What role is the fire department playing? 2) Is it easier to land in clear weather than in, say a thunderstorm? Priceless.

Monday, September 19, 2005

New Blog Location

Since I am no longer employed at Oracle, I am going to move my blog to Blogger, thus avoiding any confusion of what I "officially" said about HTML DB and what I "personally" say about it. Please update your bookmarks accordingly:

New Blog Home:
New Blog RSS:

Wednesday, September 14, 2005

Dude, I'm getting a Dell!

I’ll admit it – I’ve been spoiled for the last 9 years or so. Oracle has always provided its employees with a laptop or desktop (or both) system. Every couple years or so, you can typically convince your manager that you need an upgrade. Once your manager agreed and approved it, you could go to a web page, click on a picture of a laptop, and typically 2 or 3 days later, it would show up on your desk. Not bad!

My most recent machine was a Toshiba Tecra M2. I loved it – it had awesome battery life (could easily make the trip from Dulles to San Francisco), didn’t weigh a ton, and just worked well. Unfortunately, I couldn’t take it with me, and since my new job requires me to own a laptop, I had to purchase one ASAP.

As someone who designs software and applications, I was shocked to find that almost every major PC company’s Laptop/PC configuration site was way too complicated and cluttered! There are simply too many choices to make configuring a laptop an efficient process. Sure, there is a class or persona that likes this flexibility, but I would argue that most people – power users and beginners alike – do not fall in this category.

Starting out, I sorta knew what I wanted: a fast, capable machine with a 15” screen (not 17” – I’ve seen Tom Kyte’s machine, and that’s just too much to lug around!), a good amount of RAM and a fast hard drive. I also needed a reputable firm with a good service agreement, as a broken laptop means no billable hours for me!

Configuring a laptop was no easy task. There were way too many options to choose from. Most of the major sites tried to sell me loads of software I didn’t want, accessories that I’ve never heard of, and things that I just didn’t get. Here’s one of my favorite options:

XD is a Microsoft® Windows® XP Professional SP2 security feature that can help prevent a class of viruses (buffer overrun attacks). XD can only be enabled with current Intel® Pentium® D, Pentium® 4 and Intel® Celeron® D Processor combined with Windows® XP Professional SP2.
_X_ None
___ Execute Disable Bit for Microsoft® Windows® XPSP2 set to OFF.


Luckily for me, my brother – who also happens to work at Oracle - is an expert at PC hardware. After a few IMs and a couple of phone calls with him, he steered me towards either Dell or HP. He also introduced me to the world of is a site which lists all kinds of Internet coupons – from PCs to steaks. He was able to find me a 30% off coupon from Dell, which made the choice between Dell & HP a little easier, as I saved over $700!

I ended up going with the Inspiron 6000 with an Intel Pentium M 770. Heeding my brother’s advice one more time, I bought 2GB at for $250, saving another $100 or so. Sure, I have to install it, but that’s easy to do and well worth the $100. Since I was able to save over $800, I threw in a docking station. Why not?

The only downside is that it won’t ship for a week or so, leaving me laptop-less for the first week of my new job. Fortunately, my brother has an extra Toshiba that will get me by. It’s not the best machine, but I don’t see myself doing anything that technical in the first week anyways. At least I hope not!

Monday, September 12, 2005

Leaving Oracle

As many of you may know by now, I am no longer with Oracle. After almost 9 ½ years, Last Friday was my final day.

I’m going to be working on a contract with the US Marine Corps in Tysons Corner, VA – just a quick ride from where I live. There, I will be using HTML DB (naturally) to build a web interface to some Oracle eBusiness Suite applications. I’m anxious to get started, as I’ve long thought that using HTML DB as a one-off for eBusiness Suite applications is an excellent use for the tool which can save both time and money.

In addition to that, I am going to be doing some HTML DB & Oracle consulting on the side. I’m in the process of forming my own LLC – Sumner Technologies. While I admit that the website isn’t much to look at today, I plan on offering HTML DB consulting and training as my time permits.

So the focus of this blog is going to have to adjust accordingly. I’ll still be blogging on HTML DB related topics, but I am also going to blog on what it’s like to leave a huge company like Oracle and go off on your own.