Finding Your Way With Oracle
A Guide to Database Programming With Oracle on the Windows Platform
©Zeinab, 2006

---

You might have noticed the "Copyright" symbol attributing the work to Zeinab (Me). However, that merely serves to indicate that we're discussing Windows, not necessarily that the article was born out of thin air.

First of all, we shall see what aspects of database programming we're going to touch upon this time.

• We shall see the structure of a Web-based database application using ASP.NET. It might as well be applied to ASP, as "Best Practices" remain the same throughout.
• We shall make use of core Oracle functionality that has existed since Oracle 7.3.4. We shall not make use of the latest functionality.
• We shall see the uneasy relationship between the Web developer, database developer, and the sysadmin. We shall also see the case when you are fulfilling all three roles. Of course, we shall discuss why you have to be the three-in-one IT employee.
  
• Wherever possible, I'll use images. A picture is better than nonsense.
  
• Emphasis, as in all my programming articles, will be laid on the command line. GUI is a complicated wrapper built around a command line. Those who rely too much on the GUI are either ignorant of what happens behind, or in too much hurry; in either case they bring the application to its knees and the organization disrepute. I hope you are not one of those, or if you are, that you want to change.
  
• Lots of history and advice.
• Some UNIX commands and a little about scripts (little bit of bass, little bit of rap).
• Job scheduling, procedures, functions, and the other advanced aspects of the Oracle database you're expected to use, all the time.
  
• It does not guarantee 100% of the results that you expect out of it. More than anything, it expects you to work on the aspects you're weak in, and build yourself up (bootstrapping).
  
• Since you've elected to be in the job that requires a lot of alacrity of mind, capacity for design, analysis, and the ability to think from the point of view of the customer (user), I expect that a decent enough explanation would suffice, instead of a strict proof. After all, faith is the basis of all study.
  
• The article contains very few citations; most you would have to take on faith, but I try to demonstrate my methods.
  
• As usual, this article is dedicated to Netscape (Mozilla), Adobe, WinRAR, Crimson Editor, WordWeb, iTunes & iPod, BSPlayer, and all the nicer virtual things the world has to offer.
  
• And to Richard Stevens, who taught me how to write a computer book. If it were not for him, you would be reading something like "Mary had a little lamb..."
  

Here we go.

---

ASP Technology and What's So Wrong With It

ASP is a primitive scripting technology introduced by Microsoft to supplement its Internet Information Server (IIS). It provided bare bones functionality, with support for the following scripting languages:

• JavaScript
• Microsoft JScript (now obsolete)
  
• VBScript
  

It also provides data access through ADO and ODBC connectivity. Besides these features, you can also embed Java applets, which can communicate with a database using its own (JDBC) technology. ASP was introduced by Microsoft around 1996.

From the preceding discussion the following things are clear:

• ASP is quite an old technology quite unsuitable for the new Web scenario which is based entirely on style sheets, client side script, modularized data access code, and high volumes of data requiring an explicit memory management model.
• It does not support a full-fledged language (no, not even VB). Hence,
  
• It does not support objects
• Does not have any error handling mechanism as such
• Results in buggy code that is nearly impossible to detect
• ASP is not compiled code. The asp tags are merely "interpreted" by an ISAPI filter (asp.dll) in the Web Server, which passes these script elements for processing to the database engine (or whatever designed to handle such code).
• An ASP "application" cannot be debugged at design time. (It is not compiled code).
  
• It results in lots of spaghetti code and memory leaks (if you forget to put an rs.MoveNext in a loop, the program would freeze, and there's no way out except to restart IIS).
  

More than anyone else, the folks at Microsoft knew that their scripting platform was quite a lame one, and they have been "plugging the holes" over the years. A significantly new product was the .NET platform, which Microsoft never really expected to take off as well as it has. This was allied with ASP.NET as the Web technology, and a new language - C# - also renowned as Java's evil cousin - took centre stage as the language of choice. ASP.NET supported full-fledged object oriented languages such as

• C#
• VB.NET

among others. This new platform was extensible, and it is actually the first Microsoft technology that has been ported to other architectures (other than Intel), the most notable being the Mono Project, which is a port of ASP.NET to the Apache Web Server (and thus could be run from Linux or UNIX systems).

Microsoft has finally given its long-suffering customers their due. The .NET platform has its share of admirers, but mostly it will be appreciated for making the IIS Web Server platform competitive in the face of stiff challenge from Apache (Apache, in its various versions, still has more than 60 per cent market share, down 10 percent from their highest ever share of around 70 per cent in October 2005). Whatever the reasons, Microsoft .NET resulted in the revival of a company that was previously almost written off for its lack of a programming platform (Windows is still largely coded in C, using the native Windows API).

A new Windows based database application (oh, needless to say, it is a "Web" application - which is what we say when we should actually say, "distributed application") should have these components.

• Web technology - ASP.NET
• Database - SQL Server or Oracle (or, quite recently added, MySQL)
• An IDE for development, debugging, and testing
• The programming technology - .NET

In our particular scenario, we have

• Oracle database
• ASP technology on IIS Server
• Visual InterDev for application development (debugging and testing being meaningless for ASP)

Since this is the kind of work we're doing now, we shall see what we can do with it to develop somewhat better ASP "applications".

---

Best Practices

For distributed applications, best practices remain the same regardless of the technology used. These take into account the fact that the lonesome developer - more appropriately called a lonely devil - will do more harm than good, and mostly harm nearly all the time. The first thing to guard against in a team set-up is clever programming. Do NOT appreciate or encourage clever programming.

Cryptic shortcuts never good a program make.

A good program is a readable program. A program will always be changing, as it responds to new challenges and requirements. Once written, it will constantly be in the maintenance mode until finally it is scrapped in favour of a new version. A program is never a monolith; it is organic. Unless a program changes, its usefulness will soon be lost. Being in the telecom sector, we are acutely aware of this fact.

With this cautioning, we shall see some points and discuss each in turn.

• The most important phase is the design phase (developing/collecting a robust specification after system study), with lots of discussion among the programmers and the project leader (who should remain silent most of the time - his job being to act as the resource person on the desired functionality of the application as well as the subject expert in the house). The design phase should take around 30-35 percent of the time.
  
• Coding should never take more than 20 per cent of application development time. Once the specifications have been developed, coding can begin after agreeing upon the technologies/programming techniques to be used.
  
• In coding a database application, there should be both front-end code as well as back-end code. Standalone queries passed from Web pages and executing on the database is NOT back-end code. Back-end code is secure code stored as procedures and packages. Back-end code is cached and optimized code that won't break. Unfortunately, our applications make no use of back-end code at all (the only exception being the TRA Transfer Application, which uses 100% backend code to achieve its objective - but it is not a "Web" application).
  
• Modularize the code. Do not reinvent the wheel. Write everything as functions, preferably back-end functions. If your page makes a call to the Server, better make it call on the database server, to a function. This is best for everyone.
  
• Spend some time isolating common functionality in every page that you do, and write modules for it, and compile it (even is ASP you can package it as a dll). This way you can sure that code doesn't get fragmented. And a change to one page will not affect another page without breaking something - in which case you will have to recompile the entire application to get it running.
  
• Do not continuously modify a production system. If you get requests for modification, stack it up, and when the pile gets tall enough, release a new version of the application. This is the way to go.
  
• A database application's back-end should never be modified. This portion contains the business logic, and once it is decided, it stays. SQL manipulation of a database will kill the application.
  
• Never pass ordinals as such from the front-end (Web page or Form) to the back-end. Use bind variables. Use bind variables always. Each query should be of the form
  INSERT INTO sometable (field1) VALUES (:field1); -- using bind variables
  and not
  INSERT INTO sometable (field1) VALUES (xyz); -- schoolboy programming
• Better still, have the database accept values only after performing some sort of checking.
  
• There should be some client-side validation to see if the user has failed to enter a required field; but never entrust serious data checking to JavaScript or any other client-side scripting technology. To do that you have to hack the pages, and doing that is harmful. A database knows its data requirements best, so entrust those tasks to a database. Don't even suppose that client-side script can stand-in for heavy duty database functionality such as constraints, etc.
  
• Client-side scripts are meant to ease the load on the Web Server, not the data server. (Very important point: it means, do not use client-side scripting to check data integrity or validation). Browser version and resolution check OK; range validation, definitely not.
  
• A database is expected to deal only with data. And data means the normal types such as VARCHAR, NUMBER etc. Not other symbols or tags. Do not try to redefine common uses that have been tried and tested for over 15 years.
• Document the functionality of each page (assuming it is an ASP), such as
• What the page does
• The fields (local variables)
• Bind variables (the values it receives from the Form or front-end)
• A revision history, if possible
• Have a good idea of what code you are expected to write - do not waste time writing code everyone else will write in his/her own way. Let the Project Leader worry about the co-ordination issues!
• It is a crime to have the Web Server deal with an error. Have the database raise errors, rather than letting the Web Server deal with errors. I am talking about developing a package, which will have the SQL procedures raise errors, which will be handled by the database, and the resulting message logged. It is not easy in a "Web" application, but it is certainly the way to go.
  
• If you modularize the code, then you have a good chance of being able to compile your application, or at least your module of the application. You can then actually unit test the application!
  
• The idea of unit testing is simple: your unit will be treated as a black box. It will receive some input (fields), and it will perform some manipulation and output some fields (or modify a database, etc.). In case you perform the transaction explicitly, you can have Oracle return a code saying the transaction was successful - and thus you can make your code fully modular.
  
• If you have got the flow thus far, then you will have made a pretty scalable and useful application ruled by the business logic, and with few security holes. Testing and debugging will be very easy, and fruitful.
  

Next we shall look at some database aspects (from the developer's point of view).

---

Database Best Practices

A Web developer is expected to have expertise in html, scripting, and of course, Web technologies such as .NET or PHP or Java. However, we often find ourselves writing database applications accessed through the network, and such applications often contain nearly all the database side code. That is, it will contain nearly all the code that accesses or modifies the database! As such, it is clear that we should understand the database a little better - than just treating it as a black box.


We shall deal with the nuances of database programming in a subsequent post. Until then, cheerio!