A couple of years ago I helped put together a discussion forum application using the PL/SQL Web Toolkit. We released two versions of it and put it up on sourceforge, and for a while it was used as the internal discussion board within our company.

If you're familiar with PL/SQL, using the Web Toolkit is actually quite fun, and it's pretty easy to put together database-driven applications without too much overhead or initial work. Sometimes, however, rather than coding absolutely everything from scratch, it's good to have a framework to work within, and some method of separating the presentation code from the underlying application logic, and I was therefore interested to read a recent Amis Blog posting about a 'Model-View-Controller' approach for coding PL/SQL web applications.

The article refers to a paper by John Flack entitled "MVC Development in PL/SQL" and is available for download from the ODTUG website. According to the paper introduction:

"I’ve been learning Java and J2EE development lately and starting to use JDeveloper. We’re heading in that direction in our strategic plan, but we still have a lot of PL/SQL web applications around. Some are handwritten, some generated by the Oracle Designer Web PL/SQL generator, and many are written with a combination of PL/SQL Server Pages (PSPs) for the front end and handwritten PL/SQL for the back end.

During my education in J2EE, I was introduced to a concept that came from the object-oriented, Smalltalk world, called the Model-View-Controller framework or MVC. MVC is a way of dividing an application into three parts, so that the parts can be developed and maintained as separate modules. Dependencies between the parts can be kept to a minimum, and there is a maximum possibility for reuse. The view is the user interface, the model is the database interface, and the controller is like a train dispatcher, deciding which view is to pick up a payload of which data and transport it to which database objects and then back again with data from the database to display to users.

MVC is to me a refreshing new way to think about application development. While MVC was born in the Smalltalk world and developed in the J2EE world, it is not tied to the Smalltalk or Java languages. There is no reason that it can’t be used with any programming language and any development environment. This includes PL/SQL. In fact, it is the fulfillment of some ideas that we were already using. So with half of my mind still in the PL/SQL world and half in the new world of J2EE, I began to think about how to bring MVC from my new world into my old world.

Two of the parts of MVC were already part of our thinking about how to do web development in PL/SQL. The model provides an interface to the database and the business rules that govern the data as it passes through the database. We had already been successful using table application programming interfaces (TAPIs) as our model, both TAPIs generated by Oracle Designer’s Server Generator, and handwritten TAPIs. The view provides the front-end user interface to the application, and in our applications takes the form of PSPs, handwritten PL/SQL with Web Toolkit calls, and some plain HTML pages. The controller portion of MVC on the other hand was totally new to me. In J2EE, the controller sits between the model and the view, taking instructions from the user from the view, executing them through calls to the model, then taking the results and displaying the appropriate next view."

Model -View-Controller splits your application up into three logical parts:

• The Model, which is your database or data store
• The Controller, the central part of the application that controls what data is displayed, when it is displayed, and contains the application logic
• The View, one or more pages/screens/any other 'view' on the data, which are summoned by the controller and pass information and control back to the controller whenever the user initiates an action

In our case then, we'd have Oracle as our model, a package or procedure which is our controller, and another package or series of procedures that output HTML to form our views. Each view displays a web page, and when the user requests an action, the page passes control back to the controller, together with any values the user enters or selects. The problem you get, however, with the PL/SQL toolkit, is that the controller procedure expects a fixed number of parameters with a fixed set of names, and your set of views will send back all manner of parameter fields which contain data from the forms on the page. If you try and call the controller procedure with the wrong set of parameter names, it'll just error, presenting a problem if we want to write a single controller procedure that can handle input data from all the views.

Luckily for us, the paper suggests an elegant way around this:

"Fortunately, there is an alternative to passing data from a form to an action procedure in one parameter per form field. By identifying the procedure in the URL with a leading exclamation point, you can tell mod_plsql, the PL/SQL module that provides the interface between the web server and stored procedures, to use flexible parameter passing. So, the URL becomes “http://www.mycompany.com/pls/mydad/!myprocedure”. With flexible parameter passing, the action procedure receives two arrays (PL/SQL “index by binary_integer” tables) of parameters instead of individual parameters.The first parameter array, name_array, contains a list of parameter names, which are matched one-to-one with the second array, value_array, containing the value of each parameter. Now, all web forms and controller procedures can take the same two arrays as parameters, so any form can use any controller as its action procedure, and any controller can pass data to any form."

You can read more details, including the PL/SQL code for decoding the parameter arrays, and details of how the controller procedure was implemented, in the main article.

Lastly, If you're interested in general around working with MVC and Apache Struts with Oracle's products, you might also be interested in this previous posting where I looked at building 9iAS Portal portlets using MVC and struts, and this article by Duncan Mills on Understanding MVC.