Processing different cursor types with the same procedure/function

I have some PL/SQL code that has a few cursors that are almost identical - join same tables, return same set of columns - but differ only in their where clauses. For example:

• find all the candidate master records here 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 -- we use this to index into the candidate records collection 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(+);

and the other cursor is:

• find all the candidate master records here 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 -- we use this to index into the candidate records collection 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(+);

The rows returned by these cursors are then processed in a construct such as:

for it in v_employee_company_cursor loop

• processing code (repeated many times for each cursor type) end loop;

The problem I have is that I have to have a loop for each of the different cursors but the code within the loop block is identical.

I would like to be able to factor out this code into a generic function so I do not have repeated code and when I make changes or fixes they are done in only one place.

I looked at cursors with specified return type, but this can only be a table type so I did not know how to do this with joins or with subsets of all the columns. I could also create a procedure/function that takes one argument for each column in the select but this seemed error prone and difficult to maintain.

Does anybody have any suggestions on how could this be accomplished? This is just one case of this scenario, my application has other sections that suffer from this code repetition that I would like to avoid.

Thanks in advance

Martin Bertolino
'martinbertolino'||'_at_'||'yahoo'||'.'||'com' Received on Mon Aug 12 2002 - 17:03:19 CEST