Re: Processing different cursor types with the same procedure/function

If your colums are identical then use a UNION ALL which will combine the resultset of both cursors without eliminating duplicate rows OR use UNION if only distinct rows are desired.

> cursor v_employee_company_cursor is
> select
> t0.employee_id,
> t0.payroll_master_record,
> t0.benefits_master_record,
> t1.max_as_of_date,
> t2.data_source_user_id,
> rownum
> from employee_company t0, work_time t1, data_source t2
> where
> t0.company_id = p_company_id and
> t0.identifier = p_employee_id and
> t0.work_time_list_id = t1.work_time_list_id and
> t0.data_source_id = t2.data_source_id(+)

     UNION ALL | UNION
> select
> t0.employee_id,
> t0.payroll_master_record,
> t0.benefits_master_record,
> t1.max_as_of_date,
> t2.data_source_user_id,
> rownum
> from employee_company t0, work_time t1, data_source t2
> where
> t0.company_id = p_company_id and
> t0.ssn = p_employee_id and
> t0.work_time_list_id = t1.work_time_list_id and
> t0.data_source_id = t2.data_source_id(+);
>
>
> for it in v_employee_company_cursor loop
> -- processing code (repeated many times for each cursor type)
> end loop;
>

Now you would need only one cursor and one for loop. BTW..You can have more then one UNION's if you like to combine another identical cursor with different WHERE clause.

HTH
//Rauf Sarwar Received on Tue Aug 13 2002 - 00:47:09 CEST