This discussion is archived
2 Replies Latest reply: Sep 10, 2013 1:34 AM by DilipG RSS

passing table data to pl sql procedure oaf

ChinmayP Newbie
Currently Being Moderated

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

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

    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

Legend

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