Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Friday, December 30, 2016

Using PL/SQL in Oracle ADF Web Application

Issue:
There are many common cases where we need to build process with intensive database operations which may involve thousands or more records. Building the logic in Java or ADF model layer will cause overhead in Java memory and garbage collections.

Solution:
It is recommended to build such process in database layer and then called from ADF model layer.
With this approach, we have some benefits:
1) Database operations will be running in database servers with one db call.
2) Decouple database and application codes which will make it easier for maintenance.
3) Cleaner ADF backend codes

Example:
We can create ADF ApplicationModule class which contains method to invoke our database stored procedure.

public class AppModuleImpl extends ApplicationModuleImpl implements AppModule {

    public void callDBProc(String param1) {
        try {
            getDBTransaction().executeCommand("begin callDbProc('" + param1 + "'); end;");
        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new JboException(sqle.getMessage());
        }
    }

}