Monday, November 8, 2010

Converting PL/SQL Calls from Delphi to Java and JDBC

We recently had a support call from a customer who was migrating legacy code from Delphi and PL/SQL to Java and PL/SQL. Doing this isn't that easy - while you can search for a utility to help you do this, the best you are likely to find are utilities that convert Delphi to C#, and then C# to Java.

Apart from the inherent ugliness of this your converted code won't be able to speak to Oracle properly because the JDBC API is different from both Delphi and C#, so your database API calls will be nonsensical.

Even if you decide to convert your Delphi application to Java by re-coding you can still hit issues. While OrindaBuild will happily generate JDBC calls for PL/SQL and SQL you need to watch for unforeseen and sometimes unreasonable situations.

Our customer's problem was that in the Delphi Oracle API there is no support for PL/SQL parameters which are only "OUT", as opposed to "IN" or "IN OUT". As a result all their PL/SQL had been written to use "IN OUT" even in cases where the parameter in question was only an "OUT" parameter. Given that they had over 1200 PL/SQL procedures re-writing the PL/SQL wasn't an attractive option.

When they used OrindaBuild to generate a layer of Java to run their PL/SQL they hit a bug - generated code was breaking because it never occurred to us that anyone would want to pass a REF CURSOR as an "IN" parameter while working with JDBC. While this is supported if you are within PL/SQL the JDBC driver doesn't allow you to bind cursors as input parameters, so we had treated it as an error in the product.

At the request of the customer we tweaked OrindaBuild so that if a REF CURSOR parameter is defined as "IN OUT" the generated code works by passing in an empty string bound to null as a stub. If you are converting legacy Delphi or C++ to Java you should expect to encounter oddballs situations like this.