Invoke Stored procedure (PL/SQL) and function using ADF

This blog explains about invoking PL/SQL Stored procedure using ADF

       

   public class PrintAndApplyServicesImpl extends ApplicationModuleImpl implements PrintAndApplyServices {
    /**
     * This is the default constructor (do not remove).
     */
    public PrintAndApplyServicesImpl() {
    }

    /**
     *This method takes the lot number as the input and gives the lot details as the output
     * @param pLotNumber
     * @return
     */

    public LotDetailsDTO getLotDetails(String pLotNumber) {
        //List lotDetailsList = new ArrayList();
        LotDetailsDTO lotInformation = new LotDetailsDTO();
        CallableStatement callableStatement = null;
        try {
            ResultSet rs = null;
             callableStatement = getDBTransaction().createCallableStatement("begin  " + "XHL_PRINT_APPLY_PKG.GET_LPN_INFO(?,?);" +  "end;", 0);                  
            // register the input parameter
            callableStatement.setString(1, pLotNumber);
           // register the out parameter
            callableStatement.registerOutParameter(2, OracleTypes.ARRAY, "ITEM_TAB_RESULT");
            // execute the statement
            callableStatement.execute();
            Array outArray = (Array)callableStatement.getArray(2);
            rs = outArray.getResultSet();
            while (rs.next()) {
                Struct row = (Struct)rs.getObject(2);
                if(row!=null) {
                    System.out.println("length"+row.getAttributes().length);
                    Object[] cols = row.getAttributes();
                    lotInformation.setBatchNumber((String)cols[0]);
                    //System.out.println(lotInformation.getBatchNumber());
                    lotInformation.setItemDescription((String)cols[1]);
                    lotInformation.setItemCode((String)cols[2]);
                    lotInformation.setRevision((String)cols[3]);
                    lotInformation.setLotNumber((String)cols[4]);
                    lotInformation.setTransactionQuantity((String)cols[5]);
                    lotInformation.setExpirationDate((String)cols[6]);
                    lotInformation.setUserName((String)cols[7]);
                    lotInformation.setPalletQuantity((String)cols[8]);
                    lotInformation.setPalletNumber((String)cols[9]);
                    lotInformation.setTransactionDate((String)cols[10]);
                    lotInformation.setTransactionId((String)cols[11]);
                    lotInformation.setBatchId((String)cols[12]);
                    lotInformation.setPrintStatus((String)cols[13]);
                    lotInformation.setExpiration((String)cols[14]);
                    lotInformation.setCurrentDate((String)cols[15]);
                    lotInformation.setGrossWeight((String)cols[16]);
                    lotInformation.setPalletHeight((String)cols[17]);
                    lotInformation.setPalletLength((String)cols[18]);
                    lotInformation.setPalletWidth((String)cols[19]);
                    lotInformation.setVolume((String)cols[20]);
                    lotInformation.setConcatCode((String)cols[21]);
                    lotInformation.setReponseStatus((String)cols[22]);
                    lotInformation.setResponseMessage((String)cols[23]);
                    for(Object col : cols) {
                        System.out.println(col);
                    }
                }
            }
                //lot.setItemDescription(rs.getString(2));
            if (callableStatement != null) {
                callableStatement.close();
            }
        } catch (Exception e) {
                e.printStackTrace();
        }
        return lotInformation;
    }
}