Friday, December 30, 2005

I Always Feel Like Somebody's Watching Me...

It's not just a one-hit-wonder from the 80s; its true!

Sure, we've all seen where we and others live via Google Maps. But this time, Microsoft has stepped it up a notch with its new "Windows Live Local" site.

Now, as this is a Microsoft product, it doesn't always quite do what it says. For insatnce, it's not "Live", as my house is still occupied by the former owner as evidenced by their cars in the driveway, the salmon-colored siding (which was replaced in 2003), and the new construction at the end of the street had not started yet. However, the fact that WE can see this level of detail makes you wonder what "THEY" can see, and how "live" it really is...

The level of detail is stunning, even though the pictures are a couple years old. You can even rotate the image to view it from all 4 directions!

For example:
Enjoy & Have a Happy New Year!

Wednesday, December 28, 2005

One More Present to un-Rap...

OK, bad pun, but it's almost midnight here, and I'm tired...

Project Raptor made its OTN debut tonight, and a preview release is available for download.

I, for one, am definitely excited about this, mostly becuase the price is right: FREE!

And where are all of the HTML DB Users?

Taking a cue from Shay Shmeltzer's recent entry, I think it would be interesting to see just where all of the HTML DB users reside.

Point your browser here to chime in: http://www.frappr.com/oracleapplicationexpress

Wednesday, December 21, 2005

function HappyHolidays

Simply pass your religion & nationality to this function to get the politically correct greeting from me. :)


create or replace function HappyHolidays
(p_religion in varchar2, p_nationality in varchar2)
return varchar2
is
l_result varchar2(255);
begin
if p_religion like '%christianity%' then
l_result := 'Merry Christmas';
elsif p_religion like '%jewish%' then
l_result := 'Happy Hanukkah';
elsif p_religion like '%pagan%' then
l_result := 'Happy Winter Solstice';
else
l_result := 'Seasons Greetings';
end if;
if p_nationality != 'Chinese' then
l_result := l_result || ' and Happy New Year';
end if;
return l_result;
end HappyHolidays;
/

Sunday, December 18, 2005

Oracle MetaLink runs Oracle HTML DB

As John Scott pointed out, Oracle MetaLink is now running Oracle HTML DB.

This is a major step for Oracle HTML DB's credibility, as Oracle itself made the conscious decision to re-write one of its most mission critical customer-facing systems in Oracle HTML DB over any and all other technologies.

Sunday, December 11, 2005

New Oracle HTML DB Wiki

A new HTML DB-specific Wiki has been launched at http://wiki.shellprompt.net/bin/view/Apex/WebHome.

John Scott from Shellprompt.net was kind enough to lend the server, and John, Colin Sheppard and Carl Backstrom from Oracle have gotten the ball rolling by creating the first pass of content. As soon as I have a spare moment, I'll also be adding & editing content.

It's great to see that any and all members of the HTML DB community can easily contribute and share their tips, tricks and insight on Oracle HTML DB. I, for one, am anxious to see what others will add, as I'm sure that there will be some excellent tips that I can learn from.

Friday, November 25, 2005

I am Officially part of US History

Well, at least that's what the United gate agent told us before we began to board our flight from Washington Dulles to LAX this past Tuesday. You see, we had some "very special guests" on board. United Flight #197 was officially re-named to TURKEY-1. I'm not kidding - every Air Traffic Controller from Dulles to LAX referred to our flight as this, most with bewilderment.

I've been flying on United for over 10 years now, and I've seen some pretty dumb things, both on the part of them and their passengers alike. I've had 5+ hour delays for no apparent reason, seen drunk passengers denied boarding, even saw an older man punch a woman. I thought I'd seen it all. Wrong.

As I was checking in, I asked if my upgrade had gone through, with little hope as it was the Tuesday before Thanksgiving in the US, one of the busiest travel days.

"No, sir, I'm sorry, first class is checked in full. With 2 turkeys."

"I'm sorry, but I think I just heard you say that there are 2 turkeys flying in the first class cabin today."

"Yes, sir, these are special turkeys. They were pardoned by the President himself."

"This is a joke."

"No, I'm afraid not."

So I essentially got bumped back to coach for a pair of Turkeys.

After most people on the plane expressed concern that our tax dollars bought out the entire first class cabin, the pilot reassured us that the entire trip was paid in full by Disney. And out of respect for the turkeys, there were no turkey sandwiches on this flight. Just those made from their tasty cousin: chicken.

