rasti.hil@hilandco.com +41 79 367-9677

Monday, 21 June 2010

Return all data from table via table of object type

Running the following scripts in hr schema:
1) Create types:

create or replace
type EMP_REC as object
(
employee_id number(6),
last_name varchar2(25),
job_id varchar2(10),
manager_id number(6),
hire_date date,
salary number(8,2),
commission_pct number(2,2),
department_id number(4)
);
/
create or replace
TYPE emp_rec_t AS TABLE OF emp_rec;
/

2) Create procedure:

create or replace
PACKAGE EMP_FETCHER AS
FUNCTION get_emp_all RETURN emp_rec_t;
END;
/
create or replace
PACKAGE BODY EMP_FETCHER AS
FUNCTION get_emp_all RETURN emp_rec_t
IS
emp_type_all emp_rec_t:=emp_rec_t();
emp_type_row emp_rec;
i number := 0;

begin

for emp_found in ( SELECT employee_id,last_name,job_id,manager_id,hire_date,salary,commission_pct,department_id
FROM employees
where rownum <= 100)
Loop
i := i + 1;
emp_type_row := emp_rec(null, null, null, null,null, null, null, null);
emp_type_row.employee_id:=emp_found.employee_id;
emp_type_row.last_name:=emp_found.last_name;
emp_type_row.job_id:=emp_found.job_id;
emp_type_row.manager_id:=emp_found.manager_id;
emp_type_row.hire_date:=emp_found.hire_date;
emp_type_row.salary:=emp_found.salary;
emp_type_row.commission_pct:=emp_found.commission_pct;
emp_type_row.department_id:=emp_found.department_id;


emp_type_all.extend;
emp_type_all(i) :=emp_type_row;
end loop;

RETURN emp_type_all;
END get_emp_all;
END;
/

No comments: