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

   
  CREATE OR REPLACE PACKAGE pkgWithVars
  IS
      NORMAL            CONSTANT NUMBER := 0; 
      FOUND            CONSTANT NUMBER := 1;
      INST            VARCHAR2(50) := 'pkgWithVars';
      PROCEDURE sp;
  END;
  /
  CREATE PACKAGE BODY pkgWithVars AS
   
    PROCEDURE sp
    as
      My_Status  number(5);
    BEGIN
      My_Status      := Normal;
    END;
   
  END;
   

  → Java

   
  package com.ora;
  import java.math.*;
  public class Pkgwithvars {
   
      private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(Pkgwithvars.class);
      public static final BigDecimal NORMAL = new BigDecimal("0");
      public static final BigDecimal FOUND = new BigDecimal("1");
      private String inst = "pkgWithVars";
      public String getInst() {
          return inst;
      }
      public void setInst(String inst) {
          this.inst = inst;
      }
      public void sp() throws Exception {
          try {
              Integer myStatus  = null;
              myStatus      = NORMAL.setScale(0, RoundingMode.HALF_UP).intValueExact();
          }
          catch (Exception e) {
              LOGGER.error(String.valueOf(e));
              throw e;
          }
      }
  }
   

• %ROWTYPE attribute and Record Type conversion:

  Oracle PL/SQL

   
  DECLARE
   
    rec1 PREC_TAB%ROWTYPE;
   
    type rec2_tp is record (
       inst_id  number,
       instr_dt date
    );
   
    rec2 rec2_tp;
    v1 number := 5;
   
  BEGIN
   
    rec1.PREC_ID := v1;
    v1 := rec2.inst_id;
   
  END;
   

  → Java

   
  public class PrecTab {
      private BigDecimal precId;
      private String precSt;
      private Integer cnt;
      public BigDecimal getPrecid() {
          return precId;
      }
      public void setPrecid(BigDecimal precId) {
          this.precId = precId;
      }
      public String getPrecst() {
          return precSt;
      }
      public void setPrecst(String precSt) {
          this.precSt = precSt;
      }
      public Integer getCnt() {
          return cnt;
      }
      public void setCnt(Integer cnt) {
          this.cnt = cnt;
      }
   
      public PrecTab() {
      }
   
      public PrecTab(BigDecimal precId, String precSt, Integer cnt) {
          this.precId = precId;
          this.precSt = precSt;
          this.cnt = cnt;
      }
   
      public PrecTab(PrecTab prectab) {
          this.precId = prectab.precId;
          this.precSt = prectab.precSt;
          this.cnt = prectab.cnt;
      }
  }
   
   
  public class Example {
   
      private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(Example.class);
      private static class Rec2Tp {
   
          private BigDecimal instId;
          private Timestamp instrDt;
          public BigDecimal getInstId() {
              return instId;
          }
          public void setInstId(BigDecimal instId) {
              this.instId = instId;
          }
          public Timestamp getInstrDt() {
              return instrDt;
          }
          public void setInstrDt(Timestamp instrDt) {
              this.instrDt = instrDt;
          }
          public Rec2Tp() {
          }
          public Rec2Tp(BigDecimal instId, Timestamp instrDt) {
              this.instId = instId;
              this.instrDt = instrDt;
          }
          public Rec2Tp(Rec2Tp rec2Tp) {
              if (rec2Tp != null) {
                  this.instId = rec2Tp.instId;
                  this.instrDt = rec2Tp.instrDt;
              }
              else {
                  this.instId = null;
                  this.instrDt = null;
              }
          }
      }
      public void spQuickfileSql() throws Exception {
          try {
              PrecTab rec1  = new PrecTab();
              Rec2Tp rec2  = new Rec2Tp();
              BigDecimal v1  = new BigDecimal("5");
              rec1.setPrecId(v1);
              v1 = rec2.getInstId();
          }
          catch (Exception e) {
              LOGGER.error(String.valueOf(e));
              throw e;
          }
      }
  }
   

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

  Oracle PL/SQL

   
  DECLARE
   
    TYPE student_arraytype IS TABLE OF student%ROWTYPE INDEX BY PLS_INTEGER;
    student_array student_arraytype;
   
  BEGIN
   
    FOR i IN student_array.FIRST .. student_array.LAST
    LOOP
      DBMS_OUTPUT.PUT_LINE(student_array(i).FIRST_NAME);
    END LOOP;
   
  END;
   

  → Java

   
              AssociativeCollection < Integer, Student > studentArray  = new  AssociativeCollection < >( Integer.class,  new  Student());
   
              for (int i = studentArray.first(); i <= studentArray.last(); i ++) {
                  System.out.println(studentArray.get(i).firstName);
              }
   

