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

I wish all the postings are as complete as yours. Still, I am not quite sure exactly what your concern is.

> 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.

Can you elaborate on this?

Isn't calling a generic procedure by passing in the record from the cursors the simplest way to go? What am I missing?

SQL> declare
  2
  3 cursor cur1 is select * from test2;   4 procedure proc1(recin test2%rowtype) is   5 begin
  6 dbms_output.put_line('c1 := '||recin.c1||' , c2 := '||recin.c2);   7 end;
  8 begin
  9 for x in cur1 loop
 10 proc1(x);
 11 end loop;
 12 end;
 13 /

c1 := a , c2 := 1
c1 := b , c2 := 2
c1 := c , c2 := 3
c1 := e , c2 := 1
c1 := a , c2 := 4
c1 := a , c2 := 5
c1 := a , c2 := 6
c1 := a , c2 := 7
c1 := a , c2 := 6
c1 := a , c2 := 7

PL/SQL procedure successfully completed.

SQL> martinbertolino_at_yahoo.com (Martin Bertolino) wrote in message news:<95be4880.0208120703.68a05c15_at_posting.google.com>...
> 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(+);
>
> Notice that the only change is the where clause on the sss/identifier
> column.
>
> 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 Tue Aug 13 2002 - 01:49:38 CEST