Re: Oracle sequence problem ... I think ?

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 Sun Apr 21 2002 - 02:17:04 CDT