PL/SQL Conditional Execution

Dear Experts,

I want to write a PL/SQL procedure such that: If a result of an Update statement is successful, then I'll delete a record from other table.

How can I implement this?

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.

ste5anSenior DeveloperCommented:
If a result of an Update statement is successful

There is no such thing. Either it works or it don't. In the later case it should throw an exception. So your DELETE gets not executed.

Can you provide more details, please?
sdstuberCommented:
the code in your previous question where the delete occurs won't fire if the update fails.

however,  if the update succeeds but updates zero rows, then the delete will still run
because a delete of 0 rows is still considered a successful statement.  Is that acceptable?

if not, then you need to put a check around it

if sql%rowcount > 0 then
     --- put your delete statement here
end if;
GurcanKAuthor Commented:
It is something like:

loop <condition>

update table ta where ......;

delete table tx where ....

end loop

can it be like:

loop <condition>

update table ta where ......;

ıf no exception then delete table tx where ....

end loop
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
you don't need to do anything for outright failures,  the delete won't run, the block will simply end if the update fails.

if, as mentioned above, you want to do the delete only if a row is actually updated then you need to check the result of the update.


loop <condition>

 update table ta where ......;

if sql%rowcount > 0 then
      delete table tx where ....
 end if;

 end loop

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
johnsoneSenior Oracle DBACommented:
Based on the other questions, I will say it again.

Get rid of the delete completely.  You are worrying about something  you don't need to.

In the case of a failure, truncate the temporary table and then repopulate it.  It is a temporary table being used for the updates.  No need to try to work around it.  Truncate and recreate removes the delete statement and the scan of deleted rows in the event of a restart (the blocks still need to be read as it will be a full table scan).
ste5anSenior DeveloperCommented:
Seems I missed something...
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.