Migrate Oracle PL/SQL to Java

Conversion Samples of Oracle PL/SQL to Java

Ispirer Toolkit analyzes all object dependencies during the conversion process and provides not only line-by-line conversion, but resolves type conversions as well. The software understands and transforms the necessary inheritance dependencies. It parses the entire source code, builds an internal tree with all the information about the objects, and uses it in the migration process.

The code samples below provide you with more details.

• Common variables declaration structure conversion:

  Oracle PL/SQL

  1. CREATE OR REPLACE PACKAGE pkgWithVars
  2. IS
  3.     NORMAL            CONSTANT NUMBER := 0;
  4.     FOUND            CONSTANT NUMBER := 1;
  5.     INST            VARCHAR2(50) := 'pkgWithVars';
  6.     PROCEDURE sp;
  7. END;
  8. /
  9. CREATE PACKAGE BODY pkgWithVars AS
  10.  
  11.   PROCEDURE sp
  12.   as
  13.     My_Status  number(5);
  14.   BEGIN
  15.     My_Status      := Normal;
  16.   END;
  17.  
  18. END;

  → Java

  1. @Service
  2. @SessionScope
  3. public class Pkgwithvars implements IPkgwithvars {
  4.   @Getter
  5.   private final BigDecimal NORMAL = new BigDecimal("0");
  6.   @Getter
  7.   private final BigDecimal FOUND = new BigDecimal("1");
  8.   @Getter
  9.   @Setter
  10.   private String inst = "pkgWithVars";
  11.   @Override
  12.   public void sp() {
  13.     Integer myStatus = null;
  14.     myStatus = Converter.toInteger(NORMAL);
  15.   }
  16. }

• %ROWTYPE attribute and Record Type conversion:

  Oracle PL/SQL

  1. DECLARE
  2.  
  3.   rec1 PREC_TAB%ROWTYPE;
  4.  
  5.   type rec2_tp is record (
  6.      inst_id  number,
  7.      instr_dt date
  8.   );
  9.  
  10.   rec2 rec2_tp;
  11.   v1 number := 5;
  12.  
  13. BEGIN
  14.  
  15.   rec1.PREC_ID := v1;
  16.   v1 := rec2.inst_id;
  17.  
  18. END;

  → Java

  1. @Data
  2. @TableType
  3. public class PrecTab {
  4.   private BigDecimal precId;
  5.   private String precSt;
  6.   private Integer cnt;
  7.   public PrecTab() {
  8.     initFields(null, null, null);
  9.   }
  10.   public PrecTab(BigDecimal precId, String precSt, Integer cnt) {
  11.     initFields(precId, precSt, cnt);
  12.   }
  13.   public PrecTab(PrecTab precTab) {
  14.     if (precTab != null) {
  15.       initFields(precTab.precId, precTab.precSt, precTab.cnt);
  16.     }
  17.     else {
  18.       initFields(null, null, null);
  19.     }
  20.   }
  21.   private void initFields(BigDecimal precId, String precSt, Integer cnt) {
  22.     this.precId = precId;
  23.     this.precSt = precSt;
  24.     this.cnt = cnt;
  25.   }
  26. }
  27.  
  28. @Service
  29. @SessionScope
  30. public class QuickfileSqlScr1 implements IQuickfileSqlScr1 {
  31.   @Override
  32.   public void spQuickfileSqlScr1() {
  33.     @Record
  34.     @Data
  35.     class Rec2Tp {
  36.       private BigDecimal instId;
  37.       private LocalDateTime instrDt;
  38.       public Rec2Tp() {
  39.         initFields(null, null);
  40.       }
  41.       public Rec2Tp(BigDecimal instId, LocalDateTime instrDt) {
  42.         initFields(instId, instrDt);
  43.       }
  44.       public Rec2Tp(Rec2Tp rec2Tp) {
  45.         if (rec2Tp != null) {
  46.           initFields(rec2Tp.instId, rec2Tp.instrDt);
  47.         }
  48.         else {
  49.           initFields(null, null);
  50.         }
  51.       }
  52.       private void initFields(BigDecimal instId, LocalDateTime instrDt) {
  53.         this.instId = instId;
  54.         this.instrDt = instrDt;
  55.       }
  56.     }
  57.     PrecTab rec1 = null;
  58.     Rec2Tp rec2 = new Rec2Tp();
  59.     BigDecimal v1 = new BigDecimal("5");
  60.     rec1.setPrecId(v1);
  61.     v1 = rec2.getInstId();
  62.   }
  63. }

• For collection types conversion we’ve implemented additional classes that have same behavior as Oracle collections:

  Oracle PL/SQL

  1. DECLARE
  2.  
  3.   TYPE student_arraytype IS TABLE OF student%ROWTYPE INDEX BY PLS_INTEGER;
  4.   student_array student_arraytype;
  5.  
  6. BEGIN
  7.  
  8.   FOR i IN student_array.FIRST .. student_array.LAST
  9.   LOOP
  10.     DBMS_OUTPUT.PUT_LINE(student_array(i).FIRST_NAME);
  11.   END LOOP;
  12.  
  13. END;

  → Java

  1. public void spQuickfileSqlScr1() {
  2.   AssociativeCollection < Integer, Student > studentArray = new AssociativeCollection <>(Integer.class, Student.class);
  3.  
  4.   for (Integer i = studentArray.first();
  5.       i <= studentArray.last();
  6.       i ++) {
  7.     System.out.println(studentArray.get(i).getFirstName());
  8.   }
  9. }

