Sunday, January 10, 2010

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.

9 comments:

  1. Hi,

    Thanks for sharing this valuble information.
    Could you please confirm that by the same method can we cal PL/SQl Package also.

    Thanks,
    Tarun

    ReplyDelete
  2. Hi Tarun,
    if course you could call PL/SQL Package, just your CallableStatment should be yourPackage.yourFunction as:
    cs=getDBTransaction().createCallableStatement("begin ? := PackageName.GET_EMPLOYEE_NAME(?); end;",0);

    Sameh Nassar

    ReplyDelete
  3. Thanks, do you know how to call a pl/sql procedure which has inputs as well as it returns collection. I want to get this data and draw a adf read only table. Any idea?

    ReplyDelete
  4. Thanks, do you know how to call a pl/sql procedure which has inputs as well as it returns collection. I want to get this data and draw a adf read only table. Any idea?

    ReplyDelete
  5. Hi,
    I followed your approach to call an EBS procedure taking 2 input parameters and retun 3 o/p. but cs.executeUpdate() returns an error

    "Missing In or Out parameter at index::6". Could you please help me to solve this problem?

    ReplyDelete