OAF – Invoke CallableStatement and Prepared Statement
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|
Category: OAF