Execute parameterized PL SQL procedure from OAF page

Let us try to call PL/SQL package from OAF page. We will try to remove selected line from Database.

Package Spec


CREATE OR REPLACE PACKAGE APPS.genpack_pkg
AS
   PROCEDURE roll_delete_proc (num IN VARCHAR2);
END genpack_pkg;
/



Package Body


CREATE OR REPLACE PACKAGE BODY APPS.genpack_pkg
AS
   PROCEDURE roll_delete_proc (num IN VARCHAR2)
   AS
   BEGIN
      DELETE FROM pklist_roll_details_temp
            WHERE roll_line_id = num;
      COMMIT;
   END roll_delete_proc;
END genpack_pkg;
/





import java.sql.CallableStatement;

//in Controller PFR

 if (pageContext.getParameter("ActionsButton") != null)
    {
      String val = pageContext.getParameter("ActionsChoice");

      if ("DELLN".equals(val))
       {
      
        CallableStatement cstmt = null;
       for (OAViewRowImpl row = (OAViewRowImpl)tempvo.first(); row != null; row = (OAViewRowImpl)tempvo.next()) {
           if ((row.getAttribute("Selectflag") == null) ||
             (!row.getAttribute("Selectflag").toString().equals("Y"))) continue;
          try {
             int rollid = Integer.parseInt((String)row.getAttribute("RollLineId"));
             Connection conn = am.getOADBTransaction().getJdbcConnection();
             if (rollid == 1)
            {
           
              temphm.put(row.getAttribute("PoLineId").toString(), row.getAttribute("PoNumber").toString());
             tempvo.removeCurrentRow();
            }
            else
            {
              try
              {
                StringBuilder sb = new StringBuilder();
                sb.append(rollid);
                String strI = sb.toString();
                 System.out.println("Inside else in delete");
                cstmt = conn.prepareCall("{call GENPACK_PKG.tpc_roll_delete_proc(?)}");
               cstmt.setString(1, strI);
                System.out.println("Oracle Callable Statment Execution Init for Delete");
                cstmt.execute();
               
              }
              catch (SQLException e) {
                throw new OAException(e.toString(), (byte)0);
              }tempvo.removeCurrentRow();
            }
          }
          catch (OAException e) {
            throw new OAException("No row selected", (byte)3);
           }

       }
      }