I like the new QSR with Toad 10.5, but what happened to the commit
button? If I don’t put a commit in my script, I don’t have an easy way
to commit!
Hmmph!
David A. Hicken
UtahToad at gmail dot com
---------- Kiva.org - Make a Small Loan. Make a Big difference.
Pride is concerned with who is right; humility is concerned with what is right.
–Ezra Taft Benson
Perhaps the author has a fear of commitments
My wife once asked me as we were going to a hotel “we have reservations,
right”? I said “Yes, but we’re going anyway.”
Perhaps the author has a fear of commitments
Oh dear!
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
My wife once asked me as we were going to a hotel "we have
reservations, right"? I said "Yes, but we're going anyway."
That one made me laugh out loud.
Thanks John.
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
David,
The original thought was to make it behave like SQL*Plus and have it auto commit. Maybe we should make it behave more like Toad and give you more options as to how you do your commits and bring back the commit/rollback buttons.
Greg
Auto commit on close?
Sql*Plus auto-commits? I have always had to type in a commit; to get it to
commit.
Give us the option, if nothing else. I prefer the commit/rollback buttons like
in TOAD. QSR is not nor will it ever be SQLPlus, and if we wanted SQLPlus,
we’d use SQLPlus, or even iSQLPlus.
–
David A. Hicken
UtahToad at gmail dot com
---------- Kiva.org - Make a Small Loan. Make a Big difference.
Ability will never catch up with the demand for it.
– Malcolm Forbes
Jeff Smith wrote:
Auto commit on close?
I think I should be able to have your commit/rollback buttons back for the beta
on Monday. Greg
Morning David,
Sql*Plus auto-commits? I have always had to type in a
commit; to get it to commit.
Unless you specify otherwise (WHEREVER SQLERROR etc) then SQL*Plus
commits on exit if there were no errors in the last statement. If there
were, it exits with a rollback.
If you wish to commit before exit, then yes, you do have to manually
type and execute a commit.
HTH
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
Unless you specify otherwise (WHEREVER SQLERROR etc) then
SQL*Plus commits on exit if there were no errors in the last statement.
Umm… my understanding was that SQL Plus was reliant on the
“autocommit” setting. So long as it was left at the default
“off” position, when exiting SQLPlus without explicitly committing,
it would do a rollback.
In a test on SQL Plus version 10.2.0.1.0, with autocommit set off, an exit after
an insert does indeed end with committing a record.
Interesting… I should note I’ve never actually tested that
functionality as I always explicitly commit/rollback. It’s definitely not
what I expected.
Roger S.
possibly a larry bug instead of TOAD
SQL> set autocommit off
SQL> show autocommit
autocommit OFF
SQL> insert into locationtype VALUES(‘GEO_FENCE183’,‘AK’);
1 row created.
SQL> select * from locationtype where LOCATIONTYPE_STATE=‘AK’;
LOCATIONTYPE_STATION LO
I got sidetracked today so the QSR update with the commit/rollback buttons won’t
be ready till Thursday’s beta.
If you insert a row, you should be able to see it in the same session, even if
there was no commit.
If you can see it in another session without a commit, then there is a bug.
If you can see it in another session without a commit, then there is a bug.
Hey John;
In my case, the test was simple:
Morning Roger,
Umm... my understanding was that SQL Plus was reliant on the
"autocommit" setting. So long as it was left at the default
"off" position, when exiting SQLPlus without explicitly
committing, it would do a rollback.
From the docs for 10g, for set autocommit ... :
"You control the autocommit feature with the SQLPlus AUTOCOMMIT system
variable. Regardless of the AUTOCOMMIT setting, changes are committed
when you exit SQLPlus successfully."
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
is there some higher authority uber-level that has sysdba role assigned that can
toggle the behaviour
autocommit all DML statements aka autocommit=on
NOT autocommit all DML statements aka autocommit=off
?
Martin Gainty
standard caveats apply
Hi Martin,
is there some higher authority uber-level that has sysdba
role assigned that can toggle the behaviour
autocommit all DML statements aka autocommit=on
NOT autocommit all DML statements aka autocommit=off
You can set (I'm assuming SQL*Plus here) the AUTOCOMMIT on, off or "on
event n statements" yourself without requiring anything other than a
connection to the database.
Unfortunately, exiting from SQLPlus is independent of whetever setting
you have for autocommit. This is mentioned in the SQLPlus manual under
the SET AUTOCOMMIT commnad.
Toad does a similar thing according to how you have set your exit
criteria at Options->Oracle->Transactions:
My own preferenace is OFF (unchecked) for the former and PROMPT ME for
the latter. I do not like autocommit at all. In my view it must be up to
the developer/DBA to decide what must be committed and what must not.
A transaction is the unit of commit, in my opinion, and nothing less
than or greater than one single transaction should be committed. (Or
rolled back.)
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
yes i have found SET AUTOCOMMIT OFF a necessary first step before deciding
whether to either commit/rollback DML statements in a transaction
Thanks Norm!
Martin Gainty
Standard Caveats apply
The classic DBA nightmare about the commit-on-exit was:
SQL*Plus User: Help, I just deleted all my data.
DBA: are you still in that session?
User: No, I exited so I wouldn’t break anything.
I was probably told about that one in my first week by an experienced DBA. He
claimed he’s received that call more than once. This was, of course, way back
when lots of users had SQL*Plus and Flashback wasn’t even a dream in anybody’s
eye. So a simple “type rollback” turned into a restore from backup.
SQL*Plus has done commit on exit since the beginning. It’s worked differently
than just about everything else in Oracle. It should be changed but there are
probably hundreds of thousands of scripts at Oracle sites that assume the commit
will happen.
– jim
