Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Name

SET AUTOCOMMIT

Synopsis

The AUTOCOMMIT setting controls whether SQL*Plus automatically commits changes you make to the database, and it controls how often those changes are committed.

Syntax

SET AUTO[COMMIT] {OFF | ON | IMMEDIATE | statement_count}

Parameters

SET AUTO[COMMIT]

Is the command, which may be abbreviated SET AUTO.

OFF

Turns off autocommit and requires you to commit (or roll back) changes manually. This is the default setting.

ON

Causes SQL*Plus to issue a COMMIT after each successful SQL statement or PL/SQL block you execute.

IMMEDIATE

Has the same effect as ON.

statement_count

Causes SQL*Plus to issue a COMMIT after successfully executing the specified number of SQL statements or PL/SQL blocks. This value may range from 1 to 2,000,000.

Examples

When you set autocommit to occur after a specified number of successful SQL statements, be aware that manually executing a COMMIT, a ROLLBACK, or another SET AUTOCOMMIT command will cause the counter to be reset back to zero. Take a look at the following example:

SET AUTOCOMMIT 5 DELETE FROM project_hours WHERE employee_id = 101 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 102 AND project_id = 1001; COMMIT; DELETE FROM project_hours WHERE employee_id = 103 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 104 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 105 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 106 AND project_id = 1001; DELETE FROM project_hours ...