GurcanK
asked on
Update Using PL/SQL with Error Condition
Dear Experts,
I have below PL/SQL code. However I want to improve this code in such a way that if any error occurs while in UPDATE statement, it should start from the last successful update to avoid starting from beginning causing re-updates.
DECLARE
cnt PLS_INTEGER := 0;
BEGIN
FOR c1 IN (SELECT upd_rid
FROM ta_upd) LOOP
UPDATE ta
SET y = 'xyz'
WHERE ROWID = c1.upd_rid;
cnt := cnt + 1;
IF cnt = 1000 THEN
cnt := 0;
COMMIT;
END IF;
END LOOP;
END;
/
Best Regards
I have below PL/SQL code. However I want to improve this code in such a way that if any error occurs while in UPDATE statement, it should start from the last successful update to avoid starting from beginning causing re-updates.
DECLARE
cnt PLS_INTEGER := 0;
BEGIN
FOR c1 IN (SELECT upd_rid
FROM ta_upd) LOOP
UPDATE ta
SET y = 'xyz'
WHERE ROWID = c1.upd_rid;
cnt := cnt + 1;
IF cnt = 1000 THEN
cnt := 0;
COMMIT;
END IF;
END LOOP;
END;
/
Best Regards
ASKER
The table ta_upd only keeps rowid. Please refer to Question: https://www.experts-exchange.com/questions/28697061/PL-SQL-for-Updates-with-Frequent-Commit.html
>>> The table ta_upd only keeps rowid.
I don't know what you mean.
The previous question doesn't help explain it to me.
Why do you think the suggested answer might not work?
What does it not do for you?
I don't know what you mean.
The previous question doesn't help explain it to me.
Why do you think the suggested answer might not work?
What does it not do for you?
ASKER
This PL/SQL code was given to me as the answer in that question. In the statement SELECT upd_rid
FROM ta_upd where y is null or y != 'xyz', the table ta_upd has no field named y. The field y exists in table ta not in ta_upd. This is way I asked you to take a look in that question.
FROM ta_upd where y is null or y != 'xyz', the table ta_upd has no field named y. The field y exists in table ta not in ta_upd. This is way I asked you to take a look in that question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's OK, thanks
I realize this is already answered, but what I would do is just truncate the TA_UPD table and repopulate it again. You are only using it for this process. That is probably faster than having to check each row to see if it was already updated first, especially if you are a long way into the process.
I just saw the other question as well. Using the truncate and recreate would also eliminate the need for the delete statement within the code. Takes out a step and should make it faster.
then, if you start over, it will be able to skip any rows it already updated.
DECLARE
cnt PLS_INTEGER := 0;
BEGIN
FOR c1 IN (SELECT upd_rid
FROM ta_upd
where y is null or y != 'xyz') LOOP
UPDATE ta
SET y = 'xyz'
WHERE ROWID = c1.upd_rid;
cnt := cnt + 1;
IF cnt = 1000 THEN
cnt := 0;
COMMIT;
END IF;
END LOOP;
END;
/