How can we enable automatic transaction commit in PL/SQL?

In PL/SQL, automatic transaction submission can be achieved by setting the AUTOCOMMIT parameter.

The AUTOCOMMIT parameter controls the transaction behavior in PL/SQL. By default, the AUTOCOMMIT parameter is disabled, meaning that all SQL statements are executed within a single transaction and require manual commit or rollback.

To enable automatic transaction submission, you can use the SET AUTOCOMMIT ON statement at the beginning of the PL/SQL block as shown below:

SET AUTOCOMMIT ON;

In this way, after each SQL statement executes in the PL/SQL block, the transaction will be automatically committed. If you want to disable automatic transaction commit, you can use the statement SET AUTOCOMMIT OFF.

It is important to note that automatic transaction submission can lead to unexpected results, so it is necessary to carefully consider and test before using. Additionally, automatic transaction submission is only applicable for use with Oracle databases.