Re: How to Alter DUAL table to change value

BookerT, I feel for you man. I had to chuckle when I saw that you wanted to modify DUAL. You are in the worst position possible: Not getting paid for the work, but having to do it anyway, with no database training. Or put another way, you have to "buy" the problem that you shouldn't own. I guess you have your reasons.... but you are sitting on the cliff edge.

If you have toad or other gui tool, extract the existing sequence ddl.

Edit the START WITH value and recreate the sequence.

Open the 9i or 10g sql ref for sequence create detail: http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14195/sqlqr01.htm#sthref196 http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_615a.htm

If you don't have gui tool you can get the ddl via sql/plus

set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL
  (
   'SEQUENCE',' INQUIRIES_SEQ','')
    FROM DUAL; If you are really cool, you could use .NET, ADO.net and ADOX to create the sequence in Oracle via powershell script, but that might be over kill... but it would be advanced.

On Dec 13, 6:07 pm, "BookerT" <c..._at_ascac.org> 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.
Received on Wed Dec 13 2006 - 22:40:17 CST