Re: Use of COMMIT WORK;

A copy of this was sent to David Wright <dww_at_nortelnetworks.com> (if that email address didn't require changing) On Mon, 30 Aug 1999 00:25:20 +0100, you wrote:

>According to the SQL-92 standard, the command COMMIT WORK will
>commit all outstanding transactions, and subsequent ROLLBACK
>commands can only roll back to the most recent COMMIT.
>
>So far so good. But what happens in practice when you're
>entering SQL "conversationally" through the Oracle text window
>(Sorry I forget what Oracle call it), or similarly for Tandem SQL
>with sqlci?

do you mean sqlplus?

>
>How does the system know you want to use atomic transactions BEFORE
>you do the first COMMIT WORK? Do you have to do a COMMIT at the start
>to set up a voidable transaction, or does it assume you always do, and
>not commit anything at all until you tell it to?
>

a transaction in Oracle begins with the first statement. It continues until you issue COMMIT or ROLLBACK explicity OR implicity (eg: DDL in Oracle does an implicit commit before and after the statement executes, so a CREATE or ALTER statement will end your transaction and begin a new one).

Certain environments, such as sqlplus or jdbc, *may* do an autocommit. for example, in sqlplus I can issue:

SQL> set autocommit on

and that will cause an implicit commit to be issued after each and every statement. by default in sqlplus, this is *not* the cas.

>But in that case, what happens if you never type COMMIT?
>Does it do an auto commit when you exit the program?

depends on the program. sqlplus does issue a commit before exiting by default.

>What about before that, e.g. if you're running a script
>(open script.sql;) does it commit at the end of each script file?
>

depends on the program. sqlplus does NOT commit after each script.

>[I have to put setup data from a master database into two separate
> systems, one running in Oracle 7 on an RS/6000 and the other on
> a Tandem nonstop SQL server on an S700 with Guardian NSK;
> both are supposed to conform to SQL standards.]
>
>Regards,
> David Wright Harlow UK http://www.northern.co.uk/~dww/
> ***Boycott Internet Spam*** http://spam.abuse.net/

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 30 1999 - 08:06:53 CDT