Hi Don,
Ok, here we go:
BEGIN TRANSACTION:
as previously mentioned, Oracle automatically starts a new transaction when you start attempting to execute SQL commands against the database. So if you have just COMMITted or ROLLedBACK, the next SELECT … FOR UPDATE, UPDATE, INSERT or DELETE (ie, DML commands - Data Manipulation Language) will begin a new transaction, as will SET TRANSACTION - whcih I’ve never seen used in any live situation!
Any ROLLBACKs or COMMITs will affect all data changed since the previous COMMIT or ROLBACK.
You can create a SAVEPOINT and give it a name, then partially undo a transaction by executing the command ROLLBACK TO SAVEPOINT X; where ‘X’ is the savepoint name:
CREATE TABLE TEST(A NUMBER);
INSERT INTO TEST(A) VALUES (1);
INSERT INTO TEST(A) VALUES (2);
INSERT INTO TEST(A) VALUES (3);
–
SAVEPOINT one;
–
INSERT INTO TEST(A) VALUES (4);
INSERT INTO TEST(A) VALUES (5);
INSERT INTO TEST(A) VALUES (6);
–
SAVEPOINT two;
–
INSERT INTO TEST(A) VALUES (7);
–
ROLLBACK TO SAVEPOINT two;
–
If you now SELECT * FROM TEST you will see that 1 through 6 are still there (but are as yet, uncommitted). 7 is now gone due to the ROLLBACK.
–
ROLLBACK TO SAVEPOINT one;
–
If you now SELECT * FROM TEST you will see that 1 through 3 are still there (but are as yet, uncommitted). 4 through 6 are now gone due to the ROLLBACK.
See also COMMIT below.
SAVE TRANSACTION:
Not an Oracle command, but see above where SAVEPOINTS are discussed.
COMMIT TRANSACTION:
Easy one this - just type COMMIT; There are numerous optional arguments to the COMMIT command, but the default handles most situations I’ve ever seen.
Be aware that COMMIT will get rid of all SAVEPOINTs created in the current transaction.
Be aware that any DDL (data Definition Language) such as CREATE TABLE, DROP TABLE etc, work by first executing a COMMIT statement, then doling whatever Oracle does internally to, for example, create a table, then if all was ok with the internal processing, will COMMIT a second time to make the internal changes stick. So this is a bad thing:
INSERT INTO TEST(A) VALUES (10);
INSERT INTO TEST(A) VALUES (12);
INSERT INTO TEST(A) VALUES (11);
–
SAVEPOINT one;
–
INSERT INTO TEST(A) VALUES (14);
INSERT INTO TEST(A) VALUES (15);
INSERT INTO TEST(A) VALUES (16);
–
SAVEPOINT two;
–
INSERT INTO TEST(A) VALUES (13);
–
CREATE INDEX TEST_INDEX ON TEST(A);
–
At this point, all the above changes have been COMMITted and no rollback or such like can undo the changes. All the rows from 10 through 16 are present and permanent in the table.
Never ever carry out DDL and DML in the same script/transaction if you wish to avoid interesting and unexpected problems. Ask me how I know? I worked in a contract where third part vendors didn’t know about this and expected a ROLLBACK to have some effect after they had created a few tables etc. Sigh!
IF @@ERROR:
This one is a bit more difficult. SQLPLus has a WHENEVER command which can trap SQL errors or OS errors etc, and take an action such as committing or rollingback and/or exiting with a given error code etc. However, this is specific to SQLPlus and is not an Oracle database feature.
Toad doesn’t use the WHENEVER command, so trapping SQL errors is a little more contrived, and the following carries on from the state of the TEST table above.
First create a primary key constraint, so we have some way of causing errors!
ALTER TABLE TEST ADD CONSTRAINT TEST_PK PRIMARY KEY (A);
Now, create an error logging table:
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(‘TEST’, ‘TEST_ERRORS’);
Now add a duplicate row so that we violate the primary key just created:
INSERT INTO TEST(A) VALUES (16);
The result is the following popup error alert from Toad:
ORA-00001: unique constraint (SYS.TEST_PK) violated
Now do it again, but this time with logging:
INSERT INTO TEST(A) VALUES (16)
LOG ERRORS INTO TEST_ERRORS (‘Oops, it broke!’) REJECT LIMIT 5;
What we have done here is tell Oracle to log any errors into the table TEST_ERRORS and not to abort the command(s) unless 5 or more errors occur IN THE SAME STATEMENT,
You will notice that no errors were raised, however, a SELECT * FROM TEST_ERRORS shows this:
1 ORA-00001: unique constraint (SYS.TEST_PK) violated
I Oops, it broke! 16
The final column is the PK column that caused the error. We can see that 16 was a bad value.
As noted above, the REJECT LIMIT only comes into play when there are more errors in THE SAME STATEMENT. If you run the above 20 times, nothing will happen, however:
This occurs:
ORA-00001: unique constraint (SYS.TEST_PK) violated
And selecting from the TEST_ERRORS table again, shows this:
1 ORA-00001: unique constraint (SYS.TEST_PK) violated
I Oops, it broke! 16
As before, but also the following new rows:
1 ORA-00001: unique constraint (SYS.TEST_PK) violated
I Multiple Errors! 1
1 ORA-00001: unique constraint (SYS.TEST_PK) violated
I Multiple Errors! 2
1 ORA-00001: unique constraint (SYS.TEST_PK) violated
I Multiple Errors! 3
1 ORA-00001: unique constraint (SYS.TEST_PK) violated
I Multiple Errors! 4
1 ORA-00001: unique constraint (SYS.TEST_PK) violated
I Multiple Errors! 5
1 ORA-00001: unique constraint (SYS.TEST_PK) violated
I Multiple Errors! 6
So you can see that it allowed 5 errors in the one statement, but error number 6 caused it to give up and abort.
Hope this helps.