3 Jun 2011

Using Pipelined functions in View Objects

In this post I'm going to show how we can use Oracle pipelined functions feature to retrieve data from database in the model layer of our ADF application.
Let's assume I have the following function in database:

create or replace function getEmployees
(aDepartmentID Number)
return empTable
  for rec in (select Employee_ID, First_Name, Last_name
              from employees
              where Department_ID = aDepartmentID) loop
    pipe row(new empRow(rec.Employee_ID, rec.First_Name, rec.Last_name));
  end loop;

The function returns a collection of employees for some department. The return type empTable is declared as table of some object type empRow:

create or replace type empTable as table of empRow;

create or replace type empRow as object (
 Employee_ID Number,
 First_Name Varchar2(20),
 Last_name Varchar2(25)

In the Model project of the application I created VO wtith the following definintion:

The VO's query casts the result of getEmployees to table and it has a required parameter deptid - the department's id. JDeveloper works with such structures correctly and the following VO's attributes have been added automatically:

In the Data Controls palette my VO Vpipelined has operation ExecuteWithParams:

I dropped this operation on the jspx page as ADF Parameter Form and got an inputText for department id value and a button:

After that I dropped Vpipelined as a table to the jspx page and set the table as a partial target for the ExecuteWithParams button. Finally I got the following working page:

No comments:

Post a Comment

Post Comment