Set "Start With" value for Oracle sequence dynamically

Question

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?

Answer 1

you can use the COLUMN XX NEW_VALUE YY syntax to perform calculation in SQL*Plus and store the result in a variable:

SQL> col sequence_num new_value seq
SQL> select &EnvironNum * 1000000 sequence_num from dual;
Enter value for environnum: 2
old   1: select &EnvironNum * 1000000 sequence_num from dual
new   1: select 2 * 1000000 sequence_num from dual

SEQUENCE_NUM
------------
     2000000

SQL> create sequence scott.seq1 start with &seq;
old   1: create sequence scott.seq1 start with &seq
new   1: create sequence scott.seq1 start with    2000000

Sequence created.