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
GurcanKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
simply put the final condition into the select statement.
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;
/
GurcanKAuthor Commented:
sdstuberCommented:
>>> 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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GurcanKAuthor Commented:
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.
sdstuberCommented:
oh sorry, didn't need the other question, I just needed to read this one more carefully.
I didn't notice it was two different tables.

same idea though.  just put the condition into the select


DECLARE
    cnt   PLS_INTEGER := 0;
BEGIN
    FOR c1 IN (SELECT upd_rid
                 FROM ta_upd
                WHERE upd_rid NOT IN (SELECT ROWID
                                        FROM ta
                                       WHERE 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;
/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GurcanKAuthor Commented:
That's OK, thanks
johnsoneSenior Oracle DBACommented:
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.
johnsoneSenior Oracle DBACommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.