Re: Oracle sequence problem ... I think ?

Actually, Robert, unless the sequence is set for NOCACHE, there's no hope (or reason to hope, I should say), that

T1 gets sequence 000400
T2 gets sequence 000401

even set to NOCACHE (highly not recommended for transaction oriented systems, like banking, telecom, etc), Oracle does NOT guarantee serialization, only uniqueness.

If serialization is critical, rather than uniqueness (or in addition to uniqueness), a DIY (Do It Yourself) gadget might be in order, as simple as a two-column table with a column for a serial number, and another 1 character or other indicator column.

A function or procedure like start_serial could retrieve the current value of the serial number, increment it by one,insert a new row with the new number as the PK, store the new row in the little table, and return the number to the calling procedure, and populate the indicator with 'N' or whatever you like before storing the row.

complete_serial would be called at all successful exits to the transacting LUW, and merely UPDATE WHERE the tracking table to set the value to Y.

Any failure exits would leave the value in the log table as N, meaning serial numbers that cannot be related to transactions unknown to the database.

For the lack of overhead involved, if one wishes to go that far, one might also add columns to store the SYSDATEs for the start_serial and complete_serial; and on the insert of course setting both to the SYSDATE at the time; any error exits would leave both of those columns with identical values.

But this is goopy thinking, Oracle sequence numbers work pretty reliably and I can't imagine recreating what they do, unless you want additional auditing functionality.

RSH. "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 Mon Apr 22 2002 - 14:29:44 CDT