2 Replies Latest reply: Sep 10, 2013 3:34 AM by DilipG RSS

    passing table data to pl sql procedure oaf

    ChinmayP

      Hi All,

       

      I have a requirement where i have to pass table data to plsql procedure.

       

      In the first page i select the REQUISITION and click on RETURN button and it will take me to the next page.

      and in the Next page i will click on APPLY button.

       

      When i click on APPLY, it will call the procedure and will give input to the procedure whatever has been selected when i have selected requisition.

       

      Please help. Please tell me the approach how to get this task done. A sample code will work.

       

      Hope the requirement is clear.

       

      Thanks in Advance.

        • 1. Re: passing table data to pl sql procedure oaf
          Sushant Sharma

          Hi,

           

          If you want to get data from DB table, you can use PreparedStatement for that. Refer below link:

          http://oa-learning.blogspot.in/2011/08/execute-sql-script-in-oaf.html

          http://oracleanil.blogspot.in/2009/04/serialqueryvoxml.html

           

          --Sushant

          • 2. Re: passing table data to pl sql procedure oaf
            DilipG

            Hi Chinmay,


            Refer below code for Your Requirement.



            //Code For Quering Data

             

             

            import java.sql.PreparedStatement;

            import java.sql.ResultSet;

             

             

             

            {

            Connection conn = pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();

            String Query = "SELECT organization_id FROM hr_operating_units WHERE organization_id = fnd_global.org_id";

            PreparedStatement stmt = conn.prepareStatement(Query);

            resultset=stmt.executeQuery();

            while (resultset.next())

            {

            orgId = (String)resultset.getString("ORGANIZATION_ID").toString();

             

            }

            conn.commit();

            }

            catch(Exception e)

            {

            e.printStackTrace();

            }

            }

             

             

             

            //Code for Pass Resulted column to Procedure Input for delete Particular Record

             

             

            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 (orgId IN VARCHAR2);

            END genpack_pkg;

            /

             

             

             

            Package Body

             

             

            CREATE OR REPLACE PACKAGE BODY APPS.genpack_pkg

            AS

            PROCEDURE roll_delete_proc (orgId IN VARCHAR2)

            AS

            BEGIN

            DELETE FROM pklist_roll_details_temp

            WHERE roll_line_id = orgId;

            COMMIT;

            END roll_delete_proc;

            END genpack_pkg;

            /

             

             

             

            //in Controller PFR

             

             

             

            import java.sql.CallableStatement;

             

             

             

            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("orgId"));

            Connection conn = am.getOADBTransaction().getJdbcConnection();

            if (rollid == 1)

            {

             

            temphm.put(row.getAttribute("orgId").toString(), row.getAttribute("PoNumber").orgId());

            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