• Cursor conversion:

  Oracle PL/SQL

  1. CREATE PROCEDURE sp_student_loop IS    
  2.  
  3.     CURSOR C_STUD IS
  4.         SELECT * FROM STUDENT;
  5. BEGIN
  6.  
  7.     FOR STUD_REC IN C_STUD
  8.     LOOP
  9.  
  10.         STUD_UPDATE(STUD_REC);
  11.  
  12.     END LOOP;
  13. END;

  → Java

  1. public void spSpStudentLoop() {
  2.   CursorReader cStud = new CursorReader("SELECT * " +
  3.                       "FROM STUDENT ");
  4.   cStud.open(cursorManager);
  5.  
  6.   while (cStud.fetch()) {
  7.     studUpdate.spStudUpdate(EntityMapper.mapEntity(cStud, Student.class));
  8.   }
  9.  
  10.   cStud.close();
  11. }

• Output parameters conversion:

  Oracle PL/SQL

  1. CREATE PACKAGE BODY sample_pkg
  2. AS
  3.     PROCEDURE increm_fn (
  4.         PAR_ID OUT varchar2,
  5.         PAR_SAJES_OUT IN OUT NUMBER
  6.     )
  7.     IS  
  8.     BEGIN
  9.  
  10.         PAR_ID := 'new value';
  11.         PAR_SAJES_OUT := PAR_SAJES_OUT + 1;
  12.  
  13.     END;
  14.  
  15.     PROCEDURE main_sp
  16.     IS  
  17.        V_ID varchar2(50);
  18.        V_SAJES NUMBER;
  19.     BEGIN
  20.  
  21.        increm_fn(V_ID, V_SAJES);
  22.  
  23.     END;
  24. END;

  → Java

  1. public class SamplePkg implements ISamplePkg {
  2.   @Autowired
  3.   private AdviceWrapper aw;
  4.   @Override
  5.   @OutParameters
  6.   public Map< String, Object > incremFn(@Out String parId, BigDecimal parSajesOut) {
  7.     Map<String, Object> outData = new HashMap<>();
  8.  
  9.     try {
  10.       parId = "new value";
  11.       parSajesOut = MathUtil.add(parSajesOut, 1);
  12.       return outData;
  13.     }
  14.     finally {
  15.       outData.put("parId", parId);
  16.       outData.put("parSajesOut", parSajesOut);
  17.     }
  18.   }
  19.   @Override
  20.   public void mainSp() {
  21.     Map<String, Object> outDataResult;
  22.     String vId = null;
  23.     BigDecimal vSajes = null;
  24.     outDataResult = aw.getProxy(SamplePkg.this).incremFn(vId, vSajes);
  25.     vId = (String) outDataResult.get("parId");
  26.     vSajes = (BigDecimal) outDataResult.get("parSajesOut");
  27.   }
  28. }

• Nested procedure conversion:

  Oracle PL/SQL

  1. create PROCEDURE pkg_nested_example IS
  2.     PROCEDURE CHECK1 IS
  3.    
  4.         W_TOBE CHAR(1) := '0';
  5.         W_PAR   CHAR(1) := '0';
  6.        
  7.         PROCEDURE CHK AS
  8.         BEGIN
  9.             W_PAR      := '1';
  10.             W_TOBE    := '1';
  11.         END;
  12.        
  13.     BEGIN
  14.         CHK;
  15.     END;
  16.    
  17. BEGIN
  18.     CHECK1;
  19. END;

  → Java

  1. public void spPkgNestedExample() {
  2.   class NestedFunctions {
  3.     public void check1() {
  4.       MutableObject<String> mutablewTobe = new MutableObject<>();
  5.       MutableObject<String> mutablewPar = new MutableObject<>();
  6.       class NestedFunctions2 {
  7.         public void chk() {
  8.           String wPar = mutablewPar.getValue();
  9.           String wTobe = mutablewTobe.getValue();
  10.  
  11.           try {
  12.             wPar = "1";
  13.             wTobe = "1";
  14.           }
  15.           finally {
  16.             mutablewPar.setValue(wPar);
  17.             mutablewTobe.setValue(wTobe);
  18.           }
  19.         }
  20.       }
  21.       NestedFunctions2 nestedFunctions2 = new NestedFunctions2();
  22.       String wTobe = "0";
  23.       String wPar = "0";
  24.       mutablewTobe.setValue(wTobe);
  25.       mutablewPar.setValue(wPar);
  26.       nestedFunctions2.chk();
  27.       wTobe = mutablewTobe.getValue();
  28.       wPar = mutablewPar.getValue();
  29.     }
  30.   }
  31.   NestedFunctions nestedFunctions = new NestedFunctions();
  32.   nestedFunctions.check1();
  33. }