Blog

Call Procedure in OAF (1 input, 1 output parameter):

            String sql = "BEGIN xx_custom_pkg.custom_prc (:1,:2); END;";
 
            try {
                OracleCallableStatement cs =
                    (OracleCallableStatement)oam.getOADBTransaction().createCallableStatement(sql,
                                                                                              2);
 
                ((OracleCallableStatement)cs.registerOutParameter(2,
                                                                  Types.VARCHAR,
                                                                  0, 2000));
 
                cs.setString(1, xxAttribute1Value);
                cs.execute();
                String outParamValue = cs.getString(1);
                pageContext.writeDiagnostics(this,
                                             "Result is:" + outParamValue,
                                             OAFwkConstants.PROCEDURE);
                cs.close();
 
            } catch (Exception ex) {
                pageContext.writeDiagnostics(this, "Error:" + ex.toString(),
                                             OAFwkConstants.PROCEDURE);
            } 

Call Procedure in OAF (2 input parameters):

 OAApplicationModule oam =  pageContext.getApplicationModule(webBean);
 
 String sql =  "BEGIN xx_custom_pkg.custom_prc (:1,:2); END;";
 
                    try {
                        OracleCallableStatement cs =
                            (OracleCallableStatement)oam.getOADBTransaction()
                               .createCallableStatement(sql, 2);
 
                        cs.setString(1, xxAttribute1Value);
                        cs.setString(2, xxAttribute2Value);
                        cs.execute();
                        cs.close();
                      }
                 catch (Exception ex) {
                        pageContext.writeDiagnostics(this,
                                                     "Error:" + ex.toString(),
                                                     OAFwkConstants.PROCEDURE);
                    } 

Call Function in OAF:

   OAApplicationModule oam = pageContext.getApplicationModule(webBean);  
 
   String sql =   "BEGIN :1 := xx_custom_pkg.call_custom_function (:2); END;";
 
  OracleCallableStatement cs =
                            (OracleCallableStatement)oam.getOADBTransaction()
                                 .createCallableStatement(sql,2);
 
                        try {
                             //Register your function output...
                            cs.registerOutParameter(1, Types.VARCHAR, 0, 2000);
                            //Your input parameter below...
                            cs.setString(2, LinesRow.getAttribute("ReportLineId").toString());
                            cs.execute();
                            String p_res = cs.getString(1);
                            cs.close();       
                            }
                        catch (Exception ex) {
                                        pageContext.writeDiagnostics(this,
                                                                     "Error:" + ex.toString(),
                                                                     OAFwkConstants.PROCEDURE);
                           }