-- cursors.sql
-- Cursors - Example of a cursor reading records from a table and then inserting into another table
--
DECLARE CURSOR curMainCursor IS 
   select * 
   from tablea@linkx
   where client_id = 'xxxxxx';
	
   rInputRec     tablea@linkx%rowtype;
BEGIN
   if curMainCursor%ISOPEN = TRUE then
      CLOSE curMainCursor;
   end if;
   OPEN curMainCursor;
   LOOP
      FETCH curMainCursor INTO rInputRec;
      EXIT WHEN curMainCursor%NOTFOUND;
      insert into tableb (col1,col2,col3)
         values(rInputRec.col1,rInputRec.col2,null)
   END LOOP;
   if curMainCursor%ISOPEN = TRUE then
      CLOSE curMainCursor;
   end if;
END;  
      
-- Example from reading V$RMAN_STATUS
DECLARE CURSOR curMainCursor IS 
   select * 
   from v$rman_status
   ;
    
   rInputRec     v$rman_status%rowtype;
BEGIN
   if curMainCursor%ISOPEN = TRUE then
      CLOSE curMainCursor;
   end if;
   OPEN curMainCursor;
   LOOP
      FETCH curMainCursor INTO rInputRec;
      EXIT WHEN curMainCursor%NOTFOUND;
      dbms_output.put_line(to_char(rInputRec.start_time)||' '||to_char(rInputRec.output_bytes));
   END LOOP;
   if curMainCursor%ISOPEN = TRUE then
      CLOSE curMainCursor;
   end if;
END;  
/