ADF - Call PL/SQL function in Managed Bean

In this blog we will see a very common use case of how to call pl/sql function in managed bean.

We can achieve this use case using  Application Module Client Interface

Here’s step by step procedure to call pl/sql function in managed bean using Application Module Client Interface.

1 . Create ApplicationModuleImpl class.

   

2.   Write following line of code to call database function (sampleMethod is a pl/sql function which return number.)

    public BigDecimal sampleMethod(BigDecimal param1, BigDecimal param2) {

        CallableStatement plsqlBlock;

        plsqlBlock = null;

        String statement = "BEGIN ?:= YOUR_FUNCTION_NAME( ?, ?); END;";

        plsqlBlock = getDBTransaction().createCallableStatement(statement, 0);

        try {

            plsqlBlock.registerOutParameter(1, Types.DECIMAL);

            plsqlBlock.setBigDecimal(2, param1);

            plsqlBlock.setBigDecimal(3, param2);

            plsqlBlock.executeUpdate();

            return plsqlBlock.getBigDecimal(1);

        } catch (SQLException sqlException) {

            throw new SQLStmtException(CSMessageBundle.class, CSMessageBundle.EXC_SQL_EXECUTE_COMMAND, statement,

                                       sqlException);

        } finally {

            try {

                plsqlBlock.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

For procedure add below code

    public BigDecimal sampleMethod(BigDecimal param1, BigDecimal param2) {

        CallableStatement plsqlBlock;

        plsqlBlock = null;

        String statement = "BEGIN YOUR_PROCEDURE_NAME( ?, ?,?); END;";

        plsqlBlock = getDBTransaction().createCallableStatement(statement, 0);

        try {

           

            plsqlBlock.setBigDecimal(1, param1);

            plsqlBlock.setBigDecimal(2, param2);

            plsqlBlock.registerOutParameter(3, Types.DECIMAL);

            plsqlBlock.executeUpdate();

            return plsqlBlock.getBigDecimal(3);

        } catch (SQLException sqlException) {

            throw new SQLStmtException(CSMessageBundle.class, CSMessageBundle.EXC_SQL_EXECUTE_COMMAND, statement,

                                       sqlException);

        } finally {

            try {

                plsqlBlock.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

Note: the difference between function and procedure is of ' ? '. For function 1st ? will be output and in case of procedure last ? will be output. 

3.   Add function to Client Interface.

4. Bind component on jspx page as MethodAction

5.  Write following lines of code to execute ClientInterface Method action on Manged Bean.(in ActionListener of your button)

       BindingContext bCtx = BindingContext.getCurrent();

       DCBindingContainer dcbContainer = (DCBindingContainer) bCtx.getCurrentBindingsEntry();

       BindingContainer bContainer = (BindingContainer) dcbContainer;

       OperationBinding opBinding = (OperationBinding) bContainer.getOperationBinding("sampleMethod");

        opBinding.getParamsMap().put("param1", 10);                                                                          

        opBinding.getParamsMap().put("param2", 20);

BigDecimal result= opBinding.execute();                                                                                                             

In this way you can call pl/sql function or procedure using Application Module Client Interface.                   

Hope this will be useful!!