Skip to main content

Class 2.3 Statements in Oracle TCL

Statements in Oracle TCL

Statements in Oracle TCL is one of the most important part in the subset of Oracle statements. TCL means Transaction Control Language.

There is more chance that there will be a interview questions in this section.

Before reading this article one should know more differences between DDL and DML commands which are already posted before.

What is mean by Transaction ?

Transaction means an event that takes place between two commit points, For example: In Retail words, the transaction means buying and selling any goods so that complete event is called as transaction.

transaction control language

What is mean by Failed Transaction ?

Failed Transaction means thus that event is failed to achieve the commit point after the event starts. For example: If you sending any amount in online transaction means due to some technical issues there will be failed to achieve the commit points in other side so that event is called as failed transaction.

This TCL is mainly related to the above two questions only.

List of Statements in Oracle TCL

The List of Transaction Control Language statements in oracle are:
  • COMMIT
  • ROLLBACK
  • SAVE POINT

Importance of Commit in TCL

- Commit is used to end the transaction and save the changes whatever we done that which are done in the previously in that same session.

- If you failed to give commit means the changes will not be happened in same scenario.

When this Commit is required ?

Commit Statement is required whenever we are using any DCL (Data Control language) means we will use Commit statement, because they are manual commit statement.

Whereas DDL (Data Definition Language) is auto commit statement so it doesn't required any commit statements to this scenario. 

Let's take two different session,

SESSION 1 :

CREATE TABLE trans (id NUMBER );

ddl statements are auto commit

DDL is auto commit statement so no need to give commit, while using other session.

SESSION 2:

ddl statements are auto commit statements

Here both session 1 and session 2 are showing the DDL part, they are auto commit statement, so it will automatically ends the transaction in data base itself.

Two Different Session Using DML statements

SESSION 1:        => NO COMMIT

DML statements require commit

In figure, we have inserted 3 records, which is DML operations are performed so, if we failed to give commit means it will rollback, while viewing in another session.

SESSION 2: 

DML statements are manual commit
No data found in that same table.

AFTER COMMITING in SESSION 1:

Statements in Oracle TCL

Now SESSION 2 can able to view the records which was created in session 1.

Transaction Control Language

TRY IT BY YOURSELF
  • Create a table oracle with columns as item NUMBER
  • Now Insert three records 1, 2, 3
  • DELETE record 3
  • Now Insert two records 4, 5
  • Rename the column from item to new_item
  • Now Insert two records 6 and 7
  • Delete record 7 
Now find how many records are in another session, check it.... ?????

Important Note

In some cases other than commit we can end the transaction by using this keywords like,
  • Commit
  • Exit
  • Using DDL command
In PLSQL block this is very important point, whenever you are using any DML statements means, commit is required one.
ROLLBACK
Rollback is very similar to undo like process, while we are doing any DML operations means, at the time if we need to do undo means, just use this rollback command to go to the implicit save point. 

During this situation there should not be use any commit or DDL operations else it wont rollback.

If it is once roll backed means it can't be redo.

SAVE POINT
Save point is used to rollback the particular transaction where the save point are available.

For Example:

INSERT INTO trans VALUES (8);
INSERT INTO trans VALUES (9);

SAVEPOINT S1;

INSERT INTO trans VALUES (10);
INSERT INTO trans VALUES (11);

SAVEPOINT S2;
SERT INTO trans VALUES (12);
INSERT INTO trans VALUES (13);

SAVEPOINT S3;

DELETE FROM trans
WHERE id = 10;

SAVEPOINT S4;

INSERT INTO trans VALUES (20);

SAVEPOINT S5;

Now,
ROLLBACK to S2; 	

statements in oracle TCL Savepoint



Points to remember:
- Whenever you are using any DML operations means it will be very important to commit the transaction.


Conclusion

I hope you have understand, and got some ideas related to the article, so if you having any doubts or queries means kindly comment below or reach out to our official mail id.

Interview Questions

1, Can we use TCL in trigger (PLSQL) ?
2, What happens when the transaction is not committed means ?
3, What is Transaction ?
4, What is Save point ?
5, What is Rollback ?
6, Difference between Roll back and Rollback 2 ?


Labels

Labels:

Comments

Post a Comment

Thanks for commenting, we will always respond your comment.

Popular posts from this blog

Oracle SQL Interview Questions Series 1 [Basic]

Oracle SQL Interview Questions Series - 1 Oracle SQL Interview Questions covered in Eduflee, so complete all the series to get placed into the IT company. At present Structured Query Language is very important to enter into the Software company. After completing all the Interview series, you can crack many interviews with 100% assurance from my experience. Basic Questions for SQL interview Schema => User Records => Rows Fields => Columns Result set => Output 1, What are the Schema Objects in Oracle SQL ?     Table, View, Materialized view, Sequence, Synonyms, Index are the schema objects in SQL.     Table => Basic Unit of Storage [Rows and Columns]     View => Logical Representation of subject of records, It does not contains any data. So here data is logical.     Materialized View =>   It contains the data, it is also called as Snapshot. It contains data physically.     Sequence => It is a Numeric Value...
Post a Comment
Read more

Class 3 Functions in Oracle SQL

Functions in Oracle SQL Functions is one of the important features in Oracle SQL, with the help of this functions we can able to get the result as per our requirement. Functions in Oracle SQL contains Single Row Functions, Multiple Row Functions. Mainly those functions are used to returns value based on the input that which we given. And practicing this functions in oracle SQL, one can able to master in query writing. Functions is used to perform some actions like doing any calculations, grouping the data, conversion in type, date conversion etc. Types of Functions in Oracle SQL Functions in oracle is classified on the basis of the input which we are giving and getting the output, and they are: - Single Row Functions - Multiple Row Functions Single Row Functions in Oracle SQL Single Row Functions works on the single rows which will shows the result for that single rows only. If "n" number of inputs to give "n" number of outputs. From the above figure explains about ...
Post a Comment
Read more

Regular Expression in Oracle SQL

Regular Expression in Oracle SQL Regular Expression in Oracle SQL is a pattern matching technique to retrieve sort of data based on the conditions given in Oracle database. Pattern Matching is similar to functions like, replace, substring and in string which are previously seen topics. At the end of this article there will be certain task to practice for this Regular Expressions. It is very important to be very clear about this topic, this are very helpful for many interview Questions. Using regular expressions with Oracle Database: Overview Regular Expression is one of the easiest way to learn with clear understanding if one are familiar with the below Basic string matching patterns, Quantifiers, Meta characters, Functions etc. Note : The above points should understand clearly means it will be very easy to write a query. Basic String Matching with Regular Expressions | Either or  () Group \ Escape ^ Start $ Stop Literals of regular expression in oracle sql [ a b c ] a, b, c [...
Post a Comment
Read more