How I Exposed an Entire PL/SQL-Based Application as REST APIs in Less Than a Week

Back in the good(?) old days, PL/SQL applications that 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, microservices, etc. only emerged later.

I used to work for a company with such an application and the company encountered a problem — an old PL/SQ- based application was in an un-maintainable state (people who even knew PL/SQL became scarce), 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. But during this time, the application will still be un-sellable, and existing customers might not be patient enough and dump us for a 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. This is how:

The secret was to automate the entire process, and this was doable thanks to the fact that Java enables us to:

• Investigate PL/SQL procedures that live in a DB (names, parameters, return value types etc.)
• Call PL/SQL procedures and process the returned data
• Easily generate java classes

Investigating PL/SQL procedures 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 identify 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 generate that, calling the procedure is a breeze using JPAs:

StoredProcedureQuery query =  

                         this.em.createNamedStoredProcedureQuery("calculate");

query.setParameter("itemPrice ", 1.23d);

query.setParameter("itemQuantity ", 9.5);

query.execute();

Double total = (Double) query.getOutputParameterValue("total");

Okay, so now we know how to generate calls to stored procedures! 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 principle 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 excel to enable 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 years has turned into a week, the customers are happy, salespeople can sell the renovated application, and we have time to think about the next innovation.