11 Replies Latest reply: Sep 6, 2013 8:20 AM by Siva Prasad Nidimamidi RSS

    Calling a PlSql Function from a button

    ChinmayP

      Hi All,

       

      I have a requirement where i have to call a plsql function from a Button ( APPLY Button)

      Can any one please help  me how to approach with this task.

       

      The Page Information are as belows :-

      Page Info - /Oracle/apps/po/autocreate/webui/ReturnReqPG

      Controller - oracle.apps.po.autocreate.webui.ReturnReqCO

      Application Module - oracle.apps.po.autocreate.server.AutocreateAM

       

      Thanks,
      Chinmay

        • 1. Re: Calling a PlSql Function from a button
          Siva Prasad Nidimamidi

          Hi,

          in ReturnReqCO :

          if (null != pageContext.getParameter("XXSUBMIT"))

          {

                  OracleCallableStatement oraclecallablestatement = null;

          XXNCVSupplierExbmntDtlREFVOImpl  supplierExbmntDtlREFVOImpl = supplierExtbmntDtlREFAMImpl.getXXNCVSupplierExbmntDtlREFVO1();

          XXNCVSupplierExbmntDtlREFVORowImpl supplierExbmntDtlREFVORowImpl = (XXNCVSupplierExbmntDtlREFVORowImpl)supplierExbmntDtlREFVOImpl.first();

          OracleConnection oracleconnection = (OracleConnection)supplierExtbmntDtlREFAMImpl.getOADBTransaction().getJdbcConnection();

          oraclecallablestatement = (OracleCallableStatement)oracleconnection.prepareCall("BEGIN XXNCV_TEST_PKG.XXNCV_VALIDATE_P(:1); END;");

          try

          {

          DBUtil.setNUMBER(oraclecallablestatement,i++,supplierExbmntDtlREFVORowImpl.getVendorId());

          //(if :1 is number then it will be setNUMBER else setString)

          }

          catch (Exception exception)

                   {

                     throw OAException.wrapperException(exception);

                   }

                  finally

                  {

                      try

                      {

                          oraclecallablestatement.close();

                      }

                      catch (Exception exception1)

                      {

                          throw OAException.wrapperException(exception1);

                      }

                  }

          }

          • 2. Re: Calling a PlSql Function from a button
            ChinmayP

            Hi Siva,

             

            Thanks for the reply.

            Can you please explain me the full procedure how to execute the plSql function from a button.

             

            In the above code, have you extended the controller ?

             

            Please explain a little, so that it will be easy for me.

             

            Thanks in Advance.

            • 3. Re: Calling a PlSql Function from a button
              Siva Prasad Nidimamidi

              Yes I've extended the code.

              and put the below code as follows

              if (null != pageContext.getParameter("XXSUBMIT"))

              {

                      OracleCallableStatement oraclecallablestatement = null;

              XXNCVSupplierExbmntDtlREFVOImpl  supplierExbmntDtlREFVOImpl = supplierExtbmntDtlREFAMImpl.getXXNCVSupplierExbmntDtlREFVO1();

              XXNCVSupplierExbmntDtlREFVORowImpl supplierExbmntDtlREFVORowImpl = (XXNCVSupplierExbmntDtlREFVORowImpl)supplierExbmntDtlREFVOImpl.first();

              OracleConnection oracleconnection = (OracleConnection)supplierExtbmntDtlREFAMImpl.getOADBTransaction().getJdbcConnection();

              oraclecallablestatement = (OracleCallableStatement)oracleconnection.prepareCall("BEGIN XXNCV_TEST_PKG.XXNCV_VALIDATE_P(:1); END;");

              try

              {

              DBUtil.setNUMBER(oraclecallablestatement,i++,supplierExbmntDtlREFVORowImpl.getVendorId());

              //(if :1 is number then it will be setNUMBER else setString)

              }

              catch (Exception exception)

                       {

                         throw OAException.wrapperException(exception);

                       }

                      finally

                      {

                          try

                          {

                              oraclecallablestatement.close();

                          }

                          catch (Exception exception1)

                          {

                              throw OAException.wrapperException(exception1);

                          }

                      }

              }

               

              and here you have to create a package and inside in it put your function.

              in my case i have created as follows

              Package name: XXNCV_TEST_PKG

              Function name : XXNCV_VALIDATE_P with one in parameter.

              just compile above package in your instance and try executing your java code.

              • 5. Re: Calling a PlSql Function from a button
                DilipG

                Hi Chinmay,

                 

                Below is sample code for Delete line You can refer this for save Functionality.

                 

                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);

                           }

                 

                       }

                      }

                 

                 

                  Thanks,

                  Dilip

                • 6. Re: Calling a PlSql Function from a button
                  ChinmayP

                  Hi Dilip,

                  Trust you are doing well.

                   

                  here you have extended the Controller.


                  Is the task can be achieved with extending controller only.

                  Or we need to extend the Application module as well.

                   

                  Please reply.

                  • 7. Re: Calling a PlSql Function from a button
                    DilipG

                    Hi Chinmay,

                     

                    Yes, You achieve this using Controller Extension Only.

                     

                    Thanks,

                    Dilip

                    • 8. Re: Calling a PlSql Function from a button
                      Siva Prasad Nidimamidi

                      package xxncv.oracle.apps.pos.supplier.webui;

                       

                       

                      import oracle.apps.fnd.framework.OAApplicationModule;

                      import oracle.apps.fnd.framework.OAException;

                      import oracle.apps.fnd.framework.OAViewObject;

                      import oracle.apps.fnd.framework.webui.OAControllerImpl;

                      import oracle.apps.fnd.framework.webui.OAPageContext;

                      import oracle.apps.fnd.framework.webui.beans.OAWebBean;

                       

                       

                      import oracle.apps.pos.isp.server.DBUtil;

                       

                       

                      import oracle.jdbc.OracleCallableStatement;

                      import oracle.jdbc.OracleConnection;

                       

                       

                      public class XXTESTCO extends OAControllerImpl

                      {

                          public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)

                          {

                              OracleCallableStatement calleblestatment = null;

                              OAApplicationModule am = pageContext.getApplicationModule(webBean);

                             

                              XXOAViewObjectVOImpl view = am.getXXOAViewObjectVO1();

                              XXOAViewObjectVORowImpl viewrow = (XXOAViewObjectVORowImpl)view.first();

                              OracleConnection oracleconnection = (OracleConnection)am.getOADBTransaction().getJdbcConnection();

                              calleblestatment = oracleconnection.prepareCall("BEGIN test_package.data_sum(:1,:2,:3);END");

                              int i = 1;

                              try{

                                  DBUtil.setNUMBER(calleblestatment, i++, viewrow.item1());

                                  DBUtil.setNUMBER(calleblestatment, i++, viewrow.item2());

                                  int j=i;

                                  calleblestatment.registerOutParameter(i++, 12, 0, 8000);

                                  calleblestatment.executeQuery();

                                  int sum = calleblestatment.getInt(j++);

                                  pageContext.writeDiagnostics(this, "Sum==>"+sum,1);

                              }catch(Exception e)

                              {

                                  throw OAException.wrapperException(e);

                              }

                              finally

                              {

                                  try

                                  {

                                      calleblestatment.close();

                                  }

                                  catch (Exception exception1)

                                  {

                                      throw OAException.wrapperException(exception1);

                                  }

                              }

                             

                             

                          }

                      }

                      • 9. Re: Calling a PlSql Function from a button
                        Siva Prasad Nidimamidi

                        Before executing to above code you will have to compile test_package.data_sum.

                        ==> replace XXTESTCO by your extended controller name.

                        ==> replace XXOAViewObjectVOImpl  by your custom view.


                        Let me know if you need any help.

                        • 10. Re: Calling a PlSql Function from a button
                          ChinmayP

                          Thanks, Siva and Dilip for your help.

                           

                          @Siva - I will let you know if i face any issues when i will implement this one.