Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to keep two database environment tables in sync by creating scripts using procedure

Albert ChaoDec 26 2021 — edited Dec 26 2021

I have one table in the DEV environment and the same table was in SIT environment too. But I have added a few columns in the DEV environment table that I need to sync in SIT environment also. So, I am wondering if this can be done using the stored procedure by creating scripts where first I will have to find the levels of the tables based on the parent-child relationship that I will deploy directly into the SIT environment using any deployment tool like GIT/ JENKINS and that will make the tables in sync.
---DEV Environment---

CREATE TABLE table_dev1 (
    e_id    NUMBER(10),
    e_name  VARCHAR2(10),
    e_location varchar2(10),
    constraint pk_table_dev primary key(e_id)
);


CREATE TABLE table_dev2 (
    e_id2    NUMBER(10),
    e_id NUMBER(10),
    e_name  VARCHAR2(10),
    e_location varchar2(10),
    constraint pk_table_dev primary key(e_id),
    constraint fk_table_dev2 foreign key(e_id) references table_dev1(e_id)
);

table_dev1 is the parent and table_dev2 is the child table so in this order only it should create
tables into higher environment

---SIT Environment---

CREATE TABLE table_dev1 (
  e_id  NUMBER(10),
  e_name VARCHAR2(10),
  constraint pk_table_dev primary key(e_id)
);

Table which is already present in SIT has e_location column missing that needs to be synced 
from DEV environment and also new table got created in DEV environment i.e table_dev2
that needs to created into the SIT environment or I can say in the script it should like
--check if table exist or not 
--If exist then check if all columns are present or not if not then sync
--If table doesn't exist then create one.

I have also asked the same question on : https://stackoverflow.com/questions/70484521/how-to-keep-two-database-environment-tables-in-sync-by-creating-scripts-using-pr

This post has been answered by Mike Kutz on Jan 6 2022
Jump to Answer
Comments
Post Details
Added on Dec 26 2021
12 comments
835 views