You see, with national disasters like Katrina & Wilma and the earthquakes in Pakistan, it makes perfect sense to pay at least $25,000 (figuring about $1,500 a seat for 12 first class seats, plus at least 2 or 3 additional seats in coach for the photographer & marketing people and I'm guessing $5,000 for a police escorted motorcade from the White House to Dulles, but I've never priced one out, so that figure could be way off) to fly 2 "pardoned" turkeys clear across the country, so that if history repeats itself again, they will die in a couple of weeks anyways.

My suggestion to the United "customer service" manager was to get a photo loading a turkey on the plane at Dulles, not actually fly them, and once the plane get to LA, get another photo of 2 stunt-double turkeys being off-loaded. If any city can find 2 stunt-double turkeys, it's Los Angeles. Then, take the $25,000 or so that was just saved and perhaps buy a whole bunch of chickens & hams (out of respect for the turkeys, of course) and feed those to people who actually need them. However, as the media reported, no one took my suggestion seriously.

So we took off 45 minutes late, as evidently the "be at the airport 1 hour early" doesn't apply to turkeys. To me delight, the turkeys were quiet and did not smell, unless you were right next to their cage. We had a "gobbling" contest, people told turkey jokes, and the First Officer even conducted a Turkey Trivia Contest. The only redeeming qualities of this whole fiasco was that the 1st prize in the Trivia Contest was a bottle of Wild Turkey and that we somehow made it to LAX 10 minutes early.

Since nobody will believe this without proof, here is a photo of the United Baggage handlers strapping the turkey's cage into 3A & B:
















Here is "Marshmallow", the Official Turkey of 2005 of something, shot from the 2nd row in coach through the "peep-hole" in the blukhead that used to be closed before 9/11.















And here is a close-up of "Marshmallow":















As soon as we landed and taxied to our gate, I got up and got off the plane as fast as I could, as the news media was anxiously waiting by our gate, cameras locked & loaded.

So when your children learn about Thanksgiving in the US, perhaps they will read about the story of TURKEY-1, as I am now officially part of US History.

I hope everyone had a safe & enjoyable holiday!

Monday, November 21, 2005

Happy Turkey Day

Tomorrow, I'm off to Los Angeles for the remainder of the week to visit with my wife's family for Thanksgiving. While LA isn't exactly my favorite city, it should be a good time catching up with her family & friends. We're going to see a taping of the Jimmy Kimmel show on Wednesday, so that should be interesting. Amy Smart is the guest, and Def Leppard is performing outside – that alone is a reason to make the trip!

I'm putting the finishing touches on my MAOP Keynote and technical session. I've also changed the topic of my Technical Session to Integrating Oracle HTML DB with the Oracle eBusiness Suite. I simply didn'y have time to get the Oracle Forms presentation in order, hence the last minute change. At one of my current contracts, I've been working extensively with the eBusiness Suite and HTML DB, so I think that this will make for a good story, with lots of advise and best practices.

If you plan on attending the Keynote on Friday, I will say this – it will be something very different than I’ve done in the past. Hopefully, different will be a good different, not a bad different!

Anyways, to the US readers, have a Happy Thanksgiving! To those overseas, enjoy a full week of work! :)

Monday, November 14, 2005

Best Widget Ever!

If you haven't already tried it, give Konfabulator a shot. It's basically Apple's "Dashboard" for the PC (and Mac). (Konfabulator actually came first) It used to cost $20 or so, but since Yahoo! purchased them, it is now free.

Most of the "widgets" are borderline-useless, with a few exceptions:
  • Calendar - nothing like always having a small calendar on your desk
  • Weather - Complete with the forecast, its the next best thing to being outside
  • Wireless Network Status - a meter which shows the signal strength of your WiFi connection
There are many, many more - from dozens of clocks to your favorite movie characters who will say their funniest lines when clicked.

But perhaps the most useful one I have discovered is Knut August Johansen's iTunes Companion. This one stands out above the rest, as it truly automates a tedious task - it fetches album artwork & details from Amazon.com and imports it into iTunes for your existing songs. It will also optionally fetch the Album name, Year, and Track Number and import that into iTunes as well.

I've thrown a number of songs at it - from ABC to ZZ Top - and it seemed to work a good 95% of the time. Simply amazing!

Sunday, November 13, 2005

MAOP Conference Presentations

I'll be presenting the Keynote Presentation at this year’s MAOP Annual Training Conference on Friday, December 8th at George Washington University. My topic will be "The Best Free Tool That You're Probably Not Using" and will obviously mention Oracle HTML DB a couple of times.

I'm very excited, as this will be my first keynote presentation. I've seen my share of them, and understand that the topic needs to be more business-focused as opposed to technical. As this year's conference theme is "Leveraging your Oracle Investment", I figured that would be a good theme for my presentation as well.

I'm also going to be doing a technical session: Making the Switch: Migrating Oracle Forms Applications to Oracle HTML DB.

Before the flame way starts, let me say that not all Oracle Forms applications should and must be migrated to anything. The point that I will attempt to convey is that Oracle HTML DB is a viable platform for some Oracle Forms migrations, should anyone feel the need to do so today.

If you're in the Washington, DC area, consider attending, as there will be several interesting presentations, including a Java/HTML DB track.

Migrating MS Access to HTML DB Article

My article "Migrating to Oracle HTML DB" was just published in the Nov/Dec issue of Oracle Magazine. It talks about the high-level steps required to migrate from MS Access to Oracle HTML DB.

To summarize: Oracle HTML DB is one of the best platforms to use when considering migrating MS Access applications to the web. However, there is no "silver bullet" approach – each MS Access application should be examined and then migrated (or not) to HTML DB accordingly.

Tuesday, November 01, 2005

Oracle 10g eXpress Edition

Lots of activity about Oracle XE over the past couple of days! I figure that I just wouldn't be "cool" if I didn't weigh in on the topic, so here goes: Brilliant!

Here's why: Pure simplicity. Take the situation which happened today: I download Oracle XE and go to install it on my laptop. Download took about 1 minutes, install another 4-5, and I'm was up & running, HTML DB and all.*

My officemate, at about the same time, decided to install Oracle Enterprise Edition and HTML DB on his laptop, just so he could learn HTML DB and perhaps close a piece of our production environment. Needless to say, the download of 10gR2 alone took longer than the install of XE. I was gleefully clicking away, while he sat there, counting the bits as they flew by.

This is one of the largest benefits of Oracle XE – small, compact size with most of the power of the Oracle Database. While Oracle software has always been available for anyone to download, what was the use if you know that legally you couldn't afford to use it for production. It was like test driving a Lamborghini - sure, it could be a lot of fun, but at the end of the day, your time is better spent researching cars that you can afford (appologies to anyone who can afford a Lamborghini) With that barrier removed, I think that many shops who went with other databases on purely a cost basis will take another look at Oracle.

But is it really ready for production? While some may argue that remains to be seen, I would think that aside from the initial glitches, yes, it is. It's not a new set of code – rather, it's the Oracle kernel stripped down and contained in a finite disk & memory space. It would be interesting to see something like asktom.oracle.com running on Oracle XE. It shouldn't be hard, as moving the HTML DB application is trivial – all that would have to be moved is the data, which isn't all that difficult, either.

Truth be told, Oracle XE is going to force many organizations to take another look at Oracle, based purely on cost. Many of these organizations will find that they like what they see, and for smaller projects, may need nothing more. A significant number of these organizations may even upgrade to SE One, SE or even EE, as their needs expand.

As a small business, I couldn't be happier! Hosting my own sites in HTML DB just got a heck of a lot cheaper for both me and my clients. And I feel 100% confident that should their needs outgrow XE, they can easily transition to one of the for-cost editions of the database.

As a final thought, I can't help but think of the poster hanging on the wall in the Parter Technology Center in Reston, VA: It's a picture of an F-16 with the Oracle logo, shooting down a bi-plane sporting the dBase or some other now-defunct database vendor's logo. The caption reads: "Oracle: The Last RDBMS" With the introduction of Oracle XE, we could be witnessing the beginning of a fresh round of the Database Wars, but this time, at the workgroup level.

*(Note: I did have an issue which some friends at Oracle helped to resolve. My username on my laptop (Scott Spendolini) has a space in it. There is a bug in the XE installation script which doesn't correctly add that user to the ora_dba group, and thus HTML DB doesn't work. A workaround is available on the XE Forums here. I'm told that this bug will be fixed before production. That's why it's labeled BETA!)

Monday, October 31, 2005

Bad Customer Service, Part II

It happened again. Poor customer service is the norm these days, but I feel that I have to take a moment to mention exceptionally poor customer service.

Some quick background: A few months back, I bought a file cabinet. The bottoms of the drawers were made of a cardboard-like material that could support about 1 or 2 pounds for a few weeks before buckling and splitting in two. Having this knowledge in hand, I proceeded to put about 10 or 15 pounds in each drawer and hope for the best.

Well, this weekend, the proverbial straw finally broke the camel’s back and my drawer's bottom. I was sort of expecting this, so I knew that when the time came, I'd simply go to Home Depot, get some sort of panel board, and re-build the bottoms of the drawers. So off to the Depot I went, thinking this would be a simple, uncomplicated trip. Wrong, wrong, wrong…

Upon entering the store, I make my way over to the wood section, gawking that they already have Christmas trees on display! I quickly find the boards that I need, and begin my search for an employee. They must know when you're looking for them, as when you don’t need one, you will have at least 3 or 4 of them ask you for help. Finally, after waiting in an ad-hoc queue, I got my turn.

"Hi, I need these two boards cut down to 12 ½ inches by 26 ½ inches please."

"No, not today."

"Why not?"

"Our saw is broken."

"Don't you, like, sell saws here, too?"

"Yes, they are different."

"But the ones you sell – they cut wood, no?"

"Yes."

"So not THAT different."

"No, but ours is broken…"

This went on for a couple more minutes, as I could tell that some other customers were quite amused by the irony I was pointing out. I can’t imagine that I was the only one who needed a piece of wood cut that weekend, and for them to simply accept the fact that their saw was busted is just poor customer service at its best. Luckily for me, I have a good neighbor who has every tool under the sun. He gladly helped me out and cut the boards down to size in just a few seconds. They fit perfectly, and my file cabinet is now back up and running, so to speak.

Competition has its benefits. It fosters alternatives and good service. Take away competition, and there goes the alternatives and good service. The Home Depot that I frequent is just a couple of miles from my house. The nearest Lowe’s are anywhere from 9 to 13 miles away. Not really that far distance-wise, but if you know the DC area, you understand that a drive from Vienna to either Sterling or Alexandria on a weekend day takes most of that weekend day. It's all about travel time, not distance.

The people at the Home Depot know this too: no matter how poor their service is, its still better than dealing with the Mixing Bowl or the Dulles Toll Road.

Thus, another day, another miserable customer service story. This one doesn't have as happy an ending as my other one, as I will likely go back to that Home Depot many times in the future, because it is just so close and convenient. If someone from Lowe's is reading this, you now know where your next store needs to be…

Thursday, October 27, 2005

Game Reset

Fans of NCAA Basketball will understand what I mean by a "Game Reset". For the benefit of those who don’t, let me take a moment to explain: As the game proceeds on into the 2nd half, the last few minutes typically take longer than the rest of the half. This allows the corporate sponsors ample opportunities to remind us which SUV(s) we should have, why Miller Lite tastes better than Bud Lite or vice versa, and why we should refinance our house for the 4th time this year.

Since the networks are sensitive to these breaks, and we have such short attention spans these days, when they come back from commercial, they will typically put up a graphic entitled "Game Reset". This graphic will show the current score, time remaining, number of fouls, and any other relevant statistic (such as 3 players with 4 fouls). It basically allows the viewer to become reacquainted with the game that they left for 60 seconds or less.

I like this concept, as sometimes you’re not always paying 100% attention to the game. More than once I’ve watched my beloved Orangemen dominate their opponent for the 1st half, only to gradually give back the lead in the second. All too often I get overconfident and stop paying attention until I see one of these "Game Resets" and say to myself "how did we lose that 20 point lead!"

So why on earth would I blog about commercials (or adverts for those in the UK) and basketball? Developing an application, particularly in HTML DB, can be compared to a college basketball game. There's an initial sprint to get as far ahead as possible. Once you establish some good progress, you try to regroup, and hope rest of the project goes as smoothly. Often, requirements are changing hourly, features get added and removed and then added back again, and things generally go downhill. Before you know it, you're looking at code in your application that you're not even sure how it got there, let along what it does. Time for a game reset.

I've found it extraordinarily helpful to every now and again stop everything and take a good look at all of the pages and code that I've assembled. More often than not, I'm able to tighten things up considerably, remove obsolete objects, and generally make a better application. With tight deadlines and limited resources, this type of activity is typically on the end of your "to do" list, but I can personally attest that it has helped me tremendously.

One of HTML DB's strengths is that it lets developers rapidly build applications. One of HTML DB's weaknesses is that it lets developers rapidly build applications. This is not a typo! It doesn't take a genius to realize that applications built quickly and "under the gun" are much more susceptible to poor design than those which are built at a more leisurely and regulated pace.

Make sure that you get in the habit of taking an occasional Game Reset to see where your applications stand. You'll be more than happy you did.

Tuesday, October 25, 2005

AJAX Select List Code Generator

Carl Backstrom has provided some truly amazing HTML DB AJAX & DHTML examples on his demo page on htmldb.oracle.com. Most of them are documented, but they assume that you have more of a technical background than not.

With Carl’s blessings, I took one of his examples – AJAX Process on Demand – and created a working example of an AJAX-based Select List. The example is quite simple: select an Employee Name, and the Job field will automatically be populated with the corresponding Job from the EMP table. This is a common behavior in Oracle Forms (when a user select one thing, another field is auto-populated) and will make the transition for those coming from a Forms environment a bit easier.

To take it one step further, I created an AJAX code generator of sorts. Give it the required six fields, and it will automatically generate the PL/SQL Application Process, the JavaScript Event and the JavaScript function. Now you can harness the power of AJAX (or at least this one simple example) without knowing J or X!

Friday, October 14, 2005

MAOP Tech Talk: Oracle HTML DB

I'll be presenting at the first MAOP (Mid-Atlantic Association of Oracle Professionals) TechTalk on Friday, October 21st, at the Federal Trade Commission in Washington, DC at 1pm. My topic will be: Oracle HTML DB: Where Does it Fit?

In my presentation - which is my first public speaking engagement since leaving Oracle - I will be discussing all of the places that I have seen HTML DB used by a wide variety of customers - from Federal Government to Commercial, and everything and anything in between. I'll also spend some time going over what's new in Release 2.0.

If you're going to be in the DC area that Friday, please feel free to attend. NOTE: If you do plan on attending, please RSVP to mike.licht@casetech.net in advance, otherwise you will not be allowed into the buidling.

Wednesday, October 12, 2005

Generate PDF files from HTML DB w/out Java!

The question: How do I print PDF documents with HTML DB?

The answer (maybe): PL/PDF

Looks like the folks at PL/PDF have some instructions on how to set it up in an HTML DB environment. For only $300, it seems like a good deal. It's not WYSIWYG, but the code for a complex master-detail report, which contains page breaks and headings on each page, is not that bad.

If you host your HTML DB site with Revion, PL/PDF is even included as a part of their package.

I have not tested this out yet, but am anxious to do so, as I'm sure it will come in handy!

Wednesday, October 05, 2005

Manipulating Images with the... Database?

A recent thread on the OTN HTML DB Forum asked about how to determine the width & height of an image stored as a BLOB in an Oracle table. I mentioned in that thread that I have some code to manipulate an image stored in a BLOB column. This is particularly useful if you’re going to let users upload images, and you want to re-size them to display as a thumbnail.

Thanks to Oracle interMedia, it is trivial to manipulate the width, height, and other attributes of images stored in an Oracle table. I’ve created a sample application here which demonstrates Oracle interMedia and HTML DB in action. Feel free to have a look. You can download this application from HTML DB Studio as well.

Basically, this application allows you to upload images and perform an operation on the image as it is inserted into the PHOTO_CATALOG table. There are two places where some PL/SQL code is required: an After Submit process on page 2, and a procedure to display the images.

Here is the PL/SQL for the After Submit process with some comments in-line:

declare
l_photo blob;
l_thumb blob;
begin
-- Fetch the 2 BLOB columns into local variables
select blob_content a, blob_content b into l_photo, l_thumb
from wwv_flow_files where name = :P2_PHOTO_NAME;

-- Rezise the PHOTO column so that it is proportionally 400x400
ordimage.process(l_photo, 'maxScale=400 400');

-- Determine which operation was selected, and then perform
-- the appropriate Oracle interMedia function on that image,
-- using the user defined width & height
if :P2_OPERATION = 'Proportional' then
ordimage.process(l_thumb, 'maxScale=&P2_WIDTH. &P2_HEIGHT.');
elsif :P2_OPERATION = 'Fixed' then
ordimage.process(l_thumb, 'fixedScale=&P2_WIDTH. &P2_HEIGHT.');
elsif :P2_OPERATION = 'Flip' then
ordimage.process(l_thumb, 'flip');
elsif :P2_OPERATION = 'Mirror' then
ordimage.process(l_thumb, 'mirror');
else
null;
end if;

-- Insert the data into the PHOTO_CATALOG table as a SELECT from
-- WWV_FLOW_FILES and by referencing HTML DB items
insert into photo_catalog
(photo_catalog_id, photo_name, description, uploaded_on,
mime_type, photo, thumbnail)
select :P2_PHOTO_CATALOG_ID, :P2_PHOTO_NAME, :P2_DESCRIPTION,
sysdate, mime_type, l_photo, l_thumb
from wwv_flow_files where name = :P2_PHOTO_NAME;

-- Remove the image from the HTML DB wwv_flows_files table
delete from wwv_flow_files where name = :P2_PHOTO_NAME;
end;

The code for the procedure used to display the image is similar to the CUSTOM_IMAGE_DISPLAY procedure code supplied as a part of the Sample Application.

create or replace procedure display_thumb (p_photo_id in number)
as
l_mime varchar2(255);
l_length number;
l_file_name varchar2(2000);
lob_loc BLOB;
begin
select mime_type, thumbnail, photo_name, dbms_lob.getlength(thumbnail)
into l_mime, lob_loc, l_file_name, l_length
from photo_catalog where photo_catalog_id = p_photo_id;

-- Set up HTTP header
-- Use an NVL around the mime type and if it is a null, set it to
-- application/octect - which may launch a download window from windows
owa_util.mime_header(nvl(l_mime,'application/octet'), FALSE );

-- Set the size so the browser knows how much to download
htp.p('Content-length: ' || l_length);

-- The filename will be used by the browser if the users does a "Save as"
htp.p('Content-Disposition: filename="' || l_file_name || '"');

-- Close the headersowa_util.http_header_close;
-- Download the BLOB
wpg_docload.download_file( Lob_loc );
end;

Finally, the query on Page 1 has some HTML embedded into it so that it can display the THUMBNAIL column in the report. Here’s the SQL for that query:

select "PHOTO_CATALOG_ID", "PHOTO_NAME", "DESCRIPTION", "UPLOADED_ON",
"MIME_TYPE", '<img src="#OWNER#.display_thumb?p_photo_id=' ||
nvl(photo_catalog_id,0) || '" />' thumbnail
from "PHOTO_CATALOG"

That’s all you need in order to do some basic image manipulation with Oracle interMedia and HTML DB!

Tuesday, October 04, 2005

Forming an LLC

DISCLAIMER: I am not an attorney, nor have I sought the advice of one for creating an LLC. The steps which I outline below may be incomplete, erroneous, or just plain wrong. They are meant for informational purposes only, and should not be used as a guide in any way.

A few days and $100 later, the first of several steps required to start an LLC in the Commonwealth of Virginia is complete:




I have officially filed with the State Corporation Commission of the Commonwealth of Virginia – which ironically, I have demoed several Oracle products to. I can say with almost 100% certainty that this is not one of them.

Once I hear back from the SCC, it’s off to irs.gov to get an EIN. After that, I have to fill out a form for the Virginia Department of Taxation, check with my county & town governments, and then I’m officially in business!

I also ordered QuickBooks Pro so that I can keep my own books. This is not something that I’m 100% comfortable with, but I have used Intuit’s TurboTax before, and if QuickBooks is anything like TurboTax, I should manage. Worse comes to worse, I know a couple of CPAs that may be able to be persuaded to help me out.

For the next few days at least, I’m waiting to hear back from the Virginia SCC, as all of the preceding steps depend on the LLC being successfully registered.

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: http://www.marcato.org/luke/web/cursors.html

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

<td class="t12data"#ALIGNMENT#
onMouseover="this.style.background='#0f0'" onMouseout="this.style.background='#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: http://htmldb.oracle.com/pls/otn/f?p=33582:1

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)
as
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
is
l_sql varchar2(32767);
l_collection_exists boolean;
begin
l_sql := 'select id, name, address
from my_collection_view';
l_collection_exists :=
htmldb_collection.collection_exists(p_collection_name =>
'MY_COLLECTION');
if l_collection_exists = true then
htmldb_collection.delete_collection(p_collection_name =>
'MY_COLLECTION');
end if;
htmldb_collection.create_collection_from_query(
p_collection_name => 'MY_COLLECTION',
p_query => l_sql);
end;

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 wdbsrv.app 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: http://spendolini.blogspot.com
New Blog RSS: http://feeds.feedburner.com/ScottSpendolinisBlog

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.


Huh?

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 Techbargains.com. Techbargains.com 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 NewEgg.com 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.