On The Mark Technology

So I've got an application that requires some reporting. There are several different reports, and each report can be run with any number of input filters. Some of those filters are required, some are not. So the requirement is to select a report, and based on that selection, display the available filters - some of those filters even require some dynamic selections.

So here's what I did.

I built a reports table, and a report_filters table - a report may have 1 to many filters associated with it. When the user hits the reports page they see a drop down box of the reports available. Also on this page is a couple of hidden regions; "Filters" and "Results".

The drop down box has an "onChange" action that passes the selected report ID to a server procedure via Ajax that provides the HTML required for all the filters available for that report in the "Filters" section of the page. This is where the Native Dynamic Sql (NDS) comes in.

In my table of report filters I have a column (filter_input_procedure) where I put in an anonymus PL/SQL block. This block gets executed in a loop of all filters for for selected report with an EXECUTE IMMEDIATE command. When this fires, the pl/sql block interacts with the database and outputs the required HTML for the input filter.

procedure build_rpt_filters(p_rpt_id in number) is
cursor available_filters is
	   select * 
           from report_filters_table 
           where rpt_id=p_rpt_id 
           order by sort_seq;
begin
for xx in available_filters loop
	execute immediate xx.filter_input_procedure;
end loop;
end build_rpt_filters;

To build the start date input procedure show in "Report With Two Filters", here are the contents of the "filter_input_procedure" field.

declare
begin
htp.p('Provide the Start Date (mm/dd/yy): ');
htp.p('<input type="text" name="p_start_date" size="10" value="">');
end;

The next thing I had to do was accomodate the user entering a value for a filter, but show them some information that allows them to confirm they have entered the right value.

In the "Report That must do a lookup", the input filter is displaed as before - this time with an input box and a "Submit" button. However, the "submit" button has it's own AJAX call that executes another procedure on the server fires to get another batch of HTML, which is displayed in the "Results" section of the page.

EXAMPLE:

One of the last reasons for doing report filters in this way was a support requirement. The application I wrote this for is supported remotely and often without access to the server. So as much configuration as possible is defined in tables and a user interface built for it. Using Dynamic Sql allows me to control some coding aspects on the fly, and using AJAX allows me to craft a user interface that quick and intuitive.

Here are a couple of AJAX Examples using my technique I described in a previous blog entry.

Sometimes I wonder if my chosen language of expertise, PL/SQL, is becoming obsolete. It's been a primary ingredient of the Oracle database "soup" forever, and functionality does get added to it with each release. However, Oracle's marketing department jumps up and down to promote their implementation of Java. Java here, Java there, J2EE, Java "beans" everywhere, Java in the database. I start to think that maybe I'm missing the boat by not learning Java - not coding my apps in Java. Is it possible to teach an old dog a new trick, or language in this case? That learning curve looks awfully steep from the bottom of the hill.

But then I read something that begins to restore my faith. I don't own any of his books, but Tom Kyte is one of the few folks (Steven Feuerstein is another) who sing the praises of PL/SQL. Recently, in response to a question on the Ask Tom website, Tom quoted from his book "Effective Oracle by Design" about "Why PL/SQL" and help reaffirm that PL/SQL is efficient, effective, robust, scalable, and, importantly, here to stay.

It should speak volumes about PL/SQL's capabilities that entire products are written in the language. Here are some examples:

Oracle's advanced replication was implemented in PL/SQL...
Oracle's Application Suite (HR, Financial, ERP, and so on) was written in PL/SQL... snip
Oracle's Workflow engine, which lives in the database, is written in PL/SQL...snip
The administrative interface to the database is written in PL/SQL....snip

I think I'm pretty good with PL/SQL - I've been coding web apps in it for 8 years. I do know however, that the scale still favors the "stuff to be learned" side of the equation. Nearly every day, I learn something new about the language, and I hope to continue that trend by reading, experimenting, and challenging myself to look at every piece of coding as an opportunity to learn something new.

Building software requires both knowing your tool, and applying creative thought and problem solving skills. There are innumerous ways to solve a given problem with code. What separates the hacks from really good programmers, is solving the problem with a solution that is effective, efficient, easy to understand, and maintainable by some other than the person who built it. That, is when software, becomes art.

Thanks Tom, (and Steven) for restoring my faith in my chosen technology.

I implemented a search engine on this site today. It's relatively basic, but here are some of the details.

• The search will look for all your words, in any order.
• I iteratively search the blog, the quick hits, and then the portfolio, looking for the words you supplied.
• The result set displays the linked title, and clicking it will go to the entry. If the text of the message is bigger than 250 characters, it displays a "continued" link at the end that will also take you to the entry.

Pretty typical, but what if I think that several of the articles look like something I'd want to read? Do I want to force the user to enter their search term again, see the results, try and remember which article they just read, then drill down into the article? No. I'd like to have the user select the articles they want to read, and see all of them in one click.

And that is what it does.

For each row returned in the search results, there is a checkbox. You can click the checkbox for each of the articles you want to see, click the "View Selected Articles" link, and all those articles are presented on a single page.

I don't think I've ever seen that before on a blog search facility.

There were a couple of issues that proved interesting to deal with.

1. When the 250 character cutoff was in the middle of a link tag, the "continued" link stopped working. For this I simply changed the "<a" start of the link tag with a "<!a" which essentially comments out the link. The closing link tag didn't matter.
2. I took a similar approach for any "<ol" and "<li" tags. I started to think I should just do it to all tags, but then any nice "<p>" tags caused the layout to pooch. There are probably a few more gotchas in the first 250 characters of the message that I'm not handling... but that'll be something for later.

Go ahead and try it. I think it's cool, in a web geeky kind of way.

Over the weekend I patched my database to 9.2.0.6 from 9.2.0.5. This was not a difficult upgrade, but it was necessary due to an Oracle bug I ran into while working with the XML toolsets built into the database.

There were two problems I encountered during the upgrade that caused it to take somewhat longer than normal. During the installation, I got a number of errors regarding some DLL's that were "in-use" and could not be replaced. I thought this odd as all of my Oracle services had been taken offline.

