SQL & PL/SQL

Hi friends,

Please help me to use an associate array in SQL query (WHERE clause). The requirement is similar to the following example:

===============================================
declare

type rec_emp is record(emp_id integer, emp_name varchar2(100));
type ty_emp is table of rec_emp index by pls_integer;
tb_emp ty_emp;

type ty_emp_history is table of emp_history%rowtype index by pls_integer;
tb_emp_history ty_emp_history;

begin

select emp_id, emp_name
bulk collect into tb_emp
from emp
where dept_id = 10;

--Now I want to fetch records from emp_history based on the values in tb_emp. So I want a query something like the following.
--(I know that join can be used to achive this. But it is just an example. I need to achive this in collections.)

select *
bulk collect into tb_emp_history
from emp_history
where emp_id = tb_emp.emp_id
and emp_name = tb_emp.emp_name;

end;
===============================================

Thanks in advance.

Edited by: Iniyavan on Oct 26, 2012 11:50 AM