RE: functions/procedures and commits

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: john.dunn_at_sefas.co.uk

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: ListGuru_at_fatcity.com (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: NDATFM_at_labor.state.ny.us

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: ListGuru_at_fatcity.com (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: Dan.Fink_at_mdx.com

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: ListGuru_at_fatcity.com (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: NDATFM_at_labor.state.ny.us

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: ListGuru_at_fatcity.com (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: Dan.Fink_at_mdx.com

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: ListGuru_at_fatcity.com (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).