title: PLSQL Loop Through 100 Row at a time to delete
body: <p>I tried below code to fetch the 1000 rows and loop through to delete 100 row at a time. </p>
  
  <pre><code>DECLARE 
  COUNTER INTEGER :=0;
  v_delstr varchar2(2000);
  v_countstr varchar2(2000);
  SchemaName Varchar2(40):='Staging';
  TableName Varchar2(100):='History';
   BEGIN
  LOOP
  COUNTER := COUNTER + 1;
  DBMS_OUTPUT.PUT_LINE('Counter: '); 
  v_delstr:= 'Delete FROM ' ||SchemaName ||'.'||TableName|| ' where  rownum &lt;= 1000 And startdate&gt;=sysdate-731 and  startdate&lt;=sysdate-730';
  
   execute immediate v_delstr;
   dbms_output.put_line(v_delstr);
  
  v_countstr:= 'BEGIN 
                  DECLARE CANT INTEGER; 
                        SELECT COUNT(*) INTO CANT FROM ' ||SchemaName ||'.'||TableName|| ' where  rownum &lt;= 1000 And startdate&gt;=sysdate-731 and startdate&lt;=sysdate-730;
                        EXIT WHEN CANT &lt;= 100;  
                        COMMIT;  
                END;';
  execute immediate v_countstr;
  dbms_output.put_line(v_countstr);
    --dbms_output.put_line(v_delstr);
    --dbms_output.put_line(v_countstr);
  END LOOP;
  END;
  </code></pre>
  
  
  
  <pre><code>DECLARE 
  COUNTER INTEGER :=0;
  v_delstr varchar2(2000);
  v_countstr varchar2(2000);
  SchemaName Varchar2(40):='Staging';
  TableName Varchar2(100):='History';
   BEGIN
  LOOP
  COUNTER := COUNTER + 1;
  DBMS_OUTPUT.PUT_LINE('Counter: '); 
  v_delstr:= 'Delete FROM ' ||SchemaName ||'.'||TableName|| ' where  rownum &lt;= 10 And startdate&gt;=sysdate-731 and  startdate&lt;=sysdate-730';
  
   execute immediate v_delstr;
   dbms_output.put_line(v_delstr);
  
  v_countstr:= 'BEGIN 
                  DECLARE CANT INTEGER; 
                        SELECT COUNT(*) INTO CANT FROM ' ||SchemaName ||'.'||TableName|| ' where  rownum &lt;= 10 And startdate&gt;=sysdate-731 and startdate&lt;=sysdate-730;
                        EXIT WHEN CANT &lt;= 1;  
                        COMMIT;  
                END;';
  execute immediate v_countstr;
  dbms_output.put_line(v_countstr);
    --dbms_output.put_line(v_delstr);
    --dbms_output.put_line(v_countstr);
  END LOOP;
  END;
  </code></pre>
  
  <p>Got Error</p>
  
  <pre class="lang-none prettyprint-override"><code>Error report -
  ORA-06550: line 3, column 23:
  PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
  
     begin function pragma procedure subtype type &lt;an identifier&gt;
     &lt;a double-quoted delimited-identifier&gt; current cursor delete
     exists prior
  The symbol "begin" was substituted for "SELECT" to continue.
  ORA-06550: line 6, column 18:
  PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
  
     ( begin case declare end exception exit for goto if loop mod
     null pragma raise return select update while with
     &lt;an identifier&gt; &lt;a double-quoted delimited-identifier&gt;
     &lt;a bind variable&gt; &lt;&lt; continue close current delete fetch lock
     insert open rollback savepoint set sql execute commit forall
     merge pipe purge
  ORA-06512: at line 22
  06550. 00000 -  "line %s, column %s:\n%s"
  *Cause:    Usually a PL/SQL compilation error.
  *Action:
  </code></pre>