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!!
No comments:
Post a Comment