In many situations you need to create PL/SQL function in your database and call it from your application. This post will illustrate how to create PL/SQL function in your database and how to call this function. The next example will be applied in HR Schema which we will create a PL function that take an employee Id and return his name. Follow these steps:
1- Create new ADF Application assume Application name "UsingPLSQLFunction_Application".
2- Create new page assume its name "index.jspx".
3- Your Application should like this
4- Right-click in your model project -- > new --> ADF Business Components --> Business Components From Tables. Then Press Ok.
5- Choose you database connection (assume HR Schema).
6-Choose EMPLOYEES Table as an Entity. Then Choose Employees as Updatable view object. Then press in finish button.
7- Open your database navigator then open your application connection then right-click on Function then choose New Function.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhg2GiX4CSObEuztQMC5r8_fPc17NiIi47yzcSSpTO6CG-fEJZFwiwUd3guAvZ670DjN2KTSZcWLjL6cJF-WAM8TycqPx6uVUVFq0pXf3-4I6z9UenFEv78JsRT672OhZA5mbjYaey9vM7z/s320/3.JPG)
8- Enter Function Name as "GET_EMPLOYEE_NAME" then press ok.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5_Vl90jJJP1r23YGRSK3qJ118XW_DnPI_l5ZtWhdvLKzYdyMj7_9yYWSl4iHkPac-Bl6nRchEljxMBZUPMa_hpRc9E7Ua0AEHfn7YAnnrG8lWTGqOTZV4JYwwCrp7KBHyD1bJGl6SqRhk/s320/4.JPG)
9- Write this function :
CREATE OR REPLACE FUNCTION GET_EMPLOYEE_NAME (emp_id NUMBER)
RETURN VARCHAR2
AS
emp_name VARCHAR2(20);
BEGIN
select FIRST_NAME into emp_name from employees
where EMPLOYEE_ID=emp_id;
RETURN emp_name;
END GET_EMPLOYEE_NAME;
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIfx0PRRVNNLBdAMdMaWVvDmpBYKOZyqauS8UMOJcLCieNoiQ9SKcpLv2n2lo6EodBUuUnmOKB_edDsIWi12WPKVEyf3GnS2jQ7Cxyn3AKZmP0zVi07vsVj-EPUknrr3VlnkmDKhWEHUM6/s320/5.JPG)
10 - Open your Application Module and generate
AppModuleImpl file.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRSSfAq_Q8ofnFsL3yHqfUkZp1y36agHbpYqeDTft_L153Qm9IdcVyxVvQzgbPtd2gx4V04TB8aXPMsits6_rzvrUJesdWWBziOHtZQbF4t1l-V7JS7ziHSwrwEKmtSEUxb-o26xLQUy6M/s320/6.JPG)
11- Open this file and write this function
imports:
import java.sql.CallableStatement;
import java.sql.SQLException;import java.sql.Types;
import oracle.jbo.JboException;
public String getEmployeeName(int empId){
CallableStatement cs=null;
try{
cs=getDBTransaction().createCallableStatement("begin ? := GET_EMPLOYEE_NAME(?); end;",0);
cs.registerOutParameter(1, Types.VARCHAR);
cs.setInt(2, empId);
cs.executeUpdate();
return cs.getString(1);
}catch(SQLException e){
throw new JboException(e);
}
}
12- Open AppModule -- > java --> client Interface then shuttle this function.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDTM77aQz3VZvbB6iJfvQdi1OizA_iJvYS8-KzU1dxx3J0X_lMruYv8mhFW7thCSRBeEGFN8y-uHUC1JkYlaUaWX6JsZdFF0gqKxCH5ehcu_8YNa3OZlSsCF0hMiBq2d8JycxTXnglT9rL/s320/7.JPG)
13- From your Data Control drag your function in the page then choose ADF Parameter Form.
14- Right-click in the generated button then choose "Create Method Binding For Action" then create a manged bean.
15- The commandButton Method will be like this
public String cb1_action() {
BindingContainer bindings = getBindings();
OperationBinding operationBinding = bindings.getOperationBinding("getEmployeeName");
Object result = operationBinding.execute();
System.out.println("Result= " + result); // result will be the output of PL function
if (!operationBinding.getErrors().isEmpty()) {
return null;
}
return null;
}
16- Save your Application then run index.jspx.