Set "Start With" value for Oracle sequence dynamically
- by Allan
I'm trying to create a release script that can be deployed on multiple databases, but where the data can be merged back together at a later date. The obvious way to handle this is to set the sequence numbers for production data sufficiently high in subsequent deployments to prevent collisions.
The problem is in coming up with a release script that will accept the environment number and set the "Start With" value of the sequences appropriately. Ideally, I'd like to use something like this:
ACCEPT EnvironNum PROMPT 'Enter the Environment Number: '
--[more scripting]
CREATE SEQUENCE seq1 START WITH &EnvironNum*100000;
--[more scripting]
This doesn't work because you can't evaluate a numeric expression in DDL.
Another option is to create the sequences using dynamic SQL via PL/SQL.
ACCEPT EnvironNum PROMPT 'Enter the Environment Number: '
--[more scripting]
EXEC execute immediate 'CREATE SEQUENCE seq1 START WITH ' || &EnvironNum*100000;
--[more scripting]
However, I'd prefer to avoid this solution as I generally try to avoid issuing DDL in PL/SQL.
Finally, the third option I've come up with is simply to accept the Start With value as a substitution variable, instead of the environment number.
Does anyone have a better thought on how to go about this?
