Link to home
Start Free TrialLog in
Avatar of Coco Beans
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 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;



I have about 20 to do.


Any ideas? Thanks
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

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).
Avatar of Coco Beans
Coco Beans

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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Coco Beans
Coco Beans

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial