SEO

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.

On Dec 13, 6:07 pm, "BookerT" <c...@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.

were the insert triggers in place before the rows of data were imported
from the dump file?

-bdbafh

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.

BookerT wrote:
> I am a novice user, but expected to do "Advanced things"

Others have given you good advice.

With respect to your "Subject", above, altering DUAL is one way
to be quickly escorted to the door with a pink slip of paper
explaining how to find the unemployment office.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

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....htm#sthref196
http://download-east.oracle.com/docs...ments_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...@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.

I think most unemployment offices are now online. Sorry! Couldn't
resist.

On Dec 13, 7:01 pm, DA Morgan <damor...@psoug.org> wrote:
> BookerT wrote:
> > I am a novice user, but expected to do "Advanced things"Others have given you good advice.
>
> With respect to your "Subject", above, altering DUAL is one way
> to be quickly escorted to the door with a pink slip of paper
> explaining how to find the unemployment office.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Sorry for multiposting, but just thought of something else.

Notice: don't do any of this unless you have a backup.

If you can logon as sysdba and can also shutdown the db, you could try
the following:
(modify the schema to work on your system)
================================================== =========

select 'alter sequence your_schema_here.Inquiries_seq cache ' ||
((27349 - your_schema_here.Inquiries_seq.nextval)-2) ||';' "run sql
below: "from dual;

-- copy/past above statement
-- (my example generayour_schema_here this: alter sequence
Inquiries_seq cache 90

shutdown abort;
startup;

select your_schema_here.inquiries_seq.nextval from dual;

shutdown abort;
startup;

select your_schema_here.inquiries_seq.nextval from dual;

select
dbms_metadata.get_ddl('SEQUENCE','INQUIRIES_SEQ',' your_schema_here')
from dual;

alter sequence your_schema_here.inquiries_seq nocache;

select
dbms_metadata.get_ddl('SEQUENCE','INQUIRIES_SEQ',' your_schema_here')
from dual;









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.

EscVector wrote:
> Sorry for multiposting, but just thought of something else.
>
> Notice: don't do any of this unless you have a backup.
>
> If you can logon as sysdba and can also shutdown the db, you could try
> the following:
> (modify the schema to work on your system)
> ================================================== =========
>
> select 'alter sequence your_schema_here.Inquiries_seq cache ' ||
> ((27349 - your_schema_here.Inquiries_seq.nextval)-2) ||';' "run sql
> below: "from dual;
>
> -- copy/past above statement
> -- (my example generayour_schema_here this: alter sequence
> Inquiries_seq cache 90
>
> shutdown abort;
> startup;
>
> select your_schema_here.inquiries_seq.nextval from dual;
>
> shutdown abort;
> startup;
>
> select your_schema_here.inquiries_seq.nextval from dual;
>
> select
> dbms_metadata.get_ddl('SEQUENCE','INQUIRIES_SEQ',' your_schema_here')
> from dual;
>
> alter sequence your_schema_here.inquiries_seq nocache;
>
> select
> dbms_metadata.get_ddl('SEQUENCE','INQUIRIES_SEQ',' your_schema_here')
> from dual;
>
>
>
>
>
>
>
>
>
> 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.

1) Please do not top-post;
2) what you propose is extremely dangerous. Sequences can be rolled
forward way easier without the need for SYSDBA privileges or shutting
the db down in unsafe ways:

REM You need to run this in SQL*Plus
acc desired_val prompt "Desired NEXTVAL for your sequence: "
set trimspool on termout off heading off feedback off echo off verify
off
col currval new_value curr_val noprint
select Inquiries_Seq.Nextval currval from dual;
select 'ALTER SEQUENCE Inquiries_Seq INCREMENT BY '||
to_char(&desired_val-1-&curr_val)||' NOCACHE;' from dual;

spool temp.sql
/
spool off
@@temp.sql

REM Remove temporary file, choose one of the two versions below
REM If you're on Unix...
host rm temp.sql
REM If you're on Windows...
host del temp.sql

