Blog: Converting embedded Oracle PL/SQL code to C# with an automated converter

Our customer, a global provider of risk management products and services, was looking to reduce maintenance costs by migrating their international Oracle application to Azure. Their source monolithic application used the PL/SQL code embedded into the Oracle database and required a vast modernization effort.

Particularly, we faced a challenging task of converting a huge amount of business logic. This proved to be a time-consuming and labor-intensive process. The DB Best team created a fully automated PL/SQL to C# conversion tool, which allowed us to accomplish the project in an efficient, secure, reliable, and cost-effective way. The following blog post walks through the DB Best approach and demonstrates the sheer power of our technology.

Original customer’s architecture

The customer’s original Oracle database included business logic written in PL/SQL. The source application, which interacted with the Oracle database, didn’t have any business logic. All operations were executed by the means of the PL/SQL code embedded into this database. This customer’s application had monolithic architecture. Therefore it was hard to support, develop, and maintain. So, we had to break this monolith and develop a whole new architecture for our customer’s application.

The original PL/SQL code had one more critical downside. PL/SQL does not support object-oriented programming (OOP) paradigm, which is the standard for modern software. As a result, it becomes hard to describe the application flow and properly test it.

Then the DB Best solution architects worked closely with the customer’s team to understand the business logic of the application. As a result, we came up with a future-state architecture design of the customer’s application in the cloud. This new architecture utilized the modern microservices approach. So, we had to move the business logic from the Oracle database into the application layer.

The following image shows a general view of the original and new application architecture.

PL/SQL to C# converter

To accomplish this proposed architecture upgrade, we had to rewrite the business logic from PL/SQL to C#. Considering the huge amount of the source code, the manual application conversion may take years.

Automating the migration with PL/SQL to C# converter

We conducted thorough research, willing to discover an automated conversion technology. However, this search failed, and that is why we decided to develop the PL/SQL to C# conversion technology ourselves. And now we are happy to introduce DB Best PL/SQL to C# converter.

Our automated tool uses Oracle PL/SQL code as an input to generate C# code. Then this tool automatically converts up to 100% of the source PL/SQL code, including stored procedures, functions, and packages. This allows for streamlining the application modernization process. The following video demonstrates how our PL/SQL to C# converter works.

Let us now take a look at the output of DB Best PL/SQL to C# converter. We use the following Oracle PL/SQL package as a source.

PACKAGE BODY Form_QAFE_FORM2 IS
FUNCTION ON_CHECK_DELETE_MASTER_1 (P_COURSE_NO IN NUMBER)
  RETURN sys_refcursor
IS
  lv_Return sys_refcursor;
BEGIN
  OPEN lv_Return FOR
  SELECT 1
    FROM SECTION S
      WHERE S.COURSE_NO = P_COURSE_NO;
  RETURN lv_Return;
END;
END Form_QAFE_FORM2;

Our PL/SQL to C# converter creates the following code.

public class FORM_QAFE_FORM2: OraclePackage
{
  protected FORM_QAFE_FORM2(OracleConnection connection): base(connection)
  {
    this.connection = connection;
  }
  public OracleCursor ON_CHECK_DELETE_MASTER_1(OracleDecimal P_COURSE_NO)
  {
    OracleCursor LV_RETURN = new OracleCursor (connection);
    LV_RETURN.Query = @"
SELECT 1
FROM SECTION S
  WHERE S.COURSE_NO = :P_P_COURSE_NO"
;
    LV_RETURN.AddParameter(new OracleParameter (":P_P_COURSE_NO", P_COURSE_NO));
    return LV_RETURN;
  }
}

Benefits of the converted code

The DB Best automated approach streamlines the whole modernization process at times. However, there’s still a lot of work for developers to do.

We then transform the output of PL/SQL to C# converter into microservices running in the Azure cloud. This microservices architecture provides for increased performance and makes the code perfectly maintainable.

From the performance point of view, the PL/SQL code embedded into the Oracle database works blazing fast with the data. So, we needed to ensure that the converted code demonstrates an equal or higher level of performance. Our experienced cloud solution architects accomplished this challenge. However, after the customer moves their Oracle database into the cloud, alongside with the microservices, the overall system’s performance will improve even more.

Customer’s advantages

Many organizations that have been using Oracle for over 10 years, are now taking courses on migration and modernization. Particularly, adopting the Azure cloud platform provides our customer with a rich set of future modernization opportunities. They include:

  • The ability to switch to a new database platform, like Azure Database for PostgeSQL or MySQL
  • Simplicity in adding new features to the application
  • Establishing a proven CI/CD process for instant delivery of updates

After completing the modernization effort, our customer enjoyed greater application flexibility, scalability, and improved performance.

Take advantage of DB Best application modernization services

We can tailor this proven PL/SQL conversion technology to meet your needs. For example, if you decide to move the application’s business logic from Oracle PL/SQL to Java, we can use the same approach and a slightly modified set of automation tools. Contact us to make use of our vast application modernization experience.

Share this...
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin