# Equivalent commands for Oracle SQL **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [October 2, 2015, 6:38am UTC](https://forums.toadworld.com/t/equivalent-commands-for-oracle-sql/34217 "2015-10-02T06:38:26Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![donald.wilson](https://avatars.discourse-cdn.com/v4/letter/d/ecccb3/32.png) [@donald.wilson](https://forums.toadworld.com/u/donald.wilson)\ **Post date:** [October 2, 2015, 6:38am UTC](https://forums.toadworld.com/t/equivalent-commands-for-oracle-sql/34217/1 "2015-10-02T06:38:26Z")
Hi group, I’m taking a short intro course in SQL (yes, I’m kind of new to writing SQL commands). Today I’m learning about “Transaction Processing” (BEGIN TRANSACTION, SAVE TRANSACTION, IF @@ERROR, COMMIT TRANSACTION). The course suggests to check with the DBMS to see if it uses these commands. I’ve looked around to see if Oracle uses these specific commands and I don’t see them (or any examples where they may be used). So my question is: Does Oracle (and TOAD) use these commands? If not, are their some equivalents? Could you give some examples of how these equivalents are used? In advance, thanks for your help. Don ---
**Author:** ![kevinstern](https://avatars.discourse-cdn.com/v4/letter/k/3ab097/32.png) [@kevinstern](https://forums.toadworld.com/u/kevinstern)\ **Post date:** [October 2, 2015, 5:28pm UTC](https://forums.toadworld.com/t/equivalent-commands-for-oracle-sql/34217/2 "2015-10-02T17:28:02Z")
Hi Donald - you’ve come to the right place! There is a Wiki Page here on Toad World that goes over just that. Take a look and let us know if you have any more questions. ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [October 3, 2015, 12:40am UTC](https://forums.toadworld.com/t/equivalent-commands-for-oracle-sql/34217/3 "2015-10-03T00:40:49Z")
Hi Donald. Oracle does not use or have most of those. It does have commit to end a transaction. A new transaction is started for you when you login. Another is started for you after you commit or rollback the current one. Oracle will automatically commit - twice if all goes well - whenever you do some DDL such as create table, drop index etc. This causes your existing transaction to be committed and a new one started. Never mix DDL and DML. Oracle also let's you write PL/SQL code that starts an autonomous transaction which can be committed or rolled back without affecting your current transaction. There is error handling in SQL and PL/SQL. HTH. Cheers, Norm [TeamT] On 3 October 2015 08:05:08 BST, "donald.wilson" [bounce-donaldwilson@toadworld.com](mailto:bounce-donaldwilson@toadworld.com) wrote: > **[Equivalent commands for Oracle SQL](http://www.toadworld.com/products/toad-for-oracle/f/10/t/25543)** > Thread created by donald.wilson > Hi group, > I'm taking a short intro course in SQL (yes, I'm kind of new to writing SQL commands). Today I'm learning about "Transaction Processing" (BEGIN TRANSACTION, SAVE TRANSACTION, IF @@ERROR, COMMIT TRANSACTION). The course suggests to check with the DBMS to see if it uses these commands. > I've looked around to see if Oracle uses these specific commands and I don't see them (or any examples where they may be used). So my question is: Does Oracle (and TOAD) use these commands? If not, are their some equivalents? Could you give some examples of how these equivalents are used? > In advance, thanks for your help. > Don > To reply, please reply-all to this email. > > [Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/f/10/t/25543?subscribe=false) on this subject. > > Or [Unsubscribe from Toad for Oracle - General](http://www.toadworld.com/products/toad-for-oracle/f/10?subscribe=false) notifications altogether. > > Toad for Oracle - Discussion Forum > [Flag](http://www.toadworld.com/products/toad-for-oracle/f/10/t/25543?AbuseContentId=797b6fb9-654f-4818-8e41-a3119ba7a7db&AbuseContentTypeId=46448885-d0e6-4133-bbfb-f0cd7b0fd6f7&AbuseFlag=true) this post as spam/abuse. -- Sent from my Android device with K-9 Mail. Please excuse my brevity. ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [October 3, 2015, 12:43am UTC](https://forums.toadworld.com/t/equivalent-commands-for-oracle-sql/34217/4 "2015-10-03T00:43:10Z")
Sorry about the lack of examples. I’m replying on my phone. If I remember and nobody else has done it, I’ll post some examples on Monday. Cheers, Norm [TeamT] – Sent from my Android device with K-9 Mail. Please excuse my brevity. ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [October 5, 2015, 6:16am UTC](https://forums.toadworld.com/t/equivalent-commands-for-oracle-sql/34217/5 "2015-10-05T06:16:22Z")
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. SQL_PLus 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 SQL_Plus 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. ---
**Author:** ![donald.wilson](https://avatars.discourse-cdn.com/v4/letter/d/ecccb3/32.png) [@donald.wilson](https://forums.toadworld.com/u/donald.wilson)\ **Post date:** [October 5, 2015, 1:14pm UTC](https://forums.toadworld.com/t/equivalent-commands-for-oracle-sql/34217/6 "2015-10-05T13:14:52Z")
Norm, This is EXCELLENT info. Thank you so very much. I’ll use this and go “play” and learn. This will be a BIG help! Don