Convert PL/SQL code to Java

Convert PL/SQL Code to Java

I have a lot of conversations with persons interested in language engineering. Many of them are with readers of our newsletter, others are with clients of my consulting business.

In the last few weeks, several of these conversations were about converting PL/SQL code to Java. I am interested in this topic because I believe that the most important asset a company has is its knowledge. The problem is that sometimes this knowledge is trapped in a format that is not optimal. For example, it could be coded in an old language with poor support. By executing code transformations, or automated migrations, we can preserve that knowledge by re-expressing it using new languages. In this way we can get a better value from that important asset.

As part of our activities we implement new transpilers for our clients. However in this specific case (PL/SQL to Java migration) there are some off-the-shelf solutions available. We found two PL/SQL code transpilers, created by two different companies: IO64 and Ispirer. So we decided to found more about these tools by interviewing persons at these companies. Beside the specific migration described, we think this article could be interesting for anyone one who wants to learn more about code migrations: their advantages and how they are perfomed.

In the rest of this article we look at the two tools separately and then we conclude with some final comments.

PL/SQL to Java migration tool from IO64

Also in this case we start by taking a look at the tool in action.

Motivations

Federico:         What are the problems that motivates a company to stop using PL/SQL?

IO64:               Because of the nature of PL/SQL and its execution environment; it is fragile and expensive to integrate it into modern software development lifecycles.

It will become harder to find PL/SQL developers as convincing existing non-PL/SQL developers to dive into existing code becomes harder. Thus, PL/SQL source is relatively more prone to become deep dark blocks of business logic nightmare. Over time, this may increase development costs and licensing of the infrastructure.

Lack of tooling for static and dynamic analysis of PL/SQL source. Analysing existing source for a change can become very time consuming.

Federico:         What are the main benefits a company encounter when migrating to Java?

IO64:               Developing and testing at local development workspace with modern IDEs enhances efficiency and speeds up the overall process.

Companies will have the opportunity to gradually migrate to new Java features like generics, streams. They could even take advantage of JVM compatible languages (such as Groovy). In other words they could benefit of all the progress going on in the Java world.

Motivate developers and DevOps to innovate with new features and implement other related technologies around Java (microservices, CI/CD, Dependency Injection etc.)

Federico:         Who should consider migrating their PL/SQL code?

IO64:               You should consider if:

  • You have any kind of business logic in PL/SQL and don’t have a really solid reason to keep it that way.
  • You want to modernize your infrastructure with up-to-date technologies and want to scale up according to your future progress. Licences or special DB hardware becomes expensive as you scale up. You can get lost and spend a lot in vendor’s compatibility matrices. After migration, you can run your logic on free application servers on commodity hardware. You can even migrate to a distributed architecture with microservices by dissecting the output with clean boundaries.

Federico:          Is there a scenario in which is instead better to keep the original PL/SQL code instead of doing the migration?

IO64:               Batches. PL/SQL batch execution occurs very close to the storage itself. So neither Java nor any other environment will reach its efficiency with the same amount of metal and cable. But you can overcome this situation with distributing it over parallel environments. This will require refactoring of the output.

Conversion Process

Federico:          How much of the conversion process is automatic? How much manual effort is required?

IO64:                Our aim is to generate 100% execution ready Java code. Currently, we can generate Java code for more than 95% of the original source code. This is a conservative estimate actually; there are cases that we achieve 100% execution readiness. Degree of manual effort depends on the case. If it is pointed that some feature is missing and occurring frequently in the source, we will fix the corresponding part of the translator and reconvert. If it is rare, we advise to fix manually.

Besides, some constructs of PL/SQL are not supported by the tool;

  • Triggers, because they introduce problems for co-existence of PL/SQL and Java. (Co-existence can be desirable for large conversion projects, that’s why we leave it to the end to handle.)
  • String/Varchar, a NULL VARCHAR in PL/SQL is equivalent of “”; which is not null in Java. So in the test phases, minor manual fixes will be needed. We are also working to automatize this.

