Re: Oracle sequence problem ... I think ?

Guys,

All very good points and taken on board.

What I still cannot understand is that when two transactions are run serially (from where I stand as a human - one runs and then the other a few seconds later) that the sequence number is the same.

That is why I asked if there was any way of seeing what the actual SQL is (I am running through a JDBC driver).

Alternatively, is there a way to switch a log on in Oracle so that all failed SQL statements and the reason for failure is outputted?

regards

Ed

"RSH" <RSH_Oracle_at_worldnet.att.net> wrote in message news:QHtw8.38141$Rw2.2887272_at_bgtnsc05-news.ops.worldnet.att.net...
> Remember, the Oracle Sequence function is designed to produce UNIQUE
> numbers, not necessarily sequential numbers assigned to each transaction
in
> order in which they were made. Employing the CACHE feature reduces disk
> overhead but does not necessarily ensure any better luck with
serialization.
> (Though with any cache enabled at all, any unused (unrequested/unrequired)
> sequences vanish when the DB is brought down or it crashes, and the next
> block of 10 or 100 or whatever is preallocated in memory.)
>
> If you need serialization as distinct from uniqueness, you might wish to
> consider SYSDATE to populate a DATE column in your record, and by all
means
> also use a column populated by a sequence number as well; the granularity
of
> DATE would generally preclude any two people buying Angelfish at the same
> instant, certainly checking out from the same cash register; adding
> STORE_ID, POS_ID, EMP_ID to that would pretty much conclude the deal, I
> should think.
>
> An ON INSERT trigger on your sales table could be conveniently used to
> populate the DATE and whatever you call your sequence number column..
>
> Depending on how large your application design and architecture are, the
> sequence number could be used as a convenient way to denormalize and
> collapse keys instead of carrying them all through the entire database.
>
> RSH.
>
>
> "Ed Barrett" <ed.barrett_at_bigfoot.com> wrote in message
> news:a9mrhp$rsd$1_at_knossos.btinternet.com...
> > I am posting here in the hope that I have hit a simple Oracle
> configuration
> > issue - one that is not simple to me !!
> >
> > I have written a test to perform the same functions twice in the Java
> > reference application - Pet Store (add an AngelFish to a basket, go to
> > checkout, log on, commit purchase, log out).
> >
> > When I run the test and tell it to run twice sequentially I get the
> > following error on the second commit: (please scoll down for more
> > information)
> >
> > <18-Apr-02 16:54:16 BST> <Error> <HTTP>
> > <[WebAppServletContext(1628959,petstore,
> > /petstore)] Servlet failed with ServletException
> > javax.servlet.ServletException: MainServlet: unknown exception:
> > com.sun.j2ee.blu
> > eprints.petstore.control.exceptions.EStoreAppException
> > at
> > com.sun.j2ee.blueprints.petstore.control.web.MainServlet.doProcess(Ma
> > inServlet.java:125)
> > at
> > com.sun.j2ee.blueprints.petstore.control.web.MainServlet.doGet(MainSe
> > rvlet.java:91)
> > at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
> > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > at
> > weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubIm
> > pl.java:265)
> > at
> > weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubIm
> > pl.java:200)
> > at
> > weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppSe
> > rvletContext.java:2495)
> > at
> > weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestIm
> > pl.java:2204)
> > at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
> > at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
> > >
> >
> > Through investigation I have identified that the underlying exception is
> > being thrown from the OrderDAOOracle class in the insertOrder method. I
> > have trapped the SQL statements beign issued and they are identical with
> the
> > order number being generated with "ordernum.nextval".
> >
> > Would I be right in thinking I have hit a duplicate key (row?) error and
> if
> > so anyone care to guess why? Is there a problem with Oracle
sequencing -
> > these tests are running sequentially as opposed to in parallel ?
> >
> > Regards
> >
> > Ed
> >
> >
> >
> >
>
>
Received on Mon Apr 22 2002 - 10:04:56 CDT