PDA

View Full Version : Oracle & Web Development

Murphy
04-13-01, 22:02
Hello,

Any advice on how to structure Oracle for use in a 3-tier web application? I have always used MS SQL Server/COM/ASP and am really intrigued with packages and stored functions. I'm thinking that it would be best to use stored procs everywhere I can. I would also like to cache some common data in Oracle memory so as to keep from having to select the same data for each time the ASP is built. I also would like to structure things so that adding new fields to tables has a little impact as possible. So could I a package to have a level of abstraction between the producer and consumer?

thanks, MurF
Daniel A. Morgan
04-14-01, 02:28
Nothing special is required to connect Oracle to a web front end. Your most efficient coding practice will likely be procedures inside of packages as you get all of the advantages of stored procedures along with the ability to have global variables and fewer objects.

Oracle normally, and without doing anything special, caches both SQL code and data with one proviso. That you have set up your instance properly (defining appropriate parameters in the init.ora file) and that you write your SQL using bind variables such that each time Oracle sees a query it recognizes it rather than treating it like a new query that must be handled from scratch.

From the standpoint of adding new fields to tables I have two comments. First the overhead is so minimal you couldn't possibly notice it in a web app. And second, you should not be modifying production tables at a time when it could.

The most important advice I can give you with respect ot Oracle, since you are coming from a SQL Server background is to NOT assume that because Microsoft says its product is similar ... that it is. The differences are staggering. And the more you know Oracle the more you will come to appreciate the differences and their magnitude.

Daniel A. Morgan
Murphy
04-15-01, 13:00
Thanks for the reply. I wanted to make one follow up comment because I wasn't clear in my original post. I wanted the of adding a new field to a table to be as little impact to the over all system as possible. I'm not talking just in terms of end user but rather the entire system. The fewer modules and code that also has to be changed the better. I am currently supporting a legacy system that was poorly written. They did such things as SELECT * and retrieve the fields by position rather than by name. They also used INSERT VALUES without corresponding field names. And probably worse of all they scattered the database access code through out all 3 tiers. What this means is that any alterations to the table means I have to make changes through out the application. You can bet your bottom dollar that I don't make these changes in production without first doing it in development, moving through QA and then migrating it into production.

Now that we are moving away from MS SQL Server it will give us a chance to do things right. It wasn't MS SQL Server's fault that the original developer didn't design the system correctly. The move to Oracle is more political than technical. I wanted to take advantage of the things that Oracle can offer and to make sure that I am designing the database as optimally as possible. So getting back to adding a new field I was hoping that by encapsulating every thing in a package I could at least reduce the number of changes that would be made on the database side. I was thinking that the specification should be in the package and the actual implementation in a separate body. Or should it all be combined into a single package? The app is internal but may be opened up to other corporate applications.

thanks, MurF
Charles McDonald
04-19-01, 07:21
Murphy

You probably know this, but any stored procedures referencing tables that
are modified will need to be recompiled.

Cheers Charles