This discussion is archived
11 Replies Latest reply: Sep 6, 2013 6:20 AM by Siva Prasad Nidimamidi RSS

Calling a PlSql Function from a button

ChinmayP Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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.

  • 4. Re: Calling a PlSql Function from a button
    Siva Prasad Nidimamidi Newbie
    Currently Being Moderated

    mail me @ siva432@gmail.com if you need more help!!

  • 5. Re: Calling a PlSql Function from a button
    DilipG Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    Hi Chinmay,

     

    Yes, You achieve this using Controller Extension Only.

     

    Thanks,

    Dilip

  • 8. Re: Calling a PlSql Function from a button
    Siva Prasad Nidimamidi Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    Thanks, Siva and Dilip for your help.

     

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points