Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52

    Arrow Unanswered: Procedure doesn't work ...

    I have this procedure running in my database (Oracle 8i) to update the enddate of a task. When a task has overdue I give the enddate the values of the sysdate.

    Everything worked when I created it 15 october and worked until 31 october. That was the last DDL. Since then when tasks have overdue this procedure (who is called in a ColdFusion page) hasn't updated overdued enddates.

    Here is my source:

    PROCEDURE PR_UPD_OLD IS
    CURSOR c_getold IS
    SELECT *
    FROM task
    WHERE to_char(enddate, 'dd-mm-yy') <= to_char(sysdate-1, 'dd-mm-yy');
    BEGIN
    FOR r_getold IN c_getold LOOP
    UPDATE task
    SET enddate = sysdate
    WHERE taskid = r_getold.taskid;
    END LOOP;

    COMMIT;

    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    END;

    Please help,
    thanks!
    A good programmer is a LAZY programmer!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Procedure doesn't work ...

    What on Earth are those TO_CHARs for? They are a bug. Suppose end_date was 1st December 2003 and sysdate is 3rd November 2003, which is earlier.

    using your TO_CHAR end_date becomes the string '01-12-2003' and sysdate-1 becomes the string '02-11-2003'. When you compare these 2 strings, guess which one is smallest? The one that begins with '01...'

    The correct cursor would be:

    CURSOR c_getold IS
    SELECT *
    FROM task
    WHERE enddate <= sysdate-1

    Actually, the whole procedure would be better without the cursor and exception section:

    PROCEDURE PR_UPD_OLD IS
    BEGIN
    UPDATE task
    SET enddate = sysdate
    WHERE enddate <= sysdate-1;
    COMMIT;
    END;

    Now if it fails for some reason you will see an error message.

    Personally, I wouldn't commit within the procedure either, but that's just my preference. I think the user/application that calls your procedure should be in charge of the whole transaction.

Posting Permissions

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