SQL & PL/SQL

Hi all,

I have a FOR loop to execute dynamic SQL.The SQLdynamically take the date(ln_date) from somewhere else and pass in.SQL is doing data purging job whereby all the records with SYSDATE-date > 30 will be purged, the FOR loop control on the purging interval whereby every loop count will purge data for 10 days.

However my loop below purged data within 30 days as well.

DECLARE
loop_count       PLS_INTEGER;
purge_interval   PLS_INTEGER    := 10;
ln_days             PLS_INTEGER    := 30;
ln_date             DATE;
sql_stmt         VARCHAR2 (300);
j                    PLS_INTEGER;
      
BEGIN
loop_count := CEIL((SYSDATE-ln_date- ln_days) / purge_interval) ;

      
       IF loop_count > 0 THEN
          FOR j IN REVERSE 0..loop_count
        LOOP    
         sql_stmt :=
            'BEGIN INSERT INTO '
         ||'MSG_HIST'
         || ' (SELECT * FROM '
         || 'MSG'
         || ' WHERE (SYSDATE - '
         || '(TO_DATE('||''''||ln_date||''''||','||'''dd/mm/yyyy'''||'))'
         || '-' 
         || ln_days
         || ') >'
         || j * purge_interval     
         || '); DELETE FROM '
         || 'MSG'
         || ' WHERE (SYSDATE - '
         || '(TO_DATE('||''''||ln_date||''''||','||'''dd/mm/yyyy'''||'))'
         || '-' 
         || ln_days
         || ') >' 
         || j * purge_interval
         || '; end;';
       DBMS_OUTPUT.PUT_LINE( 'sql_stmt=' || sql_stmt);
        EXECUTE IMMEDIATE sql_stmt;
     END LOOP;
   END IF;