AUTOCOMMIT controls COMMITs after execution of individual DML statements,
not upon EXITing SQL*Plus.
The point I want to make is that IMPLICIT COMMITs & ROLLBACKs do occur. I
have encountered the following scenario more than once...
'Oracle is not saving changes' is the complaint from the users. In looking
at the code, and having confidence that Oracle does not randomly decide to
not commit (barring a bug that I have yet to encounter, though possible), I
see that the process is performing multiple DML statements. One of the
statements fails, but the error is not trapped, and the program performs a
graceful exit (COMMITting the changes without the command COMMIT being
issued).
I also recall from several years ago, that Forms always issued a ROLLBACK
when leaving the Form. If you did not explicitly code a COMMIT, an IMPLICIT
(to you) ROLLBACK was performed.
Dan Fink
-----Original Message-----
Sent: Thursday, January 02, 2003 11:24 AM
To: Multiple recipients of list ORACLE-L
Dan,
I agree with you and I think I said the same thing - though not as lengthy!
:)
As you said, the Sql*Plus AutoCommit option controls issuing a commit upon
exiting the program.
As I said in a prior mail, I did not mention DDL forcing an implicit commit
as the question pertained to INSERT statements.
At least I got people answering the question! :)
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Thursday, January 02, 2003 11:35 AM
To: Multiple recipients of list ORACLE-L
Tom,
I must respectfully disagree. Explicit = a COMMIT or ROLLBACK is
explicitly issued by the user/program. Implicit = a COMMIT or ROLLBACK is
performed as the result of an action and not issued by the user/program.
The SQL*Plus options AUTOCOMMIT and COPYCOMMIT do not control EXIT
behavior. For the EXIT/QUIT commands, the default behavior is COMMIT. With
WHENEVER OSERROR or SQLERROR, the behavior can also be altered to perform a
COMMIT or ROLLBACK. Is there another option you are thinking of?
COMMIT or ROLLBACK is tied very closely to TRANSACTIONs. In order to
start a new transaction, the previous transaction must end. A COMMIT
indicates a succesful end, while a ROLLBACK indicates a failure. Please keep
in mind that this is not always coded in this manner, i.e. unhandled
exceptions in PL/SQL followed by COMMIT in calling procs. DDL exists as a
separate transaction. In order to start the DDL transaction, the previous
transaction must end. Oracle terminates the previous transaction by
COMMITting the changes and begins a new tx.
I used a small anonymous PL/SQL block to test COMMIT/ROLLBACK
behavior. I found it to be consistent with how I left SQL*Plus. If I killed
the window, the change was not committed. If I typed 'EXIT', the change was
committed.
Dan Fink
-----Original Message-----
Sent: Thursday, January 02, 2003 8:10 AM
To: Multiple recipients of list ORACLE-L
John,
there is no such thing as an implicit commit within Oracle.
the only implicit commit that I know of is during a sqlplus session when you
exit the program. even this is "settable" by a sqlplus option.
distributed transactions that are controlled by a transaction coordinator
(like MS DTC) might issue commits only because the web application requires
all updates to be handled by the app-server. but this is different from
what you are asking, I think.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Thursday, January 02, 2003 9:14 AM
To: Multiple recipients of list ORACLE-L
> Under what circumstances is a COMMIT done implicitly?
>
> If I call a function or procedure that performs an insert, but does not do
> a commit, will a commit be implicitly performed when the function ends?
>
> i.e. is ...
>
> begin
>
> INSERT INTO
> ... etc.
>
> end;
>
> the same as
>
> begin
>
> insert_the_record;
>
> end;
>
> where insert_the_record is a procedure that does the insert, but nothing
> else.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Dunn
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
