SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    date range logic in PL/SQL loop

    Hi guys. I have a PL/SQL procedure as below to use loops to control data purging.Each loop execute to purge old data for 10 days until the remaining records left is only 30 days. However, I notice this code will purge data for exactly 30 days ago (SYSDATE - 30) which should be maintained in database.


    Code:
    DECLARE
          days2keep      PLS_INTEGER    := 30;
          startDate       VARCHAR2 (50)     :=  SYSDATE - 70;
          sql_stmt        VARCHAR2 (300);
          i                   VARCHAR2 (50);
    
       BEGIN
       
       i :=  startDate ; 
       LOOP       
          sql_stmt :=
                'BEGIN INSERT INTO HIST_TBL '
             || ' (SELECT * FROM  TBL'
             || ' WHERE trandt BETWEEN '
             || i
             || 'AND' i + 10
             || '); DELETE FROM  TBL'
             || ' WHERE trandt BETWEEN '
             || i
             || 'AND' i + 10
             || '; end;';
        
          EXECUTE IMMEDIATE sql_stmt;
    
          i := i + 10;
          EXIT WHEN i >= SYSDATE - 30;
     END LOOP;

    1st loop: SYSDATE - 70 --> SYSDATE - 60 (Purged)
    2nd loop: SYSDATE - 60 --> SYSDATE - 50 (Purged)
    3rd loop: SYSDATE - 50 --> SYSDATE - 40 (Purged)
    4th loop: SYSDATE - 40 --> SYSDATE - 30 (SYSDATE-30 is purged as well, how can avoid it?)

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Start with 71 ?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •