Oracle PL/SQL to Java Migration

 
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;
 

 
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;
        }
    }
}
 

 
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;
 

 
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;
        }
    }
}
 

 
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;
 

 
            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);
            }
 

 
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;
 

 
            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);
                }
            }
 

 
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;
 

 
        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;
        }
 

 
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;
 

 
        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;
        }
 

 
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;
 

 
          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;
        }
    }
 

 
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;
 

 
    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;
        }
    }