Coco Beans
asked on
Updating Sequences with a select statement
I'm doing testing on a data warehouse. I have to keep resetting the sequences. I'm looking for a quicker way to do it
some like this......
DROP SEQUENCE SEQ_PAY_STG_DAY_CAL;
CREATE SEQUENCE SEQ_PAY_STG_DAY_CAL
START WITH (SELECT last_number
FROM all_sequences
WHERE sequence_name = 'SEQ_DIM_CALENDAR')'
MAXVALUE 99999999999999999999999999 99
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
I have about 20 to do.
Any ideas? Thanks
some like this......
DROP SEQUENCE SEQ_PAY_STG_DAY_CAL;
CREATE SEQUENCE SEQ_PAY_STG_DAY_CAL
START WITH (SELECT last_number
FROM all_sequences
WHERE sequence_name = 'SEQ_DIM_CALENDAR')'
MAXVALUE 99999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
I have about 20 to do.
Any ideas? Thanks
Use ALTER SEQUENCE, you don'd need and don't want to DROP the sequence (because, for example, that would lose explicit permissions granted to that sequence).
ASKER
but I still can't update it using a select?
You could generate the needed ALTER commands using a SELECT, but a SELECT does not change data or settings.
ASKER
I'm not sure what you mean. I still can't update the DML with a DDL
With an "alter sequence..." command it is still a multi-step process, but that is better than dropping the sequence as Scott Pletcher indicated, because then you lose the grants (if any) on the sequence.
Here are the steps you need to do for each sequence:
1. SELECT last_number FROM all_sequences WHERE sequence_name = '&seq_name';
2. alter sequence &seq_name increment by last_number * -1; -- This assumes you want them set back to 1.
3. select &seq_name.nextval from dual;
4. alter sequence &seq_name increment by 1;
I used a PL\SQL procedure to do this for multiple sequences in the past. I'll look for that source code and post it here later if I can find it.
Here are the steps you need to do for each sequence:
1. SELECT last_number FROM all_sequences WHERE sequence_name = '&seq_name';
2. alter sequence &seq_name increment by last_number * -1; -- This assumes you want them set back to 1.
3. select &seq_name.nextval from dual;
4. alter sequence &seq_name increment by 1;
I used a PL\SQL procedure to do this for multiple sequences in the past. I'll look for that source code and post it here later if I can find it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.