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).
