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:

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 Generator     Synonyms => used to provide alternate nam
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

Learn SQL From Scratch to Advanced topics

Learn SQL from Scratch to Advanced  Nowadays, many IT company moreover depend on SQL  only. So this is one of the basic query language to start for new developers. So track or to be master in SQL, the below topics should be covered. List of Basic Topics :- 1, Should know what is frontend and backend developer  2, What is DBMS, RDBMS ? 3, What is SQL ? 4, What are the database are there ? Beginner Level SQL topics  This are some beginner level SQL topics, if you are new to SQL this are the topics that should be covered first. 1, Oracle Basics  2, List of Objects in SQL   3 , Clause and its usage   4, Functions  5, Sub Query  6, Joins  7, Decode and Case 8, Constraints 9, Important Data Dictionary Table like user_source, all_source, user_tables etc.., Intermediate Level SQL topics (Creating Objects)  When we are entering into objects this below topics should be covered. In  this topics that we are creating database objects like tables, views, sequences, synonyms etc. 1, Data Definitio
Post a Comment
Read more