Create PL/SQL Function And Call It From Your ADF Application

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.

8- Enter Function Name as "GET_EMPLOYEE_NAME" then press ok. 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;

10 - Open your Application Module and generate AppModuleImpl file. 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.

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.