CODEWITHSUNDEEP
oracle-database
Rama Sarma
Rama Sarma

Reputation: 11

Does Insert require a commit statement

I am inserting few records into an oracle table. Does it require "COMMIT" statement. if Yes, in that case, If I am inserting 10 different rows, do I need to give commit after every insert statement.

Thanks

Upvotes: 0

Views: 7150

Answers (3)

Carles
Carles

Reputation: 1

I'm not sure it requires a commit anymore. I inserted rows in a table, closed the connection without any commit (I used both disconnect and exit to ensure that I was out), I connected to the account again...and the rows were there. I don't know if it depends on the version...

Upvotes: 0

hiv014
hiv014

Reputation: 1

It has some different from the way to using 'commit' keyword. Here is some example cases: exp 1:

insert into tableA(colA) values(valA);
commit;
insert into tableA(colA) values(valB);
commit;

=> If 2nd of insert statement got any error but 1st of insert statement still be inserted successfully into DB.

exp 2:

insert into tableA(colA) values(valA);
insert into tableA(colA) values(valB);
commit;

=> If 2nd of insert statement got any error then 1st of insert statement could not be inserted successfully into DB.

So, it's depended on your purposed to do with its.

Upvotes: 0

Sandesh Gupta
Sandesh Gupta

Reputation: 1195

Yes. You need to commit after insert in Oracle. For multiple statements, one commit is enough at the end of all inserts

Example:

--Example 1

Insert into table(col1) values(val1);
commit;

--Example 2
Insert into table(col1) values(val1);
Insert into table(col2) values(val2);
Insert into table(col3) values(val3);
Insert into table(col4) values(val4);
commit;

Both are legal. Example 1 will insert 1 row and Example 2 will insert 4 rows.

Upvotes: 2

Related Questions