File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes JDBC Driver, Oracle sequence number problems Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "JDBC Driver, Oracle sequence number problems" Watch "JDBC Driver, Oracle sequence number problems" New topic

JDBC Driver, Oracle sequence number problems

K DeLucia
Ranch Hand

Joined: Apr 11, 2008
Posts: 68
I have a web application where users enter some data onto a jsp page. When they submit, a record is created in an Oracle database. I need a sequencial 'Request Number' for my form and I came upon sequences in Oracle. Sounds like it will do just what I need. However, apparently there is a bug with the jdbc drivers where if you try to insert more than 7 field values, you get an ArrayIndexOutOfBoundsException (here is a discussion about it: ). This link says it should work with the ojdbc14.jar driver, but I have the same problem when I use that driver. My code does work if I only insert 6 or 7 fields. If I attempt to insert all 11 that I need, I run into the error. So, now I'm working on finding an alternate way to do this. Has anyone run into this before?

The only thing I can think of to work around this is to insert my partial record, then immediately follow that up with an update to that record with the rest of the fields I need inserted. Is this the right way to handle this? I'm pretty new to java so any suggestions are welcome.

This is the code I'm using:
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33119

Another workaround I've used is another way of dealing with two steps.
1) Write a query to get the next sequence value and return it to your application.
2) Insert a row with all the fields you want including the sequence number.

The advantage to this approach is that you don't have to change any db constraints.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
K DeLucia
Ranch Hand

Joined: Apr 11, 2008
Posts: 68
Thanks! Makes sense. I'll give it a try!
I agree. Here's the link:
subject: JDBC Driver, Oracle sequence number problems
It's not a secret anymore!