Creative Solution: Batch data processing using PL/SQL
When working on an application which is part of an ecosystem, data needs to transit throughout the ecosystem from one application to another. In most systems, the common way of sharing data is through data file where data is extracted from one system and then imported to another system.
There are several ways and methods to import a data file to a database and in most systems this is performed using batch processing handled by the application like for example Spring Batch.
One creative solution is using use PL/SQL with directory and organization external tables. In my case, Oracle contains all the features required to implement this functionality. Basically the idea is to create an external table which maps onto the directory of the data file.
The scripted table below will be executed in the Procedure Call once the data file is loaded and ready to be imported.
The structure of the external table should be the same as your target table. The advantage of doing this you can preprocess the data to the external table with proper validation before performing an insert in your target table. This reduce the risk of having transaction failures by validating the data beforehand.
The 3 steps approach are:
a) Data file from external system is loaded in the data repository.
b) Using PL/SQL load the data file in an external table.
c) Using PL/SQL perform perform data pre-validation if necessary.
d) Using PL/SQL insert the data from external table to target table.
e) Drop the external table after the transaction.
This pattern for data import is really helpful when you have many data processing to do. Moreover, it is completely independent of the application and focus solely on the data. If you want to dissociate the application from the batch processing, i believe this is an ideal solution since it avoid risk issues related to memory leakage, database connectivity, transaction handling.
These issues most of the time are introduced when data processing is performed at the application level and also may affect the whole application if the conception is not done properly.