Daily Oracle

short Oracle take-away tips that you can put to use in under an hour.

Thursday, September 09, 2004

Adopting an MVC approach to Oracle Development

MVC is very popular in Java for separating the model from the view from the controller.
Its advantage is that it segments an application to distinct components - Model, View, Component.

We can adopt this segmented approach for pl/sql development.

Let's take an example that we want to build an application that will compare 2 tables for differences and populate a table for the differences.

Let's say the tables have these common columns
k1, k2, a1, a2

Step 1 - The V

Build a view based on a select statement that will identify the difference.

In our example the sql might look like this:

select k1, k2, a1, a2
from table1
select k1, k2, a1, a2
from table2

We build a view for the above sql statement.
Let's call the view difference_v.
The view is also encapsulating our business rule for what differences are.

Step 2 - the business work

A package that uses the view
with pseudo logic like this:

for each row in difference_v
insert row info into difference_table

What have we achieved so far?
We have :
1. segmented the difference business rule in the difference_v
if the business rule changes, we just change the view creation select statement

2. segmented the 'what to do with difference' in the package in Step 2

Using this segmented approach, we can start with step 1 with certain assumptions which can be revised later and move on to Step 2.



Post a Comment

<< Home