Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines


John Flack on MVC Development Using PL/SQL
October 27, 2004
Mark Rittman

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 “!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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational