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.
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 functionimport java.sql.CallableStatement;
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.
very good article Sameh .thnx
ReplyDeleteHi,
ReplyDeleteThanks for sharing this valuble information.
Could you please confirm that by the same method can we cal PL/SQl Package also.
Thanks,
Tarun
Hi Tarun,
ReplyDeleteif 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
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?
ReplyDeleteThanks, 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?
ReplyDeleteVry thx man
ReplyDeleteGood one , Thank you
ReplyDeletethanks
ReplyDeleteHi,
ReplyDeleteI 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?