RE: functions/procedures and commits

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