Updating oracle sequence Grandma s sex date

This question is select seq.nextval from dual; NEXTVAL---------- 1So, by finding out the current value of the sequence and altering the increment by to be negative that number and selecting the sequence once -- we can reset it.

Just beware that if others are using the sequence during this time - they (or you) may get ORA-08004: sequence SEQ.

There are 2 syntaxes for an update query in Oracle depending on whether you are performing a traditional update or updating one table with data from another table.

You can also perform more complicated updates in Oracle.

The Ask TOM team will be busy at Open World this month.

If you are at the conference, drop into the Groundbreaker area and say Hello.

If user cancels then I have a gap in my orders saved. You could of course do this in two alters as follows (simple math going on here): [email protected] There are three things I am sure of in life: o Death o Taxes o Gaps in my sequences / Enter value for inc: 0 old 1: alter sequence s_cust_mgmt_group_cmg_id increment by -&inc minvalue new 1: alter sequence s_cust_mgmt_group_cmg_id increment by -0 minvalue alter sequence s_cust_mgmt_group_cmg_id increment by -0 minvalue * ERROR at line 1: ORA-01722: invalid number SQL alter sequence s_cust_mgmt_group_cmg_id increment by -&inc minvalue 0 2 / Enter value for inc: 0 old 1: alter sequence s_cust_mgmt_group_cmg_id increment by -&inc minvalue 0 new 1: alter sequence s_cust_mgmt_group_cmg_id increment by -0 minvalue 0 alter sequence s_cust_mgmt_group_cmg_id increment by -0 minvalue 0 * ERROR at line 1: ORA-04002: INCREMENT must be a non-zero integer SQL June 28, 2002 - pm UTC you missed a zeroold 1: alter sequence s_cust_mgmt_group_cmg_id increment by -&inc minvaluenew 1: alter sequence s_cust_mgmt_group_cmg_id increment by -0 minvaluealter sequence s_cust_mgmt_group_cmg_id increment by -0 minvalue *ERROR at line 1: ORA-01722: invalid numberafter then you tried to set the increment to 0, which just isn't allowed (as evidenced by the ora-4002 error)You need to read the example above again (slower). Thanks as always :) you'd have to goto prod, grab the sequence name and last_number from dba_sequences.

We get the next val, change the increment to be that (negative), select once to get it to zero and then reset the increment to 1. Just use the code I provided:column S new_val inc;select seq.nextval S from dual;alter sequence seq increment by -&inc minvalue 0;select seq.nextval S from dual;alter sequence seq increment by 1;select seq.nextval from dual;do that, as is, and it'll work as advertised. test dababase has been populated by importing one month old dump of prod. we want to see the next values of sequences of test database at least equal to prod database's sequence next values. Then you can use the technique above in test to reset to zero, change the increment to last_number and select nextval (hence making the sequence "be at" last_number, then change the increment to 1 again and it'll be done.

In Oracle, you can create an autonumber field by using sequences.An example of a stored procedure that resets a sequence value can be found in another Stack Overflow thread.It sounds like an anonymous PL/SQL block would be acceptable to you just not a stored procedure.A sequence is an object in Oracle that is used to generate a number sequence.This can be useful when you need to create a unique number to act as a primary key. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. If you omit the MAXVALUE option, your sequence will automatically default to: Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object.

Leave a Reply