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!

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.