How I exposed an entire PL/SQL based application as REST APIs in less than a week and saved tons of money for my company
Back in the good(?) old days PL/SQL applications than ran inside an Oracle DB were quite the thing. All the logic was written in the DB, close to the data. And everything ran fast. Stuff like maintainability, lighter open source databases, modern APIs, micro services etc. only emerged later.
I used to work for a company with such an application and the company encountered a problem – an old PL/SQL based application was in an un-maintainable state (people who even knew PL/SQL became scares), the application could not be offered to new customers due to its old technology and even existing clients demanded modern REST APIs to integrate with the system. An estimation of manually exposing REST APIs for each PL/SQL procedure (we had almost 10000 of those) was a year of work for three developers – three man years! But during this time the application will still be un-sellable, and existing customers might not be patient enough and dump us for shinier application – the situation looked dire… Then I came and said – I can expose all PL/SQL procedures in less than a week! And I did J. This is how:
The secret was to automate the entire process, and this was doable thanks to the fact that java enables us to:
a. Investigate PL/SQL procedures that live in a DB (names, parameters, return value types etc.)
b. Call PL/SQL procedures and process the returned data
c. Easily generate java classes
Investigating PL/SQL procedure was done using JDBC. To get all procedures I needed to connect to the DB, get the metadata and select the desired procedures:
Get the connection:
conn = DriverManager.getConnection(“jdbc:oracle:thin:@" + dbUrl + ":" + dbName(),
username, password);
Get DB metadata (see java API for details):
DatabaseMetaData meta = conn.getMetaData();
Query for procedures (in this case we extract data for a specific catalog and schema, but getting all procedures):
ResultSet resultSet = meta.getProcedureColumns(catalogName,schemaPattern, "%",
"%");
Iterate the data and save it for later use. The code here is simplified, so we just assign the info to variables. In practice we accumulate procedure data and identified the input and output parameters information.
while (resultSet.next()) {
procadureCatalog = resultSet.getString("PROCEDURE_CAT");
procadureName = resultSet.getString("PROCEDURE_NAME");
remarks = resultSet.getString("REMARKS"));
position = resultSet.getInt("ORDINAL_POSITION");
// input or output param (or both)
colType = resultSet.getInt("COLUMN_TYPE");
colName = resultSet.getString("COLUMN_NAME"));
dataType = resultSet.getInt("DATA_TYPE");
dataTypeName = resultSet.getString("TYPE_NAME");
length = resultSet.getInt("LENGTH"));
}
This returned some useful details on each procedure – its name (which was meaningful, hopefully), its parameters and their type, its return values and even all the comments it has, if available. This valuable info will be used when we call the procedure. A useful class we used is @NamedStoredProcedureQuery (which in turn uses @StoredProcedureParameter). These classes allow us to describe a stored procedure in java in order to call it through JPA. We generate them using the information we extracted before from the DB metadata and the outcome looks like this:
@NamedStoredProcedureQuery(
name = "calculate",
procedureName = "calculate",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "itemPrice"),
@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "itemQuantity"),
@StoredProcedureParameter(mode = ParameterMode.OUT, type = Double.class, name = "total")
}
)
Once we generated that, calling the procedure is a breeze using JPA’s :
StoredProcedureQuery query =
this.em.createNamedStoredProcedureQuery("calculate");
query.setParameter("itemPrice ", 1.23d);
query.setParameter("itemQuantity ", 9.5);
query.execute();
Double total = (Double) query.getOutputParameterValue("total");
OK, so we now we know how to generate calls to stored procedure! Exposing this as REST is easy. Since we can generate a REST controller that will invoke the call. It is up to you to decide if you want to invoke the call directly from the controller or use an N-Tier architecture and add service and DAO layers in between, but the principal is the same. For example, we can generate:
@RequestMapping("/math")
@RestController
@CrossOrigin
public class MathController {
@GetMapping("/calculate")
public Double calculate(@PathParam Double itemPrice,
@PathParam Double itemQuantity) {
return ... (call the calculation described above)
}
}
We can also generate unit tests easily as well, and read the input parameters values from exce, to enabe easy testing, making sure nothing got lost in the translation.
The code generation itself can be done ‘manually’, by appending strings, but this is obviously less comfortable. A better approach is to use templates and some replacement library like Velocity. Even java’s MessageFormat can be handy here:
Object[] params = new Object[]{"hello", "!"};
String msg = MessageFormat.format("{0} world {1}", params);
And, if you want to live on the edge, you can always use a library like ASM or BCEL to generate classes or CodeModel to generate java source files, thus making sure the syntax is correct and the generated classes can be easily modified (adding annotations, for example) in a way that will not break compilation.
This way a few man years have turned into a week and the customers were happy, sales persons could sell the renovated application and we had time to think about the next innovation…
