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.

I peruse the Delicious linkfest page periodically when I need something new to read - usually when I need a break from coding. What I found today is an old article, but really sums up good coding techniques..

Free Programming Tips are Worth Every Penny

Here's a summary - "* The Way of the Code Samurai *"

• Think first. Think some more. Don't write code until you know what you're doing. This goes to design. A good basic design will accomodate lots of modifications in the future. You must understand business processes, both what is for the Now, and what will be in the future. Understanding both will get you headed in a good design direction, and the application will have the ability to grow and change easily as business processes evolve over time.
• Write all your code "clean," the first time you write it. It's hard to say how many times I've looked at old code and said "what was I thinking?" Some of that is because I've learned over time new ways of doing things that are more effecient, but sometimes, I took the easy route, and did the QnD (quick and dirty), only to have to go back and fix it - the right way.
• If you're in code anyways to extend it or fix a bug, CLEAN IT. This is refactoring, always trying to make the code better, more flexible, more extensible. Sometimes doing this takes a little longer, but In my mind is mostly worth it.
• Less source code is better. Less for your successors to learn, figure out, and, inevitably, to fix.
• Optimize methods ONLY after they work - Don't optimize as you write. I do this all the time. I write a piece of code, sometimes several that do similar things and get it all to work. The trick is to recognize the fact that some modules can be combined, and go back and combine them, passing the parameters to make a single module do many things. The advantages cover much of the earlier points. But, get it to work first, then combine.

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.

37 Signals is a web development, design and consulting firm based out of Chicago. They are a classic example of how a company sponsored blog ("Signals vs. Noise) can be used as a marketing vehicle. Jason Fried, who looks to be the primary writer on the blog writes smart and interesting posts about web stuff almost daily. The blog has quite a following and the comment section of each post always has some interesting discussion.

Recently, 37signals launched Basecamp, a hosted, online project management application. I'd bet that the tool was developed in house to help 37signals manage their own business. One day, somebody said "other people should use this, and they might even pay for it." So they likely invested some time and money, turned it out to the public and now have people paying anywhere from $12 to $99 per month to use it. A wonderful idea, and something I'd love to do also.

I signed up for a free Basecamp account that allows me to manage a single project. I have to admit, the application works really well. It takes a collaborative blog concept, adds a few widgets here and there and now they have a online application that people love. Congratulations to them.

But when I look at it from a developers perspective, the product is not really that deep. In some respects, my project "Reparté Collaborative" is looking at the same target market and has lots more conceptual functionality. I was focusing the application to web based development/support teams, using a blog with comments as the center, and adding the repository of other stuff around it - database info, URL, user names, passwords, document management, file upload/download etc would all be stored in the application. Adding to do lists and milestone tracking to the application would be pretty trivial. All of this would be contained within a central, available anywhere with a login, website. My concept is solid, and I believe there is a market out there who would even pay for it. It would not be that hard to develop.

Two things make finishing the build difficult: finding your customers who have money to pay, and time. 37Signals used their blog to develop a respected "brand" concept. The blog is also frequented by their target audience - web savy people who need an easy way to get organized. So when the product was ready, (I'm making an assumption here) they said "here, try this" and probably got lots of feedback - both in the determination of what functions should be included, an what someone might pay for it.

The other barrier is time for development. You see, I have this thing called a "day job", and I already have a "night job" too (daddy/husband). So other than day or night, when could I finish the build out?

A recent blog entry on SvN that would probably help me was titled Less is... More satisfying.

...I think we just cut down the feature list by about 2/3. Wow does that feel good. Faster to market, easier to design, more attention to detail on the 1/3 that remains...

Probably wise words, but then I'd have to do that marketing thing..

It's frustrating sometimes to see products coming to market that I could do using my skills. I guess I just need to quit my job and do all these software projects that rattle around in my head. Are there any Venture Capitalists out there with some money to burn (I don't need that much) on a smart web developer with lots of ideas that everybody else seems to get done first?

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.)

I got a couple of laughs from this. But it's a bit of sad commentary on developers and their projects.

1. 70% of all cars would be built without following the original designs and blueprints. The other 30% would not have designs.
2. Car design would assume that safety is a function of road design and that all drivers were considerate, sober and expert drivers.
3. Cars would have no airbags, mirrors, seat belts, doors, roll-bars, side-impact bars, or locks, because no-one had asked for them. But they would all have at least six cup holders.
4. Not all the components would be bolted together securely and many of them would not be built to tolerate even the slightest abuse.
5. Safety tests would assume frontal impact only. Cars would not be roll tested, or tested for stability in emergency maneuvers, brake effectiveness, side impact and resistance to theft.
6. Many safety features originally included might be removed before the car was completed, because they might adversely impact performance.
7. 70% of all cars would be subject to monthly recalls to add major components left out of the initial production. The other 30% wouldn’t be recalled, because no-one would sue anyway.
8. The after-market for safety devices would include such useful products as training wheels, screen doors, elastic seatbelts and devices that would restrict the car’s top speed to 3mph, if found to be unsafe (which would be always).
9. Useful safety could be found, but could only be custom retro-fitted, would take six months to fit and would cost more than the car itself.
10. A DOT inspection would consist of counting the wheels and making recommendations on wheel quantity.
11. Your only warning indicator would be large quantities of smoke and flame in the cab.
12. You could only get insurance from one provider, it would be extremely expensive, require a duplicate DOT inspection, and you might still never be able to claim against the policy.

Found here and attributed to "Denis Verdon, Senior Vice President Corporate Information Security at Fidelity National Financial" but I seem to remember seeing it somewhere else in a similar format.