Code reusability in oracle pl/sql

Again today when checking out tom’s blog I lead me to this post in Tayler Muth’s Blog. This sounds to be an interesting topic. So I thought for a while and this is what came up to my mind…

Code reusability in oracle pl/sql

First to start with pl/sql is procedural language which is not object oriented. Once said that it implies that this language is not familiar with the concept of inheritance. I have been using pl/sql for quite some time. It’s a great programming language. It would be even better if it had the class concept in it.

Class concept would come handy when we want to build a framework which can be utilized over and over.

Currently I am working on a masking utility. We have different masking algorithms say…

Algorithm 1, Algorithm 2, Algorithm 3 etc.

The general framework of this masking is something like this.

1. Get the field from user which needs to be masked.
2. Get the master table for that field.
3. Load the distinct data for the field from master table into a staging table
4. Apply your masking logic on the staging table.
5. Find all the transaction tables that have this field in it.
6. Using the staging table update all the transaction table.

Here this is a very good candidate for class concept where I can have a parent class which will have the entire frame work of what I have given in six steps. And I can have one child class for each algorithm and in each child class I can just over write the step four where I can apply my masking algorithm. But unfortunately we don’t have it. Hence I went for package, Use some CASE statements and hooked in my algorithms and made it as optimal as possible. I basically hate writing lot of code. If Oracle can introduce OOP(Object Oriented Programming) in PL/SQL it would be of great use and even more flexible.