Set the Start with value to the Oracle sequence dynamically

Question

I am trying to create a script release that can be deployed to multiple databases, but where the data can be merged together later. The obvious way to handle this is to set the serial numbers for production data high enough in subsequent deployments to prevent collisions.

The problem is to start issuing a script that takes an environment number and sets the Start from sequences accordingly. Ideally, I would 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 does not work because you cannot evaluate the numeric expression in DDL.

Another option is to create sequences using dynamic SQL through 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 would prefer to avoid this solution, as I usually try to avoid issuing DDL in PL / SQL.

Finally, the third option I came up with is simply to accept the Start With value as a substitution variable instead of the environment number.

Can anyone think of how to do this?

Answer 1

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

Answer 2

If you have a fairly limited number of databases, you can run sequences with different values, and then determine the increment so that the sequence values ​​do not collide. This will eliminate the expression in the initial 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 fixes the problem of overlapping sequences if growth values ​​grow to the next database range)

Answer 3

One trick I used was to create a 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 the already valued &EnvironNum (if, for example, the user enters "275"):

 CREATE SEQUENCE seq1 START WITH 275*100000;