Re: Oracle sequence problem ... I think ?

Ed,

I am not an Oracle DBA, so from the top on my head I do not know what the default value for (cache = default) is. But at least for some tests you should maybe set it to nocache. That way Oracle will update the sequence table everytime ....which might slow you down a bit, but it just might get rid of your problem.

the oracle command to change to sequence would be: alter sequence ordernum nocache;

Also check the sequence number that Oracle is currently producing (select ordernum.nextval from dual). Maybe you have reached the maximum value specified ??? I do not see the sequence is set to "cycle" (ie. when it reaches the highest number it should start at 1 again).

Check out the following link for some more details on Oracle sequences:

http://www.jlcomp.demon.co.uk/faq/seq_loss.html

good luck,

Robert

"Ed Barrett" <ed.barrett_at_bigfoot.com> schreef in bericht news:a9ojng$ium$1_at_paris.btinternet.com...
> Robert,
>
> DBA Studio shows the sequence as being:
>
> ascending
> min = 1
> int = 1
> max = 1.0E27
> cache = default
>
> It all looks good to me ... any ideas?
>
> I am not an Oracle expert - is there a way I can see the actual SQL being
> issued inside the database once the sequence is resolved to check that
this
> is indeed the problem (although I can't think that it is anything else).
>
> TIA
>
> Ed
>
> "Robert Meerwaldt" <h.beekhuizen1NOSPAM_at_chello.nl> wrote in message
> news:jvCv8.19986$qp1.15022_at_amstwist00...
> > "ordernum.nextval" suggests that an Oracle sequence named ORDERNUM is
> being
> > used. ordernum.nextval by default issues a unique (last used value + 1)
> > number, unles you have created a very strange sequence (like with
> max_value
> > set to 1, cycle is on eg.)
> >
> > Best to check how the sequence has been defined, and see if the problem
> lies
> > there
> >
> > hth,
> >
> > Robert
> >
> >
> >
> > "Ed Barrett" <ed.barrett_at_bigfoot.com> schreef in bericht
> > 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 Sat Apr 20 2002 - 07:31:19 CDT