How to Execute and Fetch SQL Queries Data Rows in OAF

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