Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: keep select in transaction

Re: keep select in transaction

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 15 Apr 2005 06:40:14 -0700
Message-ID: <1113572190.514825@yasure>


Rauf Sarwar wrote:
> Rene Nyffenegger wrote:

 >
> My understanding of the word "Transaction" in Daniel's post was a
> single DML which involves either select, insert, update or delete.
> You on the other hand read "Transaction" as a single PLSQL call. Two
> completely different things as you may open/close/commit/rollback one
> to many DML's in a single PLSQL call.
>
> That is the confusion as I see it.
>
> Regards
> /Rauf

Based on my understanding of your understanding of my understanding, etc. you are wrong on all counts.

if a single DML statement, or PL/SQL call doesn't end with either COMMIT or ROLLBACK the statement has not ended. What happens next is still part of the same transaction.

BEGIN
   INSERT INTO t (pid) VALUES (1);
   COMMIT; -- one transaction ... one statement

   INSERT INTO t (pid) VALUES (1);
   INSERT INTO t (pid) VALUES (1);
   ROLLBACK; -- one transaction ... two statements

   INSERT INTO t (pid) VALUES (1);
   UPDATE t SET pid = 2 WHERE rownum = 1;    DELETE FROM t WHERE pid <> 2;
   COMMIT; --one transaction ... three statements END;
/

But then maybe you are referring to another Daniel and I am totally confused about the entire thread.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Apr 15 2005 - 08:40:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US