In the status bar TOAD tells me “AUTOCOMMIT OFF”.
How can I turn it on?
Ben
View this message in context: http://old.nabble.com/How-can-I-turn-on-AUTOCOMMIT--tp27520353p27520353.html
Sent from the TOAD mailing list archive at Nabble.com.
Options -> Oracle -> Transactions -> Commit after every statement.
It’s in the options under the View menu.
Ben,
Autocommit is an sql plus command. IT is generally used during script execution.
You can do the following
Set autocommit on
Or set autocommit off
In your script. Check the options window (type commit in the search box in the
lower left). Toad has a few commit options as well.
chris
Hey Ben,
In the status bar TOAD tells me "AUTOCOMMIT OFF".
How can I turn it on?
The goal of my email isn't to tell you how to turn it on, but to emboss it
into your memory so that when this option comes back to bite you, you'll
remember me. Just years of paranoia and having twice been put through a
once-in-a-lifetime ringer of bad lack of forethought. Now, about your
question:
You don't mention the version of Toad you're using, but View->Toad
Options->Oracle->Transactions->"Commit after every statement" (or search for
"commit" in the options) toggle should do what you're looking for.
Best of luck!
Rich -- [TeamT]
Disclaimer: The best tool a shop teacher has is his remaining 9 fingers.
Or in other words, just because you can, doesn’t mean you should.
What Jeff and Rich are saying, I think, is that committing after every statement
is usually a VERY BAD idea.
Commits should be done based on the real transactions you’re working with. They
should be done when it logically makes sense, not after every statement.
Consider a real world example of an ATM.
First SQL statement: withdraw $100 from my account (something like "insert into
financial_trans (account, transaction_amount) values (myAccountNumber,-100);
Second SQL statement: deliver $100 to the user at the ATM
If you autocommit after the first and then the second one fails, the money
leaves your account and you get nothing.
If you commit after the second succeeds, then the money gets transferred right.
And if there’s a failure at the ATM or in the network, you simply roll back the
transaction.
Database work typically involves a bunch of individual statements grouped into
logical transactions. Committing after every statement is almost always bad
design. And, as Rich said, it will eventually bite you.
I’ve been bitten too. So I recommend leaving autocommit turned off.
– jim
I've been bitten too. So I recommend leaving autocommit turned off.
-- jim
I should start a new group on Facebook called "Transactionally Paranoid
Cheeseheads". ;D
Rich -- [TeamTPC]
Disclaimer: I must have missed the Toad for Oracle Superbowl commercial
Morning all,
late to the party as usual! I blame time zones!
What Jeff and Rich are saying, I think, is that committing
after every statement is usually a VERY BAD idea.
Oh yes indeedy, a very very bad thing. If you do this, you will
[eventually] end up with a trashed database.
In fact, as an enhancement request, I'd suggest that the option be
dropped completely in future versions of Toad to avoid the distress that
comes with a commit half way through a script that means when it goes
wrong, the rollback is only partial - in other words, back to an unknown
point in the transaction rather than undoing the whole transaction.
I wonder how many people out there in Toad Land actually turn autocommit
on?
The example I much prefer is a similar one to the banking analogy given
earlier:
You wish to transfer $100 from your savings account to your cheque
account:
UPDATE SAVINGS ACCOUNT
SET TOTAL = TOTAL - 100;
[AUTO COMMIT]
UPDATE CHEQUE ACCOUNT
SET TOTAL = TOTAL + 100;
[AUTO COMMIT]
As was pointed out earlier, if it works, fine. If it fails after
updating the savings account, it loses you $100 and the bank gains $100
as it has no idea where it came from - finders keepers. Now you might
think that the bank would do this instead:
UPDATE CHEQUE ACCOUNT
SET TOTAL = TOTAL + 100;
[AUTO COMMIT]
UPDATE SAVINGS ACCOUNT
SET TOTAL = TOTAL - 100;
[AUTO COMMIT]
I wonder if you can think of a reason why they wouldn't! 
Without autocommit it's fine and dandy whichever way around it works:
UPDATE CHEQUE ACCOUNT
SET TOTAL = TOTAL + 100;
UPDATE SAVINGS ACCOUNT
SET TOTAL = TOTAL - 100;
COMMIT;
Now, if it fails at any point in the transaction, your money is safe and
so is the bank's.
Remember, a transaction is a logically complete update that must succeed
or fail as a single unit.
Autocommit - just say NO!
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
There is one form of 'autocommit' that is very hard to turn off.
We regularly receive sql-scripts to run that contain a mix of DML- and
DDL-statements.
But still the single commit on the last line! 
Peter
Op 10-02-2010 10:56, Dunbar, Norman schreef:
Morning all,
late to the party as usual! I blame time zones!
What Jeff and Rich are saying, I think, is that committing
after every statement is usually a VERY BAD idea.
Oh yes indeedy, a very very bad thing. If you do this, you will
[eventually] end up with a trashed database.In fact, as an enhancement request, I'd suggest that the option be
dropped completely in future versions of Toad to avoid the distress that
comes with a commit half way through a script that means when it goes
wrong, the rollback is only partial - in other words, back to an unknown
point in the transaction rather than undoing the whole transaction.I wonder how many people out there in Toad Land actually turn autocommit
on?The example I much prefer is a similar one to the banking analogy given
earlier:You wish to transfer $100 from your savings account to your cheque
account:UPDATE SAVINGS ACCOUNT
SET TOTAL = TOTAL - 100;
[AUTO COMMIT]UPDATE CHEQUE ACCOUNT
SET TOTAL = TOTAL + 100;
[AUTO COMMIT]As was pointed out earlier, if it works, fine. If it fails after
updating the savings account, it loses you $100 and the bank gains $100
as it has no idea where it came from - finders keepers. Now you might
think that the bank would do this instead:UPDATE CHEQUE ACCOUNT
SET TOTAL = TOTAL + 100;
[AUTO COMMIT]UPDATE SAVINGS ACCOUNT
SET TOTAL = TOTAL - 100;
[AUTO COMMIT]I wonder if you can think of a reason why they wouldn't!
Without autocommit it's fine and dandy whichever way around it works:
UPDATE CHEQUE ACCOUNT
SET TOTAL = TOTAL + 100;UPDATE SAVINGS ACCOUNT
SET TOTAL = TOTAL - 100;COMMIT;
Now, if it fails at any point in the transaction, your money is safe and
so is the bank's.Remember, a transaction is a logically complete update that must succeed
or fail as a single unit.Autocommit - just say NO!
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.ukInformation 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
Morning Peter,
There is one form of 'autocommit' that is very hard to turn off.
We regularly receive sql-scripts to run that contain a mix
of DML- and DDL-statements.
Yes, we used to get those from vendors as well. However, I created a set
of scripting standards, had it approved and all our vendors now have a
copy - and get any updates as and when required - and they have been
told that any scripts that they supply which do not adhere to the
scripting standards will be rejected when a DBA QAs the script(s) before
application.
Once a couple of scripts have been rejected and thrown back for
correction, they soon wise up.
But still the single commit on the last line!
Glad it's not just us then! The use of a commit on the final line of a
DDL script shows a severe lack of understanding about exactly how Oracle
works - in fact, mixing DML and DDL shows this too. Our standards demand
that vendors do not put a COMMIT or ROLLBACK into any script.
They do put a prompt at the end telling the DBA that they should commit
if no errors occurred otherwise, rollback.
After all, how does the script writer know that our database has, for
example, got enough UNDO to cope with what turns out to be a huge
transaction generating lots and lots of UNDO and which blows away all of
the UNDO - then commits.
As we stipulate, the DBA applying the script(s) is the only one who
dictates whether a commit or rollback is required and appropriate. Which
means, also, that the script better have a spool at the
start and spool off at the end - of it fails QA as well!
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
Op 10-02-2010 11:22, Dunbar, Norman schreef:
dictates whether a commit or rollback is required and appropriate. Which
means, also, that the script better have a spool at the
start and spool off at the end - of it fails QA as well!Cheers,
Norm. [TeamT]
We're drifting a bit too off-topic I'm afraid, but talking about
spooling, I don't know if your standards mention spooling as well.
In the past we used an application and received vendor supplied scripts
every once in a while. They always spooled to "C:\temp".
very nice when running the scripts on a unix-box.
Well, enough ranting for today.
cheerio
Peter
Oracle provides a command for this CREATE SCHEMA. Basically you can wrap a collection of DDL commands and treat them as one transaction (all or one). It’s been in Oracle since at least Oracle 8.i.
Here’s the command description from the 8i docs:
CREATE SCHEMA
Purpose
Use the CREATE SCHEMA to create multiple tables and views and perform multiple grants in a single transaction.
To execute a CREATE SCHEMA statement, Oracle executes each included statement. If all statements execute successfully, Oracle commits the transaction. If any statement results in an error, Oracle rolls back all the statements.
I have a stored procedure that runs the following dynamic sql. When the sql is
run in the procedure it returns 0 rows. When the exact same sql string is run in
a standard editor window it returns 366 rows. I can see that the fetch is
working and the sql is being built to be correct. The value of v_SQL is exactly
what is expected, which is why I can run it in a standard editor window. It just
returns 0 rows inside the procedure. Any ideas? All of this is run on Toad 10.1
in debug mode.
This is just the beginning of the loop. The end loop exists later but there is
much more in the routine than this and this is the part that is failing again
and again.
OPEN Edits_Tab;
Loop
Fetch Edits_Tab into v_FieldName, v_Condition, v_Result, v_DataEditName;
Exit when Edits_Tab%NOTFOUND;
v_SQL := ‘(select count(*) ‘;
v_SQL := v_SQL||‘from ‘||UPPER(p_TableName)||’ t ‘;
v_SQL := v_SQL||‘where t.ERRFLAG=’‘0’’’;
if p_DataVrsn ‘ALL’ then
v_SQL := v_SQL||‘and t.DATAVRSN = ‘’’||p_DataVrsn||’’’ ‘;
end if;
v_SQL := v_SQL||’ and (’||UPPER(v_Condition)||’)) ';
execute immediate v_SQL into v_RowCount;
Thank you!!!
Eva
When you run code in PL/SQL you cannot inherit privs granted to roles within
that code – the grants must be made directly. It’s possible that
when you run as script it works because roles grants function as expected and it
does not when done as PL/SQL because you loose the roles grants ….
Not 100% sure this is your issue – but a common thing to check ….
Did you compile with debug?
If so, you should be able to see the value of v_SQL inside the loop as it
iterates by just putting your mouse over the text.
I’m wondering if your EXIT WHEN is getting fired prematurely…as you
step through the loop does it make it through the entire construct? You could
also see this if you ran the Profiler – it records each line of code
executed.
Hi Wim,
Hmmm. Sounds very nice. Never give this a thought. Sounds
good. Are those standards for the scripts available for the public?
http://qdosmsq.dunbar-it.co.uk/blog/?p=236
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
Peter,
We're drifting a bit too off-topic I'm afraid, but talking about
spooling, I don't know if your standards mention spooling as well.
Yes, if the script is "myFirstScript.sql" then the spool must be to
"myFirstScript.log" (or .txt or something else). Common spool files are
not permitted.
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