Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: any wrong on this DELETE procedure. can we write like this or is there any better way

    -- Here we are just deleting the records from master and child table prior to ---90days history records. Assume that tables have more than 20k records -----are there to delete. and here i put commit for each 5k records.. Please ------letme know if i am wrong here ?


    create or replace Procedure PURGE_CLE_ALL_STATUS ( days_in IN number )
    IS

    reccount NUMBER := 0;

    CURSOR del_record_cur IS

    SELECT EXCEPTIONID FROM EXCEPTIONREC
    WHERE trunc(TIME_STAMP) < trunc(sysdate - days_in );


    BEGIN
    FOR rec IN del_record_cur LOOP

    delete from EXCEPTIONRECALTKEY -- child table
    where EXCEPTIONID =rec.EXCEPTIONID ;

    delete from EXCEPTIONREC -- master table
    where EXCEPTIONID =rec.EXCEPTIONID;


    reccount := reccount + 1;

    IF (reccount >= 1000) THEN
    COMMIT;
    count := 0;
    END IF;
    commit;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from <OWNER>.<TABLE_NAME>.');
    END;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    yes, you are wrong.
    It is ALWAYS bad to have COMMIT inside LOOP.
    It gains nothing.
    It make procedure run slower/longer.
    It increases the odds that an ORA-01555 error will occur.
    Beside NO PL/SQL is required & same can be done with plain SQL only
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Adding a bit to anacedent's post, you should consider creating a (the) foreign key constraint on the child table with the "ON DELETE CASCADE" option. That way you only need to delete the parent.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    May 2012
    Posts
    2
    Thanks for all your responses. Could some one pls change the pl/sql.. i am new to procedures. Appreicate your great help on this.. sure we will keep in mind about LKBRWN comments.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    NO PL/SQL is required & same can be done with plain SQL only
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    create or replace Procedure PURGE_CLE_ALL_STATUS ( days_in IN number )
    IS
    BEGIN
    delete FROM EXCEPTIONREC
    WHERE trunc(TIME_STAMP) < trunc(sysdate - days_in );
    DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' records from <OWNER>.');
    commit;
    END;
    /

    This assumes you have a delete cascade.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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