Migrate Business Logic from Database to Application for Faster Innovation and Flexibility

This post was co-authored with Alex Kirpichny and Evgenia Chernyak (from Ispirer Systems)

Introduction

Many monolith applications have business logic in the database layer in the form of stored procedures and functions. Businesses have built and maintained their applications using PL/SQL, a reliable and robust programming language. As the technology landscape advances, harnessing the capabilities of microservices opens up new opportunities for adaptability, scalability, and creative advancement. Migrating to Java allows businesses to leverage the robust Java ecosystem and adopt modern development practices. This empowers companies to adapt to changing markets and improve their software systems.

The main business and technical drivers for migrating the business logic from the database layer to the application layer are:

• Scaling: PL/SQL is a procedural language that runs on the database server. This means that scaling PL/SQL applications can be costly and complex, as it requires upgrading or adding more database servers, which increases licensing fees. Scaling the application layer is simpler and more cost effective than scaling the database.
• Flexibility: With PL/SQL, you are limited in the choice of databases, often referred to as “vendor lock-in.” With business logic in the application layer, you are not dependent on the database vendor and can switch to any other technology of your choice. For example, you can use PostgreSQL, MySQL, MongoDB, or any other database and leverage the polyglot pattern.
• Support: PL/SQL is a niche language that has a limited pool of developers and resources. Hiring PL/SQL developers can be difficult and expensive, as they are in high demand and low supply. Application layer languages like Java and Python are popular and widely used languages that have a large pool of developers and resources.
• Licensing: Oracle databases require the purchase of a commercial license. Conversely, numerous application layer languages, such as Java and Python, offer an open-source version that does not require payment of license fees. Additionally, these open-source versions often benefit from longer support cycles.

Modernizing an application to remove the business logic from the database to the application layer can be complex and time consuming. Ispirer specializes in providing solutions for database and application migration, enabling businesses to smoothly transition between various platforms and technologies. Ispirer’s SQLWays Wizard tool supports migration from various source databases to multiple targets.

Discover in this blog post how the SQLWays Wizard tool streamlines the migration of PL/SQL in the database layer to Java in the application layer. The PL/SQL logic can be converted into JDBC, Spring, or Hibernate. If the converted Java application needs to work with a different database than Oracle, the tool can convert the embedded SQL to the intended target database. Figure 1 shows a high-level block diagram of the tool with source and target systems.

Figure 1: Ispirer SQLWays block diagram

Solution Overview

1. Run an assessment

The Ispier Assessment Wizard uncovers potential migration issues. For Oracle to Java migrations, it collects source code details and provides migration complexity insights. The assessment report outlines general migration complexity levels and cases requiring manual correction. It estimates the percentage of Oracle code automatically convertible with Ispirer tools and the potential automation level if customized. The tool also generates approximate timing estimates for code migration. A sample assessment report is shown in Figure 2.

Figure 2: Sample of estimates generated by the tool

After the assessment process is done and the complex cases are identified, the possible solutions are discussed and approved by the stakeholders.

2. Install and run the SQLWays wizard tool

The SQLWays Wizard tool can be downloaded from Ispirer’s website. A license is needed to run the tool. Obtain a free 30-day demo license for hands-on testing by submitting this form.

Open the SQLWays Wizard tool, which displays the Welcome screen (Figure 3). Select the project directory and the export directory.

Figure 3: SQLWays Wizard – Welcome Screen

Provide the ODBC connection to the source database along with user credentials with the required permissions.

3. Establish the source options

Choose the source and enter the username and password. In this example, it is the ODBC connection to the source database (Figure 4).

Figure 4: SQLWays Wizard – Source Options Screen

4. Establish the target options

The tool will ask for the details of the target application. Select Java from the dropdown (Figure 5).

Figure 5: SQLWays Wizard – Target Options Screen

5. Select the objects to convert

The tool will display the database objects that are available in the source database. You can filter and select the database objects that you need to migrate (Figure 6).

Figure 6: SQLWays Wizard – Select Database Objects

6. Tune the conversion options

Select any desired settings to customize the migration, for example, data type mapping, name conversion, or reserved word replacement (Figure 7).

Figure 7: SQLWays Wizard – DDL and data options

7. Confirm your selections

The tool displays a summary presenting the migration options selected in previous steps (Figure 8).

Figure 8: SQLWays Wizard – Summary page

 8. Start the conversion

Select the Start Conversion button on this page to initiate the conversion and import processes with the tool. Once the migration process is complete, a report page is displayed (Figure 9). The full report can be reviewed by selecting the Review Full Report button.

Figure 9: SQLWays Wizard – Migration Reports page

Detailed information about the converted objects, failed objects, and errors (if any) can be found in these reports (Figure 10).

Figure 10: Sample SQLWays report

After the conversion process, locate the Java code that was generated by the tool in the directory that was specified on the Welcome screen (step 2). These files can be imported into an IDE. The following steps will guide you through the process:

1. Create an appropriate workspace in an IDE suited to the target technology (e.g. IntelliJ IDEA, Eclipse).
2. From the result folder, choose the converted files and folders and add them to the created workspace.
3. The result folder also contains the “IspirerFramework” folder or additional files with classes. Add them to the workspace as well. These are part of the framework provided by Ispirer. It extends the target technology to enable certain features of the source codebase. For example, it includes a JDBC connection class to help with connecting to the database. It also includes a SQL error and exception handler class.
4. Execute the project.

You can further fine-tune and customize the SQLWays Wizard tool by creating new conversion rules tailored to the specifics of the source.

Examples

Example 1: In this example, Oracle PL/SQL code is converted into Java code.

When a %ROWTYPE declaration is used with the variables, a separate class is created, containing fields that correspond to table columns. When a %ROWTYPE declaration is used with a cursor, a separate class is created, containing fields that correspond to fields of the SELECT statement of the cursor.

PL/SQL Code:

CREATE TABLE T_RTU 
(    
   RTU_ID NUMBER(10,0), 
   RTU_BC VARCHAR2(12),
   RTU_AD DATE
)
/
CREATE TABLE T_PRD 
(   
   PRD_PC VARCHAR2(12) NOT NULL ENABLE, 
   PRD_PD VARCHAR2(240)
)  
/
...                                
CURSOR C1 IS
  SELECT A.*, PRD_PC
     FROM T_RTU A, T_PRD
     WHERE RTU_ID = P_RTU;
   C1REC C1%ROWTYPE;
...

Converted Java Code:

class C1 {
   Long rtuId;
   String rtuBc;
   Timestamp rtuAd;
   String prdPc;
   public C1() {
   }
   

   public C1(Long rtuId, String rtuBc, Timestamp rtuAd, String prdPc) {
      this.rtuId = rtuId;
      this.rtuBc = rtuBc;
      this.rtuAd = rtuAd;
      this.prdPc = prdPc;
   }

   public C1(C1 c1) {
      if (c1 != null) {
         this.rtuId = c1.rtuId;
          this.rtuBc = c1.rtuBc;
          this.rtuAd = c1.rtuAd;
          this.prdPc = c1.prdPc;
      }
      else {
         this.rtuId = null;
         this.rtuBc = null;
         this.rtuAd = null;
         this.prdPc = null;
      }
   }
}
...

C1 c1rec = new C1();

Example 2: PL/SQL code that uses cursor:

PL/SQL Code:

CREATE PROCEDURE PROC_NDH (P_RTU T_RTU%TYPE) IS
CURSOR C1 IS
   SELECT A.*, PRD_PC
      FROM T_RTU A, T_PRD
      WHERE RTU_ID = P_RTU;

C1REC C1%ROWTYPE;

BEGIN
   OPEN C1;
      FETCH C1 INTO C1REC;
   CLOSE C1;
END;

Converted Java Code:

try {
   stC1 = mConn.prepareStatement("SELECT A.*, PRD_PC \r\n" +
            " FROM T_RTU A, T_PRD \r\n" +
            " WHERE RTU_ID =  ?");
   stC1.setBigDecimal(1, pRtu);
   rsC1 = stC1.executeQuery();
   fsC1 = rsC1.next();
   if (fsC1) {
      c1rec.rtuId = rsC1.getLong(1);
      c1rec.rtuBc = rsC1.getString(2);
      c1rec.rtuAd = rsC1.getTimestamp(3);
      c1rec.prdPc = rsC1.getString(4);
   }
   rsC1.close();
   stC1.close();
}
catch (SQLException se) {
   SQLCursorHandler.getInstance().handleExeption(se);
   throw se;
}

Example 3: Conversion of a GOTO statement

Since Java does not support a GOTO statement directly, the tool converts it to a labeled break and continue statement.

PL/SQL Code:

IF name_in = 'KL500' then
   GOTO default_number;
ELSE
   GOTO increment_number;
END IF;
<<default_number>>
cnumber := 0;

<<increment_number>>
cnumber := cnumber + 1;

Converted Java Code:

incrementNumber: {
   defaultNumber: {
      if ("KL500".equals(nameIn)) {
         break defaultNumber;
      }
      else {
         break incrementNumber;
      }
   }
   cnumber = 0;
}
cnumber = cnumber + 1;

Example 4: Oracle Nested functions and procedures

Oracle Nested functions and procedures can be implemented in Java as method-local inner classes. In Oracle, nested functions and procedures can use and change variables of outer functions, procedures, or packages. In the generated Java code, such variables are passed into the method of inner class with a MutableObject wrapper.

PL/SQL Code:

CREATE FUNCTION f_tp RETURN NUMBER IS n_en NUMBER(5);
PROCEDURE p_nest(p_cag IN NUMBER) IS v_cnt NUMBER := 15;
BEGIN
   n_en := v_cnt * p_cag;
END;
BEGIN
   p_nest(5);
   RETURN n_en;
END;

Converted Java Code:

public BigDecimal spFTp() throws Exception {
   class NestedFunctions {
      public void pNest(BigDecimal pCag, MutableObject<Integer> mutablenEn)
            throws Exception {
         Integer nEn = mutablenEn.getValue();
         try {
            BigDecimal vCnt = new BigDecimal("15");
            nEn = vCnt.multiply(pCag).setScale(0, RoundingMode.HALF_UP).intValueExact();
         }
         finally {
            mutablenEn.setValue(nEn);
         }
      }
   }
   Integer nEn  = null;
   NestedFunctions nestedFunctions = new NestedFunctions();
   MutableObject<Integer> mutablenEn = new MutableObject<>();
   mutablenEn.setValue(nEn);
   nestedFunctions.pNest(new BigDecimal("5"), mutablenEn);
   nEn = mutablenEn.getValue();
   return BigDecimal.valueOf(nEn);
}

Example 5: OUTPUT parameters

By default, OUTPUT parameters are returned as Map. However, there is also the possibility to convert OUTPUT parameters to MutableObject wrapped parameters. As another option, you can return OUTPUT parameters using Get methods.

PL/SQL Code:

CREATE PACKAGE BODY pkg_fn_out_params IS 
   PROCEDURE bj_exen IS 
   var1 NUMBER;
   var2 NUMBER;
   var3 NUMBER;
   res NUMBER;
   BEGIN
      res := proc_call(var1, var2, var3);
   END;
   FUNCTION proc_call(
      par1 IN OUT NUMBER, 
      par2 IN OUT NUMBER,
      par3 Number
   )
   RETURN NUMBER IS
   BEGIN
      par1 := 1;
      par2 := par3;
      RETURN 1000;
   END;
END;

Converted Java Code:

public void bjExen() throws Exception {
   Map<String, Object> outDataFnToCall;
   BigDecimal var1 = null;
   BigDecimal var2 = null;
   BigDecimal var3 = null;
   BigDecimal res = null;
   outDataFnToCall = fnToCall(var1, var2, var3);
   var1 = (BigDecimal) outDataFnToCall.get("par1");
   var2 = (BigDecimal) outDataFnToCall.get("par2");
   res = (BigDecimal) outDataFnToCall.get("RET_VAL");
}

public Map<String, Object> fnToCall(BigDecimal par1, BigDecimal par2,
         BigDecimal par3) throws Exception {
   Map<String, Object> outData  = new HashMap<>();
   try {
      par1 = new BigDecimal("1");
      par2 = par3;
      outData.put("RET_VAL", new BigDecimal("1000"));
      return outData;
   }
   finally {
      outData.put("par1", par1);
      outData.put("par2", par2);
   }
}

Conclusion

Migrating business logic from the database layer to the application layer opens doors to improved scalability, flexibility, support, and licensing cost savings. However, this can be a complex endeavor for companies with years of PL/SQL logic embedded in their systems. Ispirer provides an end-to-end solution to streamline migrations from Oracle PL/SQL to Java through their SQLWays Wizard tool. The wizard automates assessment, code conversion, and generates detailed migration reports. This accelerates the transition and reduces risk, empowering businesses to modernize their applications. With the proper planning and the right migration tools, companies can successfully transform monolithic systems into adaptable, scalable microservices architectures.