A quick search of Metalink (Oracle's online support forum) quickly found a note on the errors which suggested stopping Microsoft's Distributed Transaction Coordinator (msdtc.exe) service. Once I did this the installation went without a hitch.

The second issue is when I ran the catpatch.sql script. This script rebuilds many of the core objects in the database using the upgraded dll's. I believe this is what actually upgrades the database. This script went for about 10 minutes and then hung just after a large comment in the script about how you'll get an error message if there's not enough tablespace. I knew I had plenty of space available, and the procedure gave me a message about compiling successfully - but there it sat. When I upgraded to 9.2.0.5, the log indicated the script ran for about 15 minutes. After 20 minutes, I saw there was no CPU activity and there were no changes being made to the log file. It was apparent the process was hung.

Back to Metalink, where somebody suggested to another post that the database must be in "noarchivelog" mode before running the script. My database was in "archivelog" mode (which allows you to take reliable backups while the database is still online). So we cancelled the script, bounced the database out of "achivelog" mode, restarted the script and it ran to completion. Then we bounced the database back into "archivelog" mode and restarted everything. All applications appear to be back online.

If the documentation had been complete, noting that the DTC should be turned off and the database should be in "noarchivelog" mode, the upgrade would have taken less than 30 minutes. Metalink, once you get familar with how the search engine works, can be very effective at solving problems.

The good news is that now the bug I was experiencing is fixed and I can continue on my way in developing my "Feedfrenzy" application.

Over the years, I've developed lots of forms for my applications, and using PL/Sql to process the data in those web forms I've run into my fair share of "gotchas". The HTML 4.01 forms specification does not provide much in the way of data validation other than the "maxlength" attribute for text fields. This is useful, but doesn't go far enough. The Web Forms 2.0 proposal being sponsored by the Web Hypertext Application Technology Working Group is probably the most robust approach, but is likely years away from being widely adopted. However, no approach will be bullet-proof, and will never replace the requirement for good server-side data validation.

One of the ways to help get good data to the server is to use Client-side scripting - typically JavaScript. This helps ensure that data getting to the server is valid. I normally will include bits of JavaScript functions to help the user submit good data - usually to ensure required fields. There is also an interesting approach brought up in an article on A List Apart entitled JavaScript Triggers that uses custom attributes on form fields that can be used by JavaScript to improve user data submission.

However, due to security and privacy concerns, in most browsers JavaScript can be disabled. Therefore, it cannot be relied upon to provide good data to the server.

One of the frustrating data types to handle with web forms is the date. Some sites handle this using three separate input fields - one each for month, day, and year. Some go even further to idiot proof the form by using drop down lists for values. I find both of these methods tend to clutter the input form. I will typically use a single field and give the user some indication of what I expect as a "normal" input format.

On the server side, input parameters in the pl/sql packages are defined along the same lines as any other variable types - character, number, date etc. But if you accept only 1 field that contains the user input for a date, defining the input variable as an Oracle date format is a recipe for a bad user experience. Despite providing the "expected" format to the user, they will invariably, give you something different. How this is handled, can have a direct impact on how users will perceive the system.

The default Oracle date format, for US users, is not intuitive - today, the 8th of February 2005, by default (at least in the database's I work in) is recognized by Oracle only in the format of '08-FEB-05'. If you define your input parameter as a date type, then your user submits the US normal date format of '02/08/05' the application will raise an error, and will be displayed to the user, as a "404-Page Not Found" error (subject to some configuration options).

My answer on this has always been to define the input variable as a character, and then handle the conversion in the code, where I have a better way to try and process the input, or to handle the error in a more appropriate manner. This way, if somebody submits '02/08/05' I can convert it correctly and move on with the application. If someone entered '30-FEB-05', the date would fail, but I can give an error message to the user rather than bombing out.

A similar issue is raised when defining the input as a numeric field. If the user puts in non-numeric data and the input variable is defined as a number, bad things happen. Like date fields above, I will often define the input variables as a character, and handle the testing and conversion in code. There I can trap the error and then I can either try and derive what the user tried to enter (entering commas in a number creates a non-numeric value - but those can be stripped out), or feed back an friendly error message.

One of the other areas is using multi-valued fields. These are provided to the pl/sql package in a table datatype (think array). However, if this format is defined as a character, it will not cause the procedure to fail. But, you will only have access to the first value. You must define the input field as a pl/sql table, and then process that table to get all the values.

Developing web applications relies heavily upon forms processing. In Part 1, I talked about how mod_plsql, Oracle's web interface to stored procedures, converts the data in your form fields to variables either explicitly, or through flexible parameter passing.

mod_plsql also allows you to process form variables explicitly, while handling multi-valued fields at the same time. This method is required when your HTML form contains form objects like "checkboxes" or select lists with the "multiple" attribute. In these cases, the user wants to collect from zero to many values for the single field.

Early in my career, I felt that multi-value inputs were difficult, mostly because my examples used an Oracle Web Toolkit (provided with the database) data structure of "owa_util.ident_arr" to accept multi-valued fields. This was fine, but I didn't know how to specify a default value for the array ("default null" didn't work as the data type you wanted to default it to had to be the same data type).

So my procedure looked like this:

procedure old_days (p_title in varchar2 default null,
                         p_type in varchar2 default null,
                         p_selected_cats in owa_util.ident_arr,
                         userAction in varchar2 default null);

Now, when I called the procedure examples.old_days, it would fail with 404 because the field "p_selected_cats" was not provided, and no default value was specified. To remedy this, I had ensure that every time the procedure was called, it had a "p_selected_cats" name=value pair with it - examples.old_days?p_selected_cats=0. So even though the form the user may have been working on didn't use the multi-valued field, it had to be submitted.

Coding to handle the "bogus" value also was ugly. When processing the array, my loops always had to start with item 2, because I knew that item 1 was going to be the placeholder.

For i in 2..p_selected_cats.count loop
    do_something
end loop;

Sometimes, I even built new procedures to handle the multi-valued form values (i.e. examples.old_days and examples.old_days_multi). In my mind, this resulted in a collection of "less than elegant" code blocks.

As my knowledge of PL/SQL expanded, the answer to this became evident - create a default value for a pl/sql table. Now, I build my code using a custom variable type, defined in my procedure, and an instance of that type that is referenced in the procedure as the default value.

type myplsqltable is table of varchar2(2000) index by binary_integer;
-- Now create an instance of an empty array of varchar2s for initialization.
empty myplsqltable; 

Now with this definition, I could have the following procedure definition that handles both single and multi-valued fields and not have to pass a default value to the multi-valued variable:

procedure more_enlightened ( p_title in varchar2 default null,
                         p_type  in varchar2 default null,
                         p_selected_cats  in myplsqltable default empty,
                         userAction in varchar2 default null);

This could then be called simply with examples.more_enlightened, or it can handle multiple values for p_selected_cats - like this. In this way, I am explicitly naming my variables the procedure expects from the form, and I can accept multi-valued fields as well and process them without having to filter the "bogus" values.

Writing web applications involves lots of form processing. When using Oracle and its embedded procedural language, PL/SQL, the developer must also be very crafty in handling those forms.

Oracle packages the Apache webserver with a database installation. To interact with the database with PL/SQL, Oracle provides the "mod_plsql" plugin that allows the user to execute stored procedures in the database from a browser.

However, the pl/sql procedure that processes those form, must be coded to accept the variables in the form. Fail to include a variable that the procedure expects - it will fail with a 404 (file not found) error. Include a variable that the program doesn't expect - it will fail similarly.

While that sounds pretty limiting on the surface - Oracle provides two methods of handling it - First, you can specify a default value for that variable, and it can be null.

PROCEDURE html (useraction in varchar2 default null);

In that example the procedure will handle a variable named "useraction", but if it is not provided, that's ok. So examples.html works the same as examples.html?useraction=userAgent

mod_plsql also provides for handling any number of variables, even you cannot expect to know what the names of those variables. It's called "Flexible Parameter Passing", and essentially, the mod_plsql plug-in will take all the variable names and values provided, and submit them in a PL/SQL table data structure (if you don't know PL/SQL, its just an array). So the procedure specification looks like this:

PROCEDURE flexible (name_array in array default empty, 
                    value_array in array default empty );

However, to make this happen, the procedure name must be preceded with and exclamation point (!), otherwise, mod_plsql will try and match the parameters submitted to variables the procedure is expecting. So to use this feature, you code the URL like this!examples.flexible and then you can pass any collection of variable names and values to the procedure.

Flexible parameter passing can be very useful when either the number of fields your procedure is not known, or the number of parameters becomes very large making your procedure definition very unwieldy.

However, while flexible parameter passing does give you unlimited flexibility for handling variables that you may be expecting, it does require processing of those arrays to extract the field and value you want. In most of my apps, my code has built the form, so the likelihood of getting field names and values I'm not expecting is small.

(In the next installment of this subject, I'll address multi-valued fields (i.e. checkboxes and multi instances of same field names) submitted to a procedure.)

Note: This is part 3 in a series about using PL/SQL to parse the User-Agent HTTP Header and how to use it in your PL/SQL web applications.

In Part 2 of this series, I talked about how to determine the user's browser and it's version number from the "User-Agent" variable using the PL/SQL toolkit. In this installment, I look at that same variable to determine the user's operating system.

While differences in browser versions can present a challenge to the web developer, we also find that the same browser and version number will behave inconsistently between operating system platforms. One example is Internet Explorer on the Mac. Before version 6, there was a wide difference between how pages would be rendered on an Intel based machine running Windows, and a Mac.

As I mentioned in a previous article, as the browser industry matures and become more standards compliant, we should see a decreasing regularity of differences between browsers and operating systems. But until that happens, we can look to the "User-Agent" variable to give us pretty reliable insight into the systems our users are using to visit our sites.

So before we start examing the "user-agent" values though, you need to determine to what level you want to detect. Is is enough to keep it at the Windows/Mac/Linux/Unix level? Or is it important to see that it is Windows 98, or RedHat Linux?

On this site, I do differentiate Windows versions, but that is a losing battle as new operating systems emerge. Other than windows, it is simply Mac, OS/X, Linux, and Sun that I report.

To make it simple, you could simply look for "Win" in the "User-Agent" to identify Windows machines. (Microsoft normally puts the word "Windows" in the variable, but I have found some variables using shorthand "Win95" and "WinNT", hence my decision.) After that, I look for "Mac", "Mac OS X", "Linux" and "SunOS".

Clearly, looking for the operating system can be much simpler than the browser version.

(In part 4, I will discuss key usage and reporting of statistics using the user-agent variable.)

Note: This is part 2 in a series about using PL/SQL to parse the User-Agent HTTP Header and how to use it in your PL/SQL web applications.

In Part 1 of this series, I talked about how to obtain the value of the "User-Agent" variable using the PL/SQL toolkit. In this installment, I will experiment with the value returned, and how to parse into a piece of usable information.

While I complained a little in the last article about the lack of standards, it actually isn't that bad. The good thing is that the browser makers, as they progress through different versions of their software, have maintained a certain consistency in the usage of the "User-Agent" field. On the other side however, different applications have formatted their data differently, so that we must first determine, essentially the manufacturer of the browser, then drill down to find the version.

Microsoft Internet Explorer

Being the current "big guy" of the browser market, we'll look at Microsoft's Internet Explorer first. IE, from version 3 forward (that's as old as my logs go) include the characters "MSIE" in the "User-Agent" string. That makes it easy. I can determine if the browser is IE using a simple INSTR function:

Which essentially says "if the letters MSIE are contained in the user_agent string, then the browser is a version of Internet Explorer.

Microsoft has also been consistent in it's placement of IE's version number. After the "MSIE" there is a space, the full version number, and then a semi-colon. So by using a combination of the SUBSTR function and the INSTR, my function will shorten "MSIE" to "IE", and then report the full version number, e.g. "IE 6.0". Here is the code:

Here are a few examples of the raw "User-Agent" field, and what I return:

Other Browsers

Most of the other browsers I've seen on my site, can be detected using a similar method: look for a string, and the version number will be somewhere after. The strings I look for are "Netscape", "Firefox", "Firebird" (the old name for Firefox"), and "Opera". "Opera" is an interesting one because it can morph it's user-agent value to try and fool you. In the value, it actually says 'MSIE' in the same position where IE normally does. However, it will usually also say "Opera" further to the right. To handle this, I simply order my IF statements such that it searches for "Opera" first, then "MSIE". Since IE will never report itself to be "Opera", I think I'm pretty safe there.

When we get into detecting the older versions of Netscape, the data starts to break down. Older versions may not say "Netscape" in the value. In these cases you must look at the value just past the "Mozilla" label at the front of the string. Not particularly reliable, but those browsers are pretty few and far between at this point.

The last bit of detection I do is aimed at the robots that continuously visit your website. These can be any number of huge variety of sources, but I only report on two: Google, and the W3C Validator. Many other robots do identify themselves with the user-agent field. Most look innocent enough, include RSS feed sniffers, college search engine projects or browser developers. None of these are really useful as far as customizing content for them, but I suppose if the robot was malicious, you could certainly deny content based on the value.

(In part 3 of this article, I'll discuss how I parse the user-agent string to determine the operating system that the user is using. In part 4 will discuss key usage and reporting of statistics.)

Note: This is part 1 in a series about using PL/SQL to parse the User-Agent HTTP Header and how to use it in your PL/SQL web applications.

In managing a website, it's often useful to know which brand of browser, and which version of the browser your users use to access your website. Sometimes, you may even feel the need to present the user different content or layout based on the browser or operating system they are using. In some cases, webpages may be displayed differently between any of these three variables (Browser, Version, and OS). As browsers evolve to fully and completely support web standards however, the need for the second item will hopefully diminish. But until then, browser detection can be important to the developer.

Luckily, when you request a webpage from a server, all browsers will first introduce themselves to your webserver in what's called the HTTP Header. The header includes information such as the language you prefer, the character set you are set to display, the types of files your browser is configured to handle, and any cookies that might have been set by my server on a previous visit. Additionally, the browser will report the "User-Agent" of the browser. This "User-Agent" value provides not only the user's brand of browser and version to the sever, but also provides a peek at the user's operating system as well.

Oracle's PL/SQL toolkit provides access to this information using the "OWA_UTIL" package. Using the function "GET_CGI_ENV" and passing the value "HTTP_USER_AGENT", you can get access to the full string of information that is available in the "User-Agent" value. Here is the function call:

owa_util.get_cgi_env('HTTP_USER_AGENT');

As I prepare this entry, I am using Firefox, v0.9 on a Windows 2000 System. The User-Agent that is being passed to the server is:

Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7) Gecko/20040707 Firefox/0.9.2

On Internet Explorer, it comes back as this:

Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)

Here is what I know about you.

Now it's really too bad that there couldn't have been some standards set between the browser makers to make this a little easier for the lowly developer. I suppose some coordination back in the wild west days of the internet and browser technology was probably too much to ask for, especially considering that Microsoft was involoved. So what we are forced to do is divide up the user-agent value, look for little tidbits that experience says will be consistent, and build a routine that will work, and when it fails to get accurate values (the user-agent value will change over time as new browsers, versions and OS's come out), make and educated and fairly accurate guess.

(In part 2 of this article, I'll discuss how I parse the user-agent string to determine the browser and the version number of the browser the user is using. In part 3 I'll discuss the OS detection, and part 4 will discuss key usage and reporting of statistics.)