Oracle Application's Blog: How to Execute and Fetch SQL Queries Data Rows in OAF

Assigning Values to the DFF attributes through SQL query in OAF Page through Controller Extension

In this post , We will use SQL to fetch the data from some custom Table and then assign this fetched columns in the DFF attributes. We will execute the SQL query based on the LOV selected value by the Business user in the OAF page. Here in this requirement , we will take help of Controller Extension to Capture the LOV selection event and then use the LOV selected value to fetch the sql query date and then assign this value to the DFF attributes for the OAF page. This is some thing we are giving solution out of the box from the standard application to capture the external information based on the Value selected by the user in the LOV. We can then use this external information in the OAF pages through DFF fields.

Assigning Values to the DFF attributes through SQL query in OAF Page through Controller Extension

Here below I am sharing you the Controller Extension for this above requirement.

There are the steps this Controller will do.

1.Capture the LOV selection event .

2.Then use the LOV selected value to fetch the sql query date.

3Then assign this value to the DFF attributes for the OAF page.

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
    {
       super.processFormRequest(pageContext, webBean);

         OAApplicationModule am = pageContext.getApplicationModule(webBean);

        String xxinvno=null;
        String Att1=null,Att2=null;
        String result ="";
        if(pageContext.isLovEvent())
        {
             String lovInputSourceId = pageContext.getLovInputSourceId();
             if("XXInvNo".equals(lovInputSourceId))
             {

                  Hashtable lovValue = pageContext.getLovResultsFromSession(lovInputSourceId);
                  if(lovValue !=null)
                    {
                      xxinvno =(String)lovResults.get("XXInvNo"); // "InvNo" is the Return Item specified in the lovMapping

                    }
                  if (xxinvno!=null)
                  {
                      String sql = "select customer_name,phone_no from xx_third_pary_data where invocie_number= ="+ xxinvno;
                      try {
                          Connection conn = (Connection)am.getOADBTransaction().getJdbcConnection();
                          PreparedStatement sql1 = conn.prepareStatement(sql);
                          ResultSet rs = sql1.executeQuery();
                          while(rs.next())
                            {
                             Attr1 = rs.getString(1).trim();
                             Attr2 = rs.getString(1).trim();
                             }
                            }
                        catch (Exception e)
                            { e.printStackTrace();
                            }

                       OAViewObject viewobject1 = (OAViewObject)am.findViewObject("SuppliersHeaderVO");
                          if(viewobject1!=null)
                          {
                           pageContext.writeDiagnostics(this, "Successfully set the VO", 2);
                          }

                          OAMessageTextInputBean viotype = (OAMessageTextInputBean)webBean.findChildRecursive("Attribute1");
                           viotype.setValue(pageContext, Attr1);
                          OAMessageTextInputBean Tktno = (OAMessageTextInputBean)webBean.findChildRecursive("Attribute2");
                           Tktno.setValue(pageContext, Attr2);
                          }
                      }
              }
          }

Assigning Values to the DFF attributes through SQL query in OAF Page through Controller Extension

How to Execute and Fetch SQL Queries Data in OAF

Hi Friends , In this post I will show the OAF code to Execute the Sql query in the OAF controller and then Fetch all the rows of this queries in the Same controller to do further Analysis. We often need to user SQL queries in OAF code to do data comparison and to get the values from other modules so using this OAF code will help you allot in you OAF controller Extension to execute and fetch all the rows of the Sql query.

How to Execute and Fetch SQL Queries rows in OAF

Step1:- Suppose you are doing Controller Extension in your PR approval Notification Page and in your PR you have maintained some values in DFF of 'PO_REQUISITION_LINES_ALL' and here in CO extension you want to fetch these values.

Step2:- This is the OAF code to execute and Fetch Sql query results in OAF controller and then can be used for Comparison.

public void processRequest(OAPageContext paramOAPageContext, OAWebBean paramOAWebBean)
    {
      paramOAPageContext.writeDiagnostics(this, "XX Start PR ", 1);

        String parameterName;

String COL1_VALUE="";

String COL2_VALUE="";

        String COL3_VALUE="";

       Number PRHdrId;
            PRHdrId = new Number(paramOAPageContext.getDecryptedParameter("PRHeaderId"));
                if (PRHdrId!=null)
                {

    Connection conn = paramOAPageContext.getApplicationModule(paramOAWebBean).getOADBTransaction().getJdbcConnection();
     String Query = "SELECT attribute1 col1,attribute2 col2,attribute3 col3 FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=TO_NUMBER(:1)";

                PreparedStatement stmt = conn.prepareStatement(Query);
                   String PRHdrId = ""+PRHdrId;

     stmt.setString(1,PRHdrId);
     for(ResultSet resultset = stmt.executeQuery(); resultset.next();)
     {

   COL1_VALUE = resultset.getString("COL1");
                       COL2_VALUE = resultset.getString("COL2");
                       COL3_VALUE = resultset.getString("COL3");
      paramOAPageContext.writeDiagnostics(this, "Query executed"+COL1_VALUE,1);
                   paramOAPageContext.writeDiagnostics(this, "Query executed"+COL1_VALUE,1);
                   paramOAPageContext.writeDiagnostics(this, "Query executed"+COL1_VALUE,1);
     }

Result Set :-

A table of data representing a database result set, which is usually generated by executing a statement that queries the database.
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

How to Execute and Fetch SQL Queries Data Rows in OAF