Re: How to Alter DUAL table to change value

BookerT wrote:
> I am a novice user, but expected to do "Advanced things"
>
> I have inherited some asp/vb code that queries and modifies an oracle
> database. Some of the code in the database builds a Select Statement
> from the DUAL table to generate the primary key sequence number for a
> few tables (maybe just one, I have not determined the full scope yet).
> One of my tables that I keep trying to add a record to through the asp
> code keeps giving me a unique constraint error.
>
> This happened, I believe because we had to reimport the database from
> an earlier copy and thus numbers in the old tables reverted back to
> lower numbers.
>
> So for example, since new records have been added, my primary key id
> number for one table is as high as 27348.
>
> however when the code generates a number for the primary key id number,
> it has a number like 27257. I need to change the DUAL table entry, so
> that it starts at 27349 and that way when the code goes in and makes a
> connection to the database, it will pick up 27349 and then sequence
> from there.
>
> How do I do that?
>
> Currently in the code it goes:
> SELECT Inquiries_seq.NextVal AS NextID
> FROM DUAL
>
>
>
> Thanks for any opinions.

DUAL is a 'dummy' table that simply provides a target for the FROM clause. You keys are not coming from the DUAL table, they are coming from an object called a 'sequence'. In your case the name of the sequence is 'Inquiries_seq'. To reset the 'nextval' of the sequence 'Inquiries_seq', you will have to drop and recreate it.

The rest is left as an exercise for the student. Received on Wed Dec 13 2006 - 17:14:33 CST