SELECT Inquiries_Seq.nextval from dual;
ALTER SEQUENCE Inquiries_Seq INCREMENT BY 1 CACHE 20;

REM Verify the outcome
set termout on heading on feedback on
SELECT Inquiries_Seq.nextval from dual;

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

On Dec 14, 1:13 pm, "Vladimir M. Zakharychev"
<vladimir.zakharyc...@gmail.com> wrote:
EscVector wrote:
> > Sorry for multiposting, but just thought of something else.
>
> > Notice: don't do any of this unless you have a backup.
>
> > If you can logon as sysdba and can also shutdown the db, you could try
> > the following:
> > (modify the schema to work on your system)
> > ================================================== =========
>
> > select 'alter sequence your_schema_here.Inquiries_seq cache ' ||
> > ((27349 - your_schema_here.Inquiries_seq.nextval)-2) ||';' "run sql
> > below: "from dual;
>
> > -- copy/past above statement
> > -- (my example generayour_schema_here this: alter sequence
> > Inquiries_seq cache 90
>
> > shutdown abort;
> > startup;
>
> > select your_schema_here.inquiries_seq.nextval from dual;
>
> > shutdown abort;
> > startup;
>
> > select your_schema_here.inquiries_seq.nextval from dual;
>
> > select
> > dbms_metadata.get_ddl('SEQUENCE','INQUIRIES_SEQ',' your_schema_here')
> > from dual;
>
> > alter sequence your_schema_here.inquiries_seq nocache;
>
> > select
> > dbms_metadata.get_ddl('SEQUENCE','INQUIRIES_SEQ',' your_schema_here')
> > from dual;
>
> > 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.1) Please do not top-post;
> 2) what you propose is extremely dangerous. Sequences can be rolled
> forward way easier without the need for SYSDBA privileges or shutting
> the db down in unsafe ways:
>
> REM You need to run this in SQL*Plus
> acc desired_val prompt "Desired NEXTVAL for your sequence: "
> set trimspool on termout off heading off feedback off echo off verify
> off
> col currval new_value curr_val noprint
> select Inquiries_Seq.Nextval currval from dual;
> select 'ALTER SEQUENCE Inquiries_Seq INCREMENT BY '||
> to_char(&desired_val-1-&curr_val)||' NOCACHE;' from dual;
>
> spool temp.sql
> /
> spool off
> @@temp.sql
>
> REM Remove temporary file, choose one of the two versions below
> REM If you're on Unix...
> host rm temp.sql
> REM If you're on Windows...
> host del temp.sql
>
> SELECT Inquiries_Seq.nextval from dual;
> ALTER SEQUENCE Inquiries_Seq INCREMENT BY 1 CACHE 20;
>
> REM Verify the outcome
> set termout on heading on feedback on
> SELECT Inquiries_Seq.nextval from dual;
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com

"Sequences can be rolled
forward way easier without the need for SYSDBA privileges or shutting
the db down in unsafe ways:"

Of course they can. There is some humor to this post, which is why I
put in this second option.

I was offering a creative alternative to the blindly simple.

Dangerous, as it may be, I did test it and it is an option, but I like
your option better.


Sorry for the top post.





....

EdStevens wrote:
> 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.

Alternative to dropping and recreating sequences (and upsetting any
dependancies based on them), you could just spin the sequence to the
right number:

DECLARE

seqvalue NUMBER;
tcount NUMBER;

BEGIN

SELECT MAX(primary_key_column)
INTO tcount
FROM mytable;

SELECT my_sequence.NEXTVAL
INTO seqvalue
FROM dual;

WHILE seqvalue < tcount LOOP

EXECUTE IMMEDIATE 'select my_sequence.nextval from dual' INTO
seqvalue;

END LOOP;

END;

If your supporting sequences are named in such a way that they can be
derived from their corresponding table names, you could even automate
doing all of them within a similar procedure.