Do you want to commit after a certain period of time? Is it through just SQL*Plus or through a program or SQL*Load? You need to give more details otherwise the answer to your question is already given :-)
Alla S. Pfauntsch
"Life is what happens while you are planning something else".
more infor for you guys........
Our software interacts with Oracle and we want to find out how we can set up and turn off auto commit apart from within SQL*PLUS. Informix have auto commit as default and we can override it when we start a new transaction from our software. Can I do the same in Oracle ? Or I have to explicitly set auto commit off?
Each time you enter sqlplus ORACLE looks for a file in your current directory called login.sql. In this file you can place any SQL*Plus command that you wish to be globally applied to your work. Thus to save typing in common format or page commands at each session they can all be placed in a file called 'login.sql'. So you can add the param set autocommit on. A typical file might be:
SET PAGESIZE 22
SET PAUSE ON
SET PAUSE "Press <ENTER> for next page"
SET PAUSE "Press <E>
SET NUMF 999999
COL ENAME FORMAT A15
COL SAL FORMAT 99,999.99
If you enter sqlplus from a directory level other than that in which the file login.sql has been placed, ORACLE will not find it and the settings will not be applied.
P.s autocommit is not recomanded.