Inject PL/SQL, Turbo-charge your SOA!!
In this age of N-tier applications, SOA and other cutting edge technologies, PL/SQL has been largely ignored and is considered legacy. Its main use has been relegated to batch processing and Oracle Applications programming. In fact, most modern puritan SOA architects recommend not to use PL/SQL at all as it becomes difficult to manage the logic being embedded in the database. In n-tier architectures where the business logic is supposed to be embedded in the application logic layer, logic embedded in database PL/SQL is almost considered a blasphemy.
I feel that even in modern SOAs it makes real sense to embed logic in PL/SQL for specific scenarios.
Consider a scenario where an 3-tier application is being designed wherein the User-interface layer interacts with the middleware layer based on SOA which in turn interacts with the database to access & manipulate the relevant data.
In the above scenario there is a lot of performance impact for any request made from the user-interface as it has to go through the SOA abstraction layer to reach the database. In this scenario, having additional logic to perform data processing like filtering, aggregation and transformation in the SOA middleware will really hit the performance. SOA tools are not designed to perform data aggregation and complex transformations required in this scenario. Unfiltered Data will need to be fetched from the database and additional logic mostly in Java needs to be written to aggregate the data and perform the complex transformations.
This is where PL/SQL can help Turbo-charge the performance of the SOA-
1. Moves the processing to the database machine and reduces performance hit on application server
2. PL/SQL is designed for performing these data processing operations optimally
3. PL/SQL allows a much higher flexibility from a language perspective in processing the data
4. PL/SQL being close to the data source (within the databse) reduces need for network bandwidth
5. It is very easy to integrate PL/SQL in SOA using adapters
The drawbacks of using PL/SQL are -
1. Source code is embedded in Database and is tightly coupled to the table structures making change a painful affair
2. The monitoring of the PL/SQL code is difficult
3. It is proprietary to the database and hence difficult to port from one database to another
Inspite of the above drawbacks I strongly advocate the use of PL/SQL for extensive data processing requirements in an SOA.
