I am trying to utilize transaction functionality in Oracle SQL for the first time and can't seem to find a good explanation. I understand that starting a new session will begin a new transaction. I also understand that commit/rollback is used to end it. What I am trying to do is execute two statements and if I either of them fail, undo any changes they might have made and continue with execution. How can I check for this condition and issue a commit or rollback accordingly?
+3
A:
Use a PL/SQL block and write something like this:
begin
statement_zero;
savepoint my_savepoint;
begin
-- if either of these fail, then exception section will be executed
statement_one;
statement_two;
exception
when others then
rollback to my_savepoint;
end;
statement_three;
commit;
end;
See also http://www.adp-gmbh.ch/ora/concepts/transaction.html
ObiWanKenobi
2010-10-11 17:35:42
Ah ok, thanks, looking into blocks a bit more I discovered another issue with specific to what I'm trying to do. I am trying to add a column to a table, set the default values of all current entries and set a trigger to update this value as needed. Blocks only allow SELECT, INSERT, UPDATE, DELETE. Any ideas for wrapping this in a transaction?
dpsthree
2010-10-11 18:18:44
DDL statements (CREATE, ALTER, DROP, etc.) implicitly COMMIT before and after executing. So the actions you are referring to cannot be performed in a single transaction, and cannot be rolled back.
Dave Costa
2010-10-11 18:21:18
You can, by the way, execute DDL statements within a PL/SQL block using dynamic SQL, e.g. `EXECUTE IMMEDIATE 'ALTER TABLE foo ADD x NUMBER'`. But this does not change the fact that the DDL statement does a COMMIT before and after updating the data dictionary.
Dave Costa
2010-10-11 18:22:44
thanks for the help guys, spent hours googling trying to piece this together. Much appreciated
dpsthree
2010-10-11 18:25:56
+2
A:
Along with a nice exaplample ObiWanKenobi provded a detailed explanation of Oracle transactions can be found at Chapter 4 of Oracle Concepts guide (the link I've provided goes for 10.2, you can find the doc suitable for your version at Oracle website as well). I suggest you read this chapter to understand how Oracle handles transaction management, and the doc at whole is very good piece of information for undestanding how Oracle DB work.
andr
2010-10-11 20:21:40