CODEWITHSUNDEEP
oracle-database
Beetlejuice
Beetlejuice

Reputation: 4425

How to get oracle killed exceptions

I have the following structure in my stored procedure:

loop
try
  update some record to state "1"
  make some process
  update that record to state "2"
  commit 
exception
  update that record to state "3" //error !
end
end loop

Now lets supose that there is an error like session kill, or client disconnect, while this loop is running. How can I threat this error? The try..exception is not working. I need to write "3" to the state field of my record.

Upvotes: 0

Views: 1152

Answers (1)

gpeche
gpeche

Reputation: 22514

You can consider:

loop
try
  update some record to state "3"
  commit
  update some record to state "1"
  make some process
  update that record to state "2"
  commit 
end
end loop

So, you start by setting the status to "3" and commit. Then you start your actual processing. If your session is killed before the process ends, changes since the first commit will be rolled back, so the status will be rolled back to "3". If your processing ends successfully, changes will be commited and your status will be set to "2".

I don't really understand why you set status to "1", it will never be visible outside the transaction, as it is always overwritten by "2" before transaction commit.

Upvotes: 1

Related Questions