If PL/SQL input is really erroneous, the output is also erroneous.

The toolkit outputs the exact same input if it does not know how to convert it to Java (extremely rare). This causes compile error and you can directly point those locations in modern Java IDEs. This is how we spot conversion errors for the time being. We are working on an automated test framework to compare input vs. output.

Federico:          Do you offer also consulting in addition to the tool?

IO64:                We offer consulting over the toolkit itself. Some decisions are made to be more generic, to fit into generic customer requirements. Like avoiding third party libraries for runtime as much as possible, avoiding JPA, avoiding Streams etc. since the target JVMs selected by the client may not support these features or libraries. When a customer needs an output not this way but the other, we develop, parameterize and consult. This can be charged according to the level of change request.

We consult over the output. If it is pointed that some translations are not working as expected, we fix, automatize and reconvert.

We consult over any Java project, with 15+ years Java development and architectural experience.

Technical Notes

Federico:          Can you describe at an high level the architecture of your solution?

IO64:                The solution itself is called a translator in literature. Like translating any other spoken language, and it’s based on ANTLR which relies on a grammar definition of PL/SQL. There are grammars for PL/SQL out there, but they are incomplete or erroneous.

Core part is a two-pass translator. First pass is for type resolution and dependency analysis, and the second for generation itself.

On top of translator component, there is the conversion server. This manages the input and other related configuration. It loads conversion core from our servers as Linux binaries at runtime (this part has few other options like sharing the complete core source).

A frontend server across the conversion server. This part is the UI and speaks with conversion server.

The whole architecture is designed for keeping the sensitive source code at customer side and all parts can be rearranged for different requirements;

  1. On-premise;
    • Frontend server and conversion server are deployed on-premise, core binaries are loaded from our servers at runtime as .so file.
    • Frontend server and conversion server are deployed on-premise, core binaries are bundled within conversion server as .jar file. (and another option that include all source files)
  2. Service
    • Frontend server and conversion server are hosted at our side. Conversion server connects client DB remotely.
    • Frontend server and conversion server are hosted at our side. Conversion server connects to a dedicated DB  at our side as a clean start environment. This is also the combination that we host for demo at https://cc.io64.com

Options under Type 2 will result client source to round-trip at our servers.

Federico:          What are the inputs and the configuration required for your tools and what are the outputs produced?

IO64:

Input & Configuration

Conversion tool has two modes of operation. Batch conversion and in-memory mode. Batch conversion requires manually exporting all sources to a target local folder. After that point, it read all files from folder and starts converting in batch mode. On the other hand, In-memory mode requires configuration to connect directly to source DB via JDBC. By using the web interface of the tool, you can convert individual objects one by one or single special object of your choice.

Output

Conversion works on single target schema (at the time of writing). If you have an application distributed over multiple schemas, multiple conversion is needed. For single schema, the output is a single Java Project. To run that project, another separate Java Project which serves as a runtime library is delivered. This project contains the code needed for running queries and getting results, array implementations, abstract cursor implementations, date/time conversion utilities, numeric utilities, transaction management utilities, and standard library placeholders, all delivered with source code.

Standard library placeholders are a set of Java classes  that mimics functionality under SYS schema. Eg.: If you have a reference to SYS.DBMS_JOB.SUBMIT(), you will see an empty method named SUBMIT() in class com.<your_target_package_preference>.SYS.DBMS_JOB.java. You can directly wire it back to DB or integrate it to your own custom job management system which is currently in place. Additionally, we can generate automatic back-wiring if a customer needs that way. Back-wiring/Empty method selection is not parametrized currently. At the time of writing only “empty method” option is available.

The package SYS.STANDARD is a very special one within this structure. A class named  STANDARD.java is in place and all converted DB packages extend this class. It contains methods like TRIM(), DECODE(), NVL() and all other standard functions as Java equivalents. Not all functionality is implemented at the time of writing. It also extends another class called BasePackage. So that clients can add additional functionality to their converted sources from top down at once.

We also deliver an Eclipse plugin for batch fixing Unreachable code and This method must return ... errors. PL/SQL allows both of the above but Java compiler does not, and this is not rare. The plugin does not effect actual execution, since both cases are naturally handled in PL/SQL flow and thus in Java.

Federico:          Is the tool designed for some specific technical role like sysadmins or developers, or it can be used by anybody?

IO64:                We prefer a developer with PL/SQL and Java capabilities, for agility of communication. If a client does not have such a resource, the whole conversion process can be planned as a key-turn project.

Federico:          How can a company be sure than the converted code is equivalent to the original one?

IO64:                There are two important points, both assure that nothing is missed;

  1. Conversion tool outputs exactly the same input if it does not know how to convert it to Java, this causes compile error in output. (If it does not, that would be a very special piece of PL/SQL input and can only be detected at runtime/tests.)
  2. Parser throws exception if it does not know how to parse the input, and we call it grammar error. This is the main control structure and tested with millions of publicly available lines of code and commercial codes under NDA. If we point a grammar error, we will fix it, regenerate parser and reconvert. In the process, we will also implement required changes in conversion core accordingly, to generate proper Java equivalent.  This is extremely rare and is getting rarer in each iteration.

Some customers demand a solid testing structure other than the theoretical one above; A framework for capturing data trace for both PL/SQL procedure call and Java execution is currently being designed at the time of writing.

Federico:          Anything else you would like to add?

IO64:                Our competitors’ conversion application out there directly convert PL/SQL numbers to java.lang.Double. That is a big mistake. Double in Java has precision errors. We recently moved to BigDecimal which is way more complex in syntax. The toolkit converts complex math operations to BigDecimal statements for exact mathematical precision.

We can also manage to align the tool for continuous conversion; you can have PL/SQL developers writing PL/SQL code and the tool converting them to Java at a given frequency. This level of automatization is our main goal as stated before.

Regarding performance, to convert 1M LoC;

  • ~12 hours to Parse
  • ~30 minutes to First-pass
  • ~1.5 hours to Second-pass

We also made modifications to ANTLR itself to enhance performance in regard to memory, while parsing massive amounts of source. Maintainers of ANTLR are already aware of the issue and have still open bug records about it. They suggest various workarounds which may be applicable at some other cases but not ours. Since they have a different vision for the project we are not planning to integrate these changes back into ANTLR.

PL/SQL to Java Migration Tool from Ispirer

Let’s start by taking a look at the tool in action.

Motivations

Federico:         What are the problems that motivates a company to stop using PL/SQL? What are the main benefits a company encounter when migrating to Java?

Ispirer:             As a trend, many companies avoid using PL/SQL even if they are using Oracle database. Their argument is that when new demands arise in the development of their systems, PL/SQL does not have enough flexibility and functionality to perform these tasks.

Java offers much more power and flexibility to companies than PL/SQL, for instance support for networking, multithreading techniques, as well as it has all the advantages of an object-oriented programming language.

Federico:         Who should consider migrating their PL/SQL code? Is there a scenario in which is instead better to keep the original PL/SQL code instead of doing the migration?

Ispirer:             In fact, Java and PL/SQL are two different tools designed for different purposes. Therefore, the choice of the target technology depends on the company’s requirements and what kind of operations it wants to do. In many cases, both Java and PL/SQL are used simultaneously.

Federico:         Do you have any success story you would like to share with us?

Ispirer:             Splice Machine, a developer of a trending SQL-compliant database, was faced with a complex migration task that was vital to the successful deployment of its technology in an end users production environment. The main challenge was to convert several hundred thousand lines of business logic written in Oracle PL/SQL to Java. Through the cooperative efforts of our technical and business teams, and Ispirer MnMTK, Splice Machine successfully completed the conversion of the PL/SQL code to Java in a short time. As a result, the Splice Machine end users efficiently migrated from Oracle to the Splice Machine database.

You can read more about this case story at Oracle PL/SQL to Splice Machine.

About your company

Federico:         Do you offer other language conversion services?

