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

If you have a reasonably limited number of databases you could start the sequences with a different values and then define an increment so that the sequence values do not collide. This would eliminate the expression in the start value.

So if you have 10 databases:

create sequence seq1 start with &startval increment by 10;

and startval is 1 for database 1, 2 for database 2, etc.

(This also eliminates the problem of sequences overlapping if the increment values grow into the next database's range)

Answer 2

One trick I've used is to create an sqlplus script from the main script and then execute it:

maybe something like

ACCEPT EnvironNum PROMPT 'Enter the Environment Number:  '
spool seq_script.sql
begin
    dbms_output.put_line('CREATE SEQUENCE seq1 START WITH '||&EnvironNum||'*100000;')
end;
spool off
@seq_script.sql

This should create a script file with &EnvironNum already evaluated (assuming user inputed '275', for example):

CREATE SEQUENCE seq1 START WITH 275*100000;

Answer 3

Hi Allan,

you can use the COLUMN XX NEW_VALUE YY syntax to perform calculation in SQL 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.