Slight problem with F5 and errors.

Toad for Oracle
1

Afternoon all,

quiet in here today isn’t it?

I’m running a pile of SQL - insert statements mostly - as a script using
F5.

Toad 10.5.1.33.
I have “execute scripts in Toad session” unchecked.
I have “execute queries in threads” checked.
I have “use separate connection when Toad is generating transactions”
unchecked.

One of my INSERTS (ok, a few!) threw up a constraint violation, which I
was expecting, however, due to a typo, they all did. I clicked CANCEL on
the error pop-up and the script ceased running.

I made a quick fix in the MOE, and F5’d again. This time, it hung. I
knew straight away that it would be because the previous F5 hadn’t
committed or rolled back, so I was a tad up the creek. I had to find and
kill the session that was lurking around in the background which
released the locks and my F5 session carried on and committed at the
end.

I’m now thinking that perhaps, when a script is running in it’s own
connection and the user does a CANCEL, that it should rollback as well?
Otherwise there’s no way for a non DBA enabled (session killer abilities
required) user to be able to find and kill the session holding the
lock(s).

Equally, if a script, running in it’s own session doesn’t commit or
rollback at the end, could we have a pop-up warning us that this has
occurred and unless we do so, we may well suffer from locking and give
the option to commit or rollback as required in that very same
background session? Please.

I know that if I had the option checked to “execute scripts in Toad
session” checked then I could click the commit/rollback button or type
commit/rollback and F5/F9 - but I don’t have that option checked.

Thanks.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

2

Yeah, I’ve seen that sort of thing too. In one way of looking at it,
the problem is this: in those conditions, there’s a session that the
user has no access to.

  • I presume that when you hit F5 under those conditions, Toad creates a
    new session, executes the script in that session, and then closes the
    session (if you don’t hit Cancel).
  • But if you hit Cancel, Toad does not close the session, so it’s still
    floating out there with an uncommitted transaction.

It seems to me that if you hit Cancel, Toad should close that session
rather than leaving it out there. But should it commit or roll back?
My thought right now is that, when you hit Cancel on an F5 that has its
own session, Toad should prompt: Commit or Rollback? (Or maybe a third
option: Open a new Editor tab that is connected to that session?)

Nate Schroeder
US Seed & Trait Commercial IT - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592

3

It’s a bit too late in this release to implement something like this. Maybe something for the IdeaPond so it doesn’t get lost?

4

Added!

Cheers,
Norm. [TeamT]

5

I even voted for it.

6

I vote for this too.

Groetjes,
Wim

On Mon, Aug 16, 2010 at 17:47, Gregory Liss wrote:

I even voted for it.