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

Search This Blog

Showing posts with label type (collection). Show all posts
Showing posts with label type (collection). Show all posts

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;
/