Wednesday, November 07, 2007

What should you say to students when teaching Oracle?

A few weeks ago I teached a group of students some parts of the Oracle Database.

When talking about new features and the things of Oracle I think are really good, I started to talk about Oracle Application Express. On the fly I did a demo of APEX and talked about Rapid Application Development. The students were really attentive and sometimes I heard an "ohhh" ;-)

The demo was the start of a very interesting discussion. The students have a course called "Rapid Application Development", it's not APEX, it's .NET. When I heard that, I thought "fine", how do they call APEX then: "High Speed Application Development", "Rapid Application Development times 10", "Application Development at Warp Speed", ... ?

But that wasn't the most interesting part yet! Apparently the students had to do a project: their development a part in Java and a part in .NET on an underlying Oracle database.
The students thought it a good idea to create packages and put the logic inside the database. They showed their teachers and they gave this feedback: "The database is for your data, the logic you put inside your application (middleware/java/.net)"... I'm not sure about you, but I disagree with that statement!

I prefer to have as much logic/validations/business rules etc as close to my data, in other words, if I can do it in the database, I will do it. For me it doesn't make sense to only use the database as a data store. What changes the most? I would think the front-end! Do I always need to recreate all the logic again? If today I use Java, at the same time I use APEX, C# and Flex on the same data, I need to write my logic four times? And will the implementation be exactly the same? I come from the DBA side and in general DBA's prefer to know what you're doing. If the same package/procedure/function in the database is called by all development environments we know exactly what's happening. It can be tuned appropriately, statistics can be kept, auditing etc. I even didn't talk about data consistency...

One of the arguments was "performance": if you always need to go back to your database server, too many round trips... Ha! What about the performance of putting everything in recordsets and treating these recordsets? Maybe I can do the same in one SQL statement by using analytical functions? Why not using materialized views? Who does best to handle the data in the most performant way?
Of course I also use some client side things to check if a field is filled in, so I avoid a server roundtrip, but it's not true for data/logic etc.

There're so many great features in the Oracle database that people using the Oracle database as just a data store are not knowing and not using. It's a pity.

Nevertheless, we had a lovely discussion about this topic, but afterwards I thought I was maybe too hard for the teachers. It's a fact there're two groups of people out there. In my experience a lot of JAVA developers are doing it the "put logic in your middleware/app" approach, where as people coming from the database prefer to have their logic as close to their data.

My question... What should you do when you get such a question (logic inside the database or not)?


Paweł Barut said...

Hi Dimitri,

I fully agree with you that logic and data manipulation should be done as close to data as possible. I also prefer to write business logic layer in PL/SQL at DB side, and have client software as thin as possible.

Cheers, Paweł

Enrique Orbegozo said...

Hi Dimitri,
I couldn't agree more. After a decade being an Oracle DBA, I've seen the problems derived of putting the logic outside of the database, it's far better to use stored procedures. Now I'm teaching Oracle courses and when we talk about the subject of where the code goes? I say: "on the database of course"

Anonymous said...

Hi Dimitri,

What you know about application development is limited with Oracle and its tools such as APEX. Probably You know nothing else and never been in an application development. You are a DBA.

I am working as an application developer for an enterpise company having ~10 OLTP databases. Here is my experience shortly.

1.Working with stored procedures with dblinks makes application develoment terribly difficult and test.

2.Its nearly impossible to refactor stored procs. and maintain them.

3.Its difficult to version stored procs and apply continues integration.

4.Its impossible for two developers working on a same package.

Dimitri Gielis said...

Hi "Anonymous",

Thank you for your feedback.

Before I do a proper reaction I prefer to know what development language you use?

Or can I somewhere see what your experience level is?