Ispirer:             Ispirer deals with a variety of database, application, programming languages and architectures. This is the result of fast technology changes, the growing necessity for integration, consolidation as well as transformation of existing platforms.

Our main fields:

  • Migration of data, database schema and server-side business logic between major databases such as Oracle, Microsoft SQL Server, DB2, Sybase, PostgreSQL, MySQL, Informix, Progress, Teradata, InterBase, etc.
  • Conversion from various legacy application technologies, e.g. Delphi, PowerBuilder, Progress 4GL, Informix 4GL, COBOL to modern ones, such as Java and .NET.
  • Conversion of embedded SQL and database API, such as Oracle Pro*C, ODBC, ECPG, Sybase CT/LIB, etc.

Conversion process

Federico:         How much of the conversion process is automatic? How much manual effort is required?

Ispirer:             The conversion process from PL/SQL to Java is automated to the maximum. The number of manual adjustments depends on the complexity of the source code. If it is quite complex, then the level of automation can be increased by extensions. If the client needs to move from Oracle to other database, as well as to migrate PL/SQL to Java that will function with the target database, and the source code has a lot of complex dynamic SQL, significant manual efforts may be required.

Federico:         Do you offer also consulting in addition to the tool?

Ispirer:             The Ispirer team provides consultation and support at all stages of the migration process. It starts with a free, no obligation assessment. The Ispirer experts determine customer’s specific requirements, goals and give professional advice on the migration project, its scope and duration. During the entire conversion process, we provide technical support to all our customers.

Technical Notes

Federico:         Can you describe at a high level the architecture of your solution?

Ispirer:             The architecture is very simple: all the data from the database is read and saved. At the same time, an important feature is that the converter architecture allows adding or improving existing rules.

Federico:         What are the inputs and the configuration required for your tools and what are the outputs produced?

Ispirer:             Through the example of converting PL/SQL to Java: as the input, our tools require an ODBC connection to the source database, as the output we get files with Java classes (.java).

Federico:         Is the tool designed for some specific technical role like sysadmins or developers, or it can be used by anybody?

Ispirer:             We assume that such a complex and serious task as conversion is performed by a technical specialist. However, the tool itself is very easy to use. It requires minimal technical skills, for example, when setting up an ODBC connection to the source database.

Federico:         How can a company be sure than the converted code is equivalent to the original one?

Ispirer:             Testing results is no different from testing when developing an application from scratch.

Final Comments

We have seen two different tools and approaches to convert PL/SQL to Java code.

At a first look PL/SQL could seem relatively simple language, compared to general purpose languages like Java or C#. However, as we dive in real-cases scenarios we soon can see that it is not the case.

Also in this case we have seen that the Pareto’s law apply: automated migrations seem to work relatively well for the vast majority of the code, but the remaining portion is hard to tackle. It may be fully automated at some point but in the meantime it just make sense to handle that small portion manually.

Personally I think that while performing automated migrations it is very important to have a good test strategy to ensure that the resulting systems are indeed equivalent to the original ones. On this aspect there is probably some more work to do to give the extra reassurance that large clients are probably looking for.

One thing that both companies agree is that this process must be overseen by a developer or somebody with the necessary technical expertise. While the tools are easy to use, the user must understand the processes in which the tool is used: it must understand how databases and Java work. This is something that is always important to remember: tools can dramatically improve productivity, but only if they are put in the right hands. If you put a scalpel in the hand of a doctor they can save a life, put the same scalpel in the hand of an engineer and you will get just a real mess.

Beside all these considerations the important things to note is that these tools actually work and can be used to translate very large code bases (in the hundreds of thousands of lines of code) with a reasonable effort.

So, if you are stuck with a codebase written in some ancient language consider that there are automated migrations or modernization techniques that can help you making your existing software more easy to maintain.

Parsing: Tools and Libraries

Parsing   tools and libraries   cover

Receive the guide to your inbox to read it on all your devices when you have time. Learn about parsing in Java, Python, C#, and JavaScript

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit