Table of Contents
1. Overview
This document will be helpful to compares Prod and Dev schema and generates alter scripts for missing columns from dev schema tables that to be deployed in Prod instance.
2. Technologies and Tools Used
- Oracle Plsql code.
3. Use Case
To migrate Scripts from development instance to prod instance we used below method to get complete script.
4. Architecture
Compares Prod and Dev schema and generates alter scrips for missing columns from dev schema tables that to be deployed in Prod instance.
Step1 : Login as sysdba.
Step2: Run below code.
Code:
DECLARE
PROCEDURE alt_script_prc (
p_schema_1 IN VARCHAR2,
p_schema_2 IN VARCHAR2
) IS
CURSOR cus_tbl_c IS
( SELECT DISTINCT
s2.table_name s2_table_name,
s2.owner s2_owner
FROM
sys.all_tab_cols s1,
sys.all_tab_cols s2
WHERE
1 = 1
AND s1.table_name = s2.table_name
AND s1.owner = p_schema_1
AND s2.owner = p_schema_2
–AND s2.table_name = ‘tbl1’
);
CURSOR alt_script_c (
c_table_name IN VARCHAR2,
–c_s1_owner IN Varchar2,
c_s2_owner IN VARCHAR2
) IS
( SELECT
s2_table_name,
s2_col,
s2_datatype,
s2_length
FROM
(
SELECT
s1.table_name AS s1_table_name,
s2.table_name AS s2_table_name,
s1.column_name AS s1_col,
s2.column_name AS s2_col,
s2.data_type AS s2_datatype,
s2.data_length AS s2_length,
s2.owner AS s2_owner
FROM
(
SELECT
table_name,
column_name,
owner
FROM
sys.all_tab_cols
WHERE
1 = 1
AND owner = p_schema_1
) s1
FULL JOIN (
SELECT
table_name,
column_name,
data_type,
data_length,
owner
FROM
sys.all_tab_cols
WHERE
2 = 2
AND owner = p_schema_2
) s2 ON s2.table_name = s1.table_name
AND s1.column_name = s2.column_name
)
WHERE
2 = 2
AND s2_table_name = c_table_name
AND s2_owner = c_s2_owner
AND s1_col IS NULL
AND s2_col IS NOT NULL
);
l_col_loop NUMBER;
l_comma CHAR(1);
l_data_ty VARCHAR2(100);
BEGIN
FOR i_table IN cus_tbl_c LOOP
l_col_loop := 0;
FOR i_col IN alt_script_c(i_table.s2_table_name, i_table.s2_owner) LOOP
IF l_col_loop = 0 THEN
l_comma := NULL;
l_col_loop := 1;
dbms_output.put_line(‘Alter table ‘
|| i_table.s2_table_name
|| ‘ Add (‘);
ELSIF ( l_col_loop = 1 ) THEN
l_comma := ‘,’;
END IF;
If i_col.s2_datatype IN (‘DATE’,’BLOB’,’CLOB’) then
l_data_ty:= l_comma || i_col.s2_col
|| ‘ ‘
|| i_col.s2_datatype;
else
l_data_ty:= l_comma
|| i_col.s2_col
|| ‘ ‘
|| i_col.s2_datatype
|| ‘(‘
|| i_col.s2_length
|| ‘)’;
end if;
dbms_output.put_line(l_data_ty);
END LOOP;
IF l_col_loop = 1 THEN
dbms_output.put_line(‘)’);
dbms_output.put_line(‘/’);
END IF;
END LOOP;
dbms_output.put_line(‘exit’);
END;
BEGIN
alt_script_prc(
:schema1, ————-PROD
:schema2 ————-DEV
);
END;
5. Screen Shot
Output:
