
-- DBMS_OUTPUT. 
set serveroutput on size UNLIMITED;

-- define Cursor
declare CURSOR cursorValue IS
        SELECT puid, rowning_siteu, rowning_sitec
        FROM infodba.PPOM_OBJECT  
        WHERE rowning_siteu is not null ;
      -- define fetch datarow
       ppom_object_rec cursorValue%ROWTYPE;
       i NUMBER := 0;
       i_tp NUMBER := 0;
       iPercent NUMBER := 0;

BEGIN    
    -- get value of i_tp (i_tp=i_ToProcess)
    SELECT COUNT(*) into i_tp FROM infodba.PPOM_OBJECT 
          WHERE rowning_siteu is not null ; 

    OPEN cursorValue;
    -- Print Start Info
    DBMS_OUTPUT.put_line('fts Start.....: ' || TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS'));
    DBMS_OUTPUT.put_line('fts to process: ' || i_tp );

    -- Get First Data via cursor
    FETCH cursorValue INTO ppom_object_rec;
    WHILE (cursorValue%FOUND) LOOP
      i := i + 1;

      -- Update Data
      Update infodba.PPOM_OBJECT set rowning_siteu=null, 
                                 rowning_sitec=null 
                                 where puid = ppom_object_rec.puid;
      -- Commit Changes
      COMMIT ;
      
      -- Print Detail Data
      if i = 1 or i = i_tp or mod(i,50000) = 0 then
       iPercent := (i / i_tp) * 100 ;
       DBMS_OUTPUT.put_line('fts:' || i || '/' || i_tp || ' '  || lpad(iPercent,5) ||  '% '  || TO_CHAR (SYSDATE, 'DD.MM.YY HH24:MI:SS'));
      end if ;      
      
      -- Get Next Data via cursor
      FETCH cursorValue INTO ppom_object_rec;
    END LOOP;
    
    -- Print End Info
    DBMS_OUTPUT.put_line('fts End.......: ' || TO_CHAR (SYSDATE, 'DD.MM.YYYY HH24:MI:SS')) ;
    CLOSE cursorValue;
END ;
