Skip to Main Content
  • Questions
  • Oracle DB - getting same sequence values across different Environments?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, R.

Asked: September 03, 2015 - 6:40 am UTC

Last updated: September 14, 2015 - 4:16 pm UTC

Version: 11 g

Viewed 1000+ times

You Asked

How to create sequences so that the value remains same across different environment? I have created a package with procedures and functions in it, which takes data from staging tables and inserts into production schema( it's a copy of production schema). The problem is the tables in the production have primary key as sequence and some other columns combination for each table. i am generating sequence values through a function that i wrote with some initial values that were available at that time. each table has a separate sqeuence created. Now i have to deploy same stuff in different environments(UAT,preprod etc) before its done in the production. I want the values to be generated as same that have been generated or will be generated across all environments. How can i achieve that, instead of giving intial values for a sequence, have the values dynamic.

and Connor said...

I'm not entirely sure I understand your requirement, but some things you can with sequences are:

a) drop/recreate to adjust the START WITH value

b) use an "alter" to adjust to increment to adjust the value without dropping and recreating.

eg to reset a sequence currently at "1234" back to 1, you can do:

alter sequence S increment by -1233
select S.nextval from dual;
alter sequence S increment by 1

In either case, if you run the same series of procedure calls etc in each environment, you could reasonably expect the generation of sequence numbers to be consistent (but thats not a guarantee).

But the main question is - if sequences are being used, then (by definition) you are using an *arbitrary* number as a key. Why you need to match them up across environments is somewhat of a mystery to me, because it suggests you're referring to their values explicitly in code...that's not such a great place to be

Hope this helps.
AskTom team.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for your answer. adding additional info.

R K, September 03, 2015 - 7:26 pm UTC

The tables are not referential on primary keys etc. The tables can be joined based on ID columns when querying data. Basically there are two main Tables forexample, Table 1 and Table 2. The Table 1 Primary Key = (surrogate_id,version_number). Table 2 primary key = surrogate_id,version_number). there is Table1_ID column in Table1. Table2_ID and Table2_ID column in Table2. Same columns are present in rest of the tables. So when quering data, we only select data based on joins Table1_ID and Table2_ID columns. Surrogate_id is not used for joins.
Chris Saxon
September 04, 2015 - 3:13 am UTC

Can you please elaborate further with some scripts to create the tables, populate them with data, and show us an example of the queries you want to run.

Thanks

Oracle has a solution for this in 12c

V Giridaran, September 04, 2015 - 8:01 pm UTC

The sys_guid function will ensure unique ID generation across environments but it requires RAW datatype.
Connor McDonald
September 05, 2015 - 12:19 am UTC

(I'm still not sure of the original poster's requirement) but if the need for a unique sequences across environments, then simply offsetting each environment by an appropriate range would be appropriate (and still allow integer data types).

Hope this helps.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library