How to fetch the cursor from the last fetch in nested cursor?

Question

I am stuck in a situation from which I am finding no solution. Let me give the PL/SQL code:

begin 
      outer_loop_counter := 0;
      inner_loop_counter := 0;
      -- Open first cursor
      open get_vacancy;
      --<<Outer_loop>>
      loop

          fetch get_vacancy 
          into v_category, v_gender, v_vacancy_count;
          exit when get_vacancy%NOTFOUND;
          open get_candidate;
          --<<Inner_loop>>
          for inner_loop_counter in 1 .. v_vacancy_count
          loop
             fetch get_candidate 
              into  c_rollno, c_category,c_gender,c_total_marks;
          update merit_list m set m.merit_position = inner_loop_counter;
          end loop; --Inner_loop;
          close get_candidate;
       end loop; --Outer_loop;
       close get_vacancy;
       end;

The above code is for preparing a merit list for an Admission Application for a College which I am Developing using Java and Oracle. Now the policy is depending upon the number of vacancy from each row fetch, the candidates will be allocated a merit rank. Now the problem with the above code is that for every fetch from the get_vacancy cursor, the inner loop cursor is fetching the records from beginning of the resultset. I want that the candidates who were allocated merit rank in the last fetch do not appear in the next fetch when the cursor moves w.r.t get_vacancy cursor. What should I do?

So when you fetch into v_vacancy_count, the value you get may actually be higher than you need. Thus causing you to iterate through get_candidate more than necessary? Is that a fair assessment? — zundarz, Jun 22 '12 at 13:20

score 0 · Accepted Answer · answered Jun 27 '12 at 16:34

I'll assume the GET_CANDIDATE cursor reads (or can be made to read from) the MERIT_LIST table. Given this, you should be able to add the following to the WHERE clause of your cursor

AND MERIT_LIST.MERIT_POSITION IS NULL

This assumes that MERIT_POSITION is in fact NULL for candidates which have not been assigned a position yet.

Share and enjoy.

How to fetch the cursor from the last fetch in nested cursor?

1 Answers1