Search code examples
oracle-databasesys-refcursor

Reusing (re-opening) Oracle refcursor


I have two Oracle refcursors one containing list of Rows(CustomersList) and the other containing column names(PriceGroups) of each Row. I need to loop through each Row and column and update the intersection of each Row/Column. How can I re-position columns cursor to begin on the first column again and again. The table looks following

Customer   | pricegroupA | priceGroupB | priceGroupC | priceGroupEtc|
-----------+---------------------------------------------------------
aaaa       | 23.5        | 23.8        | 30.9        | 41.3         |
---------------------------------------------------------------------
bbbb       | 21.7        | 24.6        | 49.9        | 45.9         |
---------------------------------------------------------------------
....

Solution

  • if you had shared a bunch of code I could rewrite it for you, but i think the following example will be helpful for you.

    declare
      type r_cursor is REF CURSOR;
      c_emp r_cursor;
      en emp.ename%type;
    begin
       /*Opening the refCursor for the first time*/
       open c_emp for select ename from emp;
      loop
          fetch c_emp into en;
          exit when c_emp%notfound;
          dbms_output.put_line(en);
      end loop;
      /*Closing it*/  
      close c_emp;
     /*Opening the refCursor again after it is closed*/
      open c_emp for select ename from emp;
      loop
          fetch c_emp into en;
          exit when c_emp%notfound;
          dbms_output.put_line(en);
      end loop;
      close c_emp;
      /*Closing it again*/  
    end;