• Cursor conversion (JDBC):

  Oracle PL/SQL

   
  CREATE PROCEDURE sp IS    
   
      CURSOR C_STUD IS
          SELECT * FROM STUDENT;
  BEGIN
   
      FOR STUD_REC IN C_STUD 
      LOOP
   
          STUD_UPDATE(STUD_REC);
   
      END LOOP;
  END;
   

  → Java

   
              Student studRec = new Student();
   
              try(PreparedStatement stResCStud = mConn.prepareStatement("SELECT * FROM STUDENT"); ResultSet rsResCStud = stResCStud.executeQuery();) {
                  while (rsResCStud.next()) {
                      studRec.setStudentid(rsResCStud.getString(1));
                      studRec.setLast(rsResCStud.getString(2));
                      studRec.setFirst(rsResCStud.getString(3));
                      studRec.setBirthdate(rsResCStud.getTimestamp(4));
                      studRec.setFacultyid(rsResCStud.getInt(5));
                      studUpdate(studRec);
                  }
              }
   

• Cursor conversion (Spring JDBC):

  Oracle PL/SQL

   
  CREATE PROCEDURE sp IS    
   
      CURSOR C_STUD IS
          SELECT * FROM STUDENT;
  BEGIN
   
      FOR STUD_REC IN C_STUD 
      LOOP
   
          STUD_UPDATE(STUD_REC);
   
      END LOOP;
  END;
   

  → Java

   
          SqlRowSet rsCStud = null;
          Boolean fsCStud = null;
   
          try {
              Student studRec = new Student();
              rsCStud = jdbcTemplate.queryForRowSet("SELECT * FROM STUDENT");
   
              do {
                  fsCStud = rsCStud.next();
   
                  if (Boolean.FALSE.equals(fsCStud)) {
                      break;
                  }
   
                  studRec.setStudentid(rsCStud.getString(1));
                  studRec.setLast(rsCStud.getString(2));
                  studRec.setFirst(rsCStud.getString(3));
                  studRec.setBirthdate(rsCStud.getTimestamp(4));
                  studRec.setFacultyid(rsCStud.getInt(5));
                  studUpdate(studRec);
              }
              while (Boolean.TRUE.equals(fsCStud));
          }
          catch (Exception e) {
              LOGGER.error(String.valueOf(e));
              throw e;
          }
   

• Cursor conversion (Hibernate):

  Oracle PL/SQL

   
  CREATE PROCEDURE sp IS    
   
      CURSOR C_STUD IS
          SELECT * FROM STUDENT;
  BEGIN
   
      FOR STUD_REC IN C_STUD 
      LOOP
   
          STUD_UPDATE(STUD_REC);
   
      END LOOP;
  END;
   

  → Java

   
          Boolean fsCStud;
          List<Object[]> cStudList = null;
          Iterator<Object[]> itrCStud;
   
          try {
              Student studRec = new Student();
   
              cStudList = session.createNativeQuery("SELECT * FROM STUDENT").list();
   
              do {
                  itrCStud = cStudList.iterator();
                  fsCStud = itrCStud.hasNext();
   
                  if (!fsCStud) {
                      break;
                  }
   
                  Object[] objCStud = itrCStud.next();
                  studRec.setStudentid(QueryUtility.toObject(objCStud[0], String.class));
                  studRec.setLast(QueryUtility.toObject(objCStud[1], String.class));
                  studRec.setFirst(QueryUtility.toObject(objCStud[2], String.class));
                  studRec.setBirthdate(QueryUtility.toObject(objCStud[3], String.class));
                  studRec.setFacultyid(QueryUtility.toObject(objCStud[4], String.class));
                  StudUpdate.spStud_update(studRec);
              }
              while (fsCStud);
   
              cStudList = null;
          }
          catch (Exception e) {
              LOGGER.error(String.valueOf(e));
              throw e;
          }
   

• Output parameters conversion:

  Oracle PL/SQL

   
  create package body sample_pkg
  as
      PROCEDURE open_cursor (
          PAR_ID IN OUT varchar2,
          PAR_SAJES_OUT OUT sys_refcursor
      )
      IS   
      BEGIN
   
          OPEN PAR_SAJES_OUT FOR
              SELECT  DAD_ID FROM SDAD;
   
      END;
   
      PROCEDURE main_sp
      IS   
         V_ID varchar2(50);
         V_SAJES open_cursor;
      BEGIN
   
          proc_test(V_ID, V_SAJES);
   
      END;
  end;
   

  → Java

   
            public Map<String, Object> openCursor(String parId)
      throws Exception {
          RowSetFactory rowSetFactory = RowSetProvider.newFactory();
          Map<String, Object> outData  = new HashMap<>();
          CachedRowSet parSajesOut = rowSetFactory.createCachedRowSet();
          Connection mConn = JDBCDataSource.getConnection();
   
          try {
              try(PreparedStatement mStmt = mConn.prepareStatement("SELECT   DAD_ID FROM SDAD");) {
                  try(ResultSet rs = mStmt.executeQuery();) {
                      parSajesOut.release();
                      parSajesOut.populate(rs);
                  }
              }
              catch (SQLException se) {
                  SQLCursorHandler.getInstance().handleExeption(se);
                  throw se;
              }
   
              if (!mConn.getAutoCommit()) {
                  mConn.commit();
              }
   
              return outData;
          }
          catch (Exception e) {
              LOGGER.error(String.valueOf(e));
              throw e;
          }
          finally {
              outData.put("par_id", parId);
              outData.put("par_sajes_out", parSajesOut);
   
              if (mConn != null) {
                  mConn.close();
              }
          }
      }
   
      public void mainSp() throws Exception {
          try {
              String vId  = null;
              OpenCursor vSajes  = new OpenCursor();
              ProcTest.spProc_test(vId, vSajes);
          }
          catch (Exception e) {
              LOGGER.error(String.valueOf(e));
              throw e;
          }
      }
   

• Nested procedure conversion:

  Oracle PL/SQL

   
  create PROCEDURE pkg_nested_example IS
      PROCEDURE CHECK1 IS
   
          W_TOBE CHAR(1) := '0';
          W_PAR   CHAR(1) := '0';
   
          PROCEDURE CHK AS
          BEGIN
              W_PAR      := '1';
              W_TOBE    := '1';
          END;
   
      BEGIN
          CHK;
      END;
   
  BEGIN
      CHECK1;
  END;
   

  → Java

   
     public void spPkgNestedExample() throws Exception {
          class NestedFunctions {
   
              public void check1(MutableObject<String> mutablewTobe, MutableObject<String> mutablewPar) throws Exception {
                  String wTobe = mutablewTobe.getValue();
                  String wPar = mutablewPar.getValue();
   
                  try {
                      wPar      = "1";
                      wTobe    = "1";
                  }
                  catch (Exception e) {
                      LOGGER.error(String.valueOf(e));
                      throw e;
                  }
                  finally {
                      mutablewTobe.setValue(wTobe);
                      mutablewPar.setValue(wPar);
                  }
              }
          }
   
          try {
              String wTobe  = "0";
              String wPar  = "0";
              NestedFunctions nestedFunctions = new NestedFunctions();
              MutableObject<String> mutablewTobe = new MutableObject<>();
              MutableObject<String> mutablewPar = new MutableObject<>();
              mutablewTobe.setValue(wTobe);
              mutablewPar.setValue(wPar);
              nestedFunctions.check1(mutablewTobe, mutablewPar);
              wTobe = mutablewTobe.getValue();
              wPar = mutablewPar.getValue();
          }
          catch (Exception e) {
              LOGGER.error(String.valueOf(e));
              throw e;
          }
      }