[OAF] How run SQL query in Oracle OAF (Programmatic View Object) in Controller?

 

In this post we will see how to run SQL Query and fetch data using the controller. 

To get data from any SQL in Oracle OAF, you can use PreparedStatement

 

Use the below import statements in Controller Java File:

 

import java.sql.*; 

 

And you can write the below code in the processFormRequest method of the controller:

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean) {

        super.processFormRequest(pageContext, webBean);

     

    try{

        String query = " SELECT 'Test Column' TEST_COL " +

                    " FROM fnd_lookup_values flv " ;

                       

        pageContext.writeDiagnostics(this, "Query---->" + query, 1);

       

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

        PreparedStatement stmt = conn.prepareStatement(query);

        ResultSet resultset = stmt.executeQuery();

        resultset.next();

        String result = resultset.getString("TEST_COL");

        stmt.close();

        resultset.close();

        pageContext.writeDiagnostics(this, "Result : " + result , 1);

       

    }

    catch(Exception ex){

        pageContext.writeDiagnostics(this, "Exception in Programatic VO: " + ex.getMessage() , 1);

       

    }

}

 

 

In case you're using parameters in your query then you can use like below:

 

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean) {

        super.processFormRequest(pageContext, webBean);

    try{

        String query =  " SELECT MEANING FROM FND_LOOKUP_VALUES "+

                        " WHERE LOOKUP_TYPE= :1 AND LOOKUP_CODE= :2 AND VIEW_APPLICATION_ID = :3" ;

                       

        pageContext.writeDiagnostics(this, "Query---->" + query, 1);

       

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

        PreparedStatement stmt = conn.prepareStatement(query);

        preparedstatement.setString(1, "YES_NO");

        preparedstatement.setString(2, "Y");

        preparedstatement.setInt(3, 0);

       

        ResultSet resultset = stmt.executeQuery();

        resultset.next();

        String result = resultset.getString("MEANING");

        //Or use the below index notation

        //String result = resultset.getString(1);

       

        stmt.close();

        resultset.close();

        pageContext.writeDiagnostics(this, "Result : " + result , 1);

       

    }

    catch(Exception ex){

        pageContext.writeDiagnostics(this, "Exception in Programatic VO: " + ex.getMessage() , 1);

       

    }

}