Oracle DB sequences no longer working in 6.1...

Questions and answers regarding general SQL and backend databases

Oracle DB sequences no longer working in 6.1...

Postby john.allen » Tue Jan 15, 2013 4:33 am

Over the last few weeks I have been upgrading a number of Servoy solutions from either 5.2 or 6.0 to 6.1. At the same time we have been migrating our Oracle databases from one server to another more secure server. For inserts on almost every table I use Oracle sequences rather than Servoy sequences. Before the upgrades all of these Oracle sequences worked fine. Now I am finding that on some tables in two different solutions the inserts are failing because of ORA-00001: unique constraint, i.e the sequences Servoy is using are out of whack with what they should be.
On these tables Servoy has the tables defined as using the db_seq and it is correctly named. But the sequence number Servoy is inserting has no bearing on what Oracle says the sequence number should be. Sometimes it appears that Servoy has decided to instead use the servoy_seq. When it does that at least the inserts don't fail. Other times though I can't even figure out where Servoy is getting the sequence from. In those cases the inserts fail because Servoy uses a number that already exists in that table. If I switch the table to servoy_seq then it works but if I try switching the sequence back to the Oracle sequence it still fails, bringing up numbers in the same order it had before. This is a big problem. Is there a way to FORCE Servoy to use that Oracle sequence properly again? In the .dbi file for the table everything looks fine too with it naming the db sequence correctly...
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Re: Oracle DB sequences no longer working in 6.1...

Postby john.allen » Tue Jan 15, 2013 9:25 pm

I've been experimenting some more with this and Servoy 6.1.3 seems to have real problems with Oracle sequences. These are things I've tried:

- Switch to Servoy sequence everything works
- Switch back to db sequence and give it a new name within Servoy and Servoy actually goes ahead and creates the sequence in Oracle. I don't ever remember it doing this before but that might be because I've always created the sequence directly in Oracle first. But in any case the sequence that Servoy creates within Oracle is useless because it starts it at 1.
- In Oracle change the sequence to start at the correct number, say 10253. Then create a new record in Servoy for that table. Record is created with ID 10253. Go in Oracle and check the sequence which obviously should give the new start as 10254. However it now gives the new start as 10273, adding 20 to the earlier start number not just 1. Create a new record in Servoy and the ID is 10255. Check the sequence in Oracle and it remains at 10273. So the first time Servoy runs it MIGHT use the Oracle sequence but most likely it is using the Servoy sequence as both would give the same ID at that point. After that first record though Servoy is certainly NOT using the Oracle sequence as the numbers being generated have no relationship to what Oracle says is the sequence. It appears that Servoy is simply using the Servoy sequence at this point. I've now done this numerous times and it always acts the same.

Originally when I had this problem on this one table and sequence after the upgrade to 6.1.3 the ID being generated with 'db sequence' selected was just a few integers behind what it should have been. So I just tried creating a few records (obviously getting errors) to increment that number to where the sequence should start. Once I got to that point everything worked EXCEPT that it was still obviously not using the Oracle sequence as that was not being incremented after creating a new record. That stays permanently where it is. Is no one else having this problem? This has now happened on two different solutions pointing a two entirely different Oracle schemas (Oracle 11). Is it possible that this is a JDBC driver issue?

Any help greatly appreciated
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 7 guests

cron