Tech Trainings

OAF – Invoke CallableStatement and Prepared Statement

May 13, 2021 by admin

Callable Statement

   public void updateDept()
        {
          
          Number deptID=null;
          String deptName=null;
          String locName=null;
          
          DeptVOImpl vo =getDeptVO1();
          DeptVORowImpl row =null;
          
          Row rows[] = vo.getFilteredRows("newRowFlag","N");
          
          System.out.println("news rows waitning for insertion -->"+ rows.length);
                
        for(int i=0 ; i<rows.length;i++)
           {
           
           row = (DeptVORowImpl)rows[i];
           row.setnewRowFlag("N");
           deptID = row.getDeptno();
           deptName = row.getDname();
           locName = row.getLoc();
           
        String sql = "BEGIN  xxcus_oaf_utils.update_dept("

                +"p_dept_id       =>:1,"
                +"p_dept_name        =>:2,"
                +"p_dept_loc        => :3 , "
                +"x_return_status    => :4,"
                +"x_message => :5); END;";
        
         String returnStatus=null ,  message=null; 
         CallableStatement cStmt =null;
        try
           {
            cStmt = getOADBTransaction().createCallableStatement(sql, 1);
            cStmt.setInt(1, deptID.intValue());
            cStmt.setString(2,deptName ); 
            cStmt.setString(3,locName);

            ((OracleCallableStatement)cStmt).registerOutParameter(4, OracleTypes.VARCHAR, 0, 10);
            ((OracleCallableStatement)cStmt).registerOutParameter(5, OracleTypes.VARCHAR, 0, 4000);
            cStmt.execute();

            returnStatus = cStmt.getString(4);  
            message = cStmt.getString(5);
            
            System.out.println("Return Status-->"+returnStatus + " Error Message-->"+ message);
            cStmt.close();

          
         }
        catch(SQLException e)
          {
            e.printStackTrace();      
          }
         
        }//end for loop
        
       
     }
     

Prepared Statement

    public String getQuantity(OAPageContext pageContext, OAWebBean webBean,
    String poNumStr, String lineNumStr)
     {
         int poNumINT = Integer.parseInt(poNumStr);
         int lineNumINT = Integer.parseInt(lineNumStr);
         int inProcessQty =0;
         
         OAApplicationModule oaapplicationmodule = pageContext.getRootApplicationModule();

                 OADBTransaction oadbtransactionimpl = oaapplicationmodule.getOADBTransaction();
                 PreparedStatement preparedstatement = null;
                 ResultSet resultset = null;
                 OracleConnection conn = (OracleConnection)oadbtransactionimpl.getJdbcConnection();
               
                  try
                 {
                     String s1 =  
                     "select PO_NUMBER,PO_LINE_NUM,\n" + 
                     " sum(QUANTITY_RECEIV) qty\n" + 
                     " from XXSAR_PO_EXPESS_REC_l  \n" + 
                     " where po_number=:1  \n" + 
                     " and po_line_num =:2 \n" + 
                     " and LINE_STATUS!='Received'\n" + 
                     " group by PO_NUMBER,PO_LINE_NUM\n" + 
                     " order by po_line_num";
                     
                      preparedstatement = conn.prepareStatement(s1);
                     preparedstatement.setInt(1, poNumINT);
                     preparedstatement.setInt(2, lineNumINT);
                     resultset = preparedstatement.executeQuery();
                
                     if(resultset.next())
                     {
                           inProcessQty = resultset.getInt(1);
                       
                         resultset.close();
                         preparedstatement.close(); 
                     }
                 }
                 catch(Exception e)
                 {
                     throw OAException.wrapperException(e);
                 }
       
         String inprocessQtyStr =  ""+ inProcessQty ; 
         return inprocessQtyStr;
     }

Comments |0|