Oracle - What Statements Need to Be Committed

oracle - what statements need to be committed?

DML (Data Manipulation Language) commands need to be commited/rolled back. Here is a list of those commands.

Data Manipulation Language (DML) statements are used for managing data
within schema objects. Some examples:

INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

Usage of Commit Statement

If you run a DDL statement (CREATE, ALTER, DROP, GRANT, etc) then:

Oracle Database implicitly commits the current transaction before and after every DDL statement.

If you run a DML statement (INSERT, UPDATE, DELETE, SELECT, CALL, MERGE) then:

These statements do not implicitly commit the current transaction.

and you will need to manually commit the uncommitted transactions (but you do not have to COMMIT after every statement).

If you want to perform a partial rollback then you can use SAVEPOINTs.

You should not put COMMIT statements in functions or procedures so that you can use multiple functions/procedures in a single transaction and then COMMIT or ROLLBACK that entire transaction.

Oracle: Need to commit after CREATE OR REPLACE FORCE EDITIONABLE VIEW

No, DDL statements automatically commit in Oracle

Which are all the statements will lead to commit in a PL/SQL procedure?

All DML statements (INSERT/DELETE/UPDATE/MERGE) don't commit in PL/SQL.

All DDL statements do commit (ALTER/CREATE...), even if the statement fails. If you're running a dynamic statement (EXECUTE IMMEDIATE) that runs a DDL, this will also commit your transaction.

Some DBMS packages also have procedures that commit, for example DBMS_STATS. It would be quite cumbersome to list them all. Read the appropriate documentation when you're using a DBMS package.

Why store procedure don't need commit statement in oracle

Mr Son. There is a thing called "good practices", and regarding "commit" with stored procedures, there are several points:

• The user, who calls your SP, controls the transaction. He should decide what he wants - commit or rollback. Not us. Your SP could be just a small part of bigger process;
• Many frameworks, have build in transaction controls - front-end developers can control the flow in applications/etc.

Is COMMIT required after every EXECUTE IMMEDIATE?

The only times that you're really forced to commit, other thasn at the end of a business transaction, are:

1. When executing DDL: the DDL execution is wrapped in a pair of implicit commits.
2. After direct path insert: the table cannot be read until the insert is committed.

As horsey comments, the correct point to commit at is when the business transaction is complete. Otherwise, you need to be writing yourself some code to detect and fix partially completed and commited transactions that have left the database is a logically inconsistent state (eg. An INVOICE record exists without any INVOICE_DETAIL records).

Does an insert trigger need a commit statement

Not only do triggers not need a COMMIT you can't put one in: a trigger won't compile if the body's code includes a COMMIT (or a rollback).

This is because triggers fire during a transaction. When the trigger fires the current transaction is still not complete. As COMMIT terminates a transaction allowing them in triggers would break the unit of work.

So changes executed in a trigger are committed (or rolled back) by the owning transaction which issued the DML that fired the trigger.

---

It is true that triggers can run under the PRAGMA AUTONOMOUS_TRANSACTION, in which case they must have a COMMIT. But this is an edge case, as there are few meaningful uses of nested transactions in Oracle.

What is the effect of placing the commit after DML in procedure?

What is the effect of placing the commit like this in program?

The Oracle Documentation describes COMMIT as:

Purpose

Use the COMMIT statement to end your current transaction and make
permanent all changes performed in the transaction. A transaction is a
sequence of SQL statements that Oracle Database treats as a single
unit. This statement also erases all savepoints in the transaction and
releases transaction locks.

If you have three PROCEDURE and each one contains a COMMIT statement then you cannot run all three then, if an exception occurs in a latter one, ROLLBACK them all as the changes from the first two will already be COMMITted.

As a general rule, you should not use COMMIT in a PROCEDURE or FUNCTION but should leave it up to the caller to COMMIT the transaction so they can bundle multiple actions together.

There are, of course, use cases where you will want to violate this rule but you should consider each case separately and take time to fully understand your business logic before you break this rule so you know what is COMMITted in each instance.

Should I make this transaction as AUTONOMOUS_TRANSACTION?

One use-case is logging - you may have a PROCEDURE which calls another PROCEDURE to log the user's actions and, regardless of whether the initial action succeeds or fails you want to keep a log of the action and ensure that log is COMMITted. In this case, the logging PROCEDURE should be an AUTONOMOUS_TRANSACTION and contain a COMMIT statement and the calling statement should (probably) have neither.

So, if the COMMIT of one PROCEDURE is always required and is independent of whether the caller COMMITs other data then make the PROCEDURE an AUTONOMOUS_TRANSACTION. If the PROCEDUREs can be bundled together and then ROLLBACK as a group then you do not want to make them AUTONOMOUS_TRANSACTIONs.

---

Related Topics