Re: Strange transactional problem with my Oracle database?

From: Jim Kennedy <kennedy-family_at_attbi.com>
Date: Sat, 24 Aug 2002 03:17:20 GMT
Message-ID: <4VC99.59739$aA.12240_at_sccrnsc02>


Sql Server and Oracle are different databases. Sql Server assumes you want to "auto commit" Since ADO is a MS concept and really more designed with SQL Server in mind it does auto commit. (There is probably a setting to NOT autocommit, but the default is to auto commit.)

In SQL Server locks and transactions are bad things that prevent other people from doing the simplest of things. In Oracle this is not the case and things work as a database with ACID properties should. So in SQL Server it can be ACID, but you have to make sure you do things in very explicit ways to make it so. In Oracle it assumes you want to do it right.

Jim

"Brad Pybus" <brad_pybus7zwq_at_hotmail.com> wrote in message news:Uwu99.1253$Bd4.10550_at_dfw-service2.ext.raytheon.com...
>
> Ok.... I feel really stupid now. In SQL Server, if I wanted to do a
> transaction, I would say "BEGIN TRANS" and then "COMMIT TRANS". Whenever
 I
> interfaced to the Oracle database with ADO, everything would work just
 fine
> (apparantly ADO does COMMIT for you). But I never realized that Oracle
> automatically wraps everything in a transaction that you have to commit
 when
> using their SQL tools.
>
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D667240.E919252F_at_exesolutions.com...
> > Brad Pybus wrote:
> >
> > > I'm working with an Oracle database that was not created by me and I'm
 an
> > > Oracle newbie. I'm having a wierd problem where when I try to modify
 or
> > > delete records in SQLPlus worksheet, the changes don't stick. This
 happens
> > > on all the tables. For example, I will delete records in table A,
 then
 I do
> > > a select count(*) to confirm that those certain records were all
 deleted.
> > > It shows that they are. So then I exit SQLPlus worksheet, then I come
 back
> > > in and then it will show me that none of the records were deleted.
 Its
> > > almost as if SQLPlus worksheet starts a transaction the minute I enter
 it
> > > and then as soon as I close the SQLPlus worksheet, it rolls back the
> > > transaction. I tried deleting all the triggers on all the tables just
 in
> > > case that was the problem; It wasn't. Is there a setting somewhere
 that
> > > would cause this kind of behavior?
> >
> > Perhaps the concept of the COMMIT has not been explained to you.
> >
> > Inserting, updating, and deleting do nothing to the database. After they
 are
> > issued you must either COMMIT (to save the changes to the database) or
 ROLLBACK
> > (so the changes do not take effect).
> >
> > Daniel Morgan
> >
>
>
Received on Sat Aug 24 2002 - 05:17:20 CEST

Original text of this message