Dear All Experts,
I am new to PL SQL ...please check into this
undef prex
undef prefx
undef a
undef thisuser
undef b
undef REMOTESCHEMA
undef REMOTEPASSW
undef connstring
undef c
undef todaysdate
variable prefx varchar2(3)
declare
i number ;
j number ;
k number ;
cnt number;
begin
for i in ascii('A') .. ascii('Z') loop
for j in ascii('A') .. ascii('Z') loop
for k in ascii('A') .. ascii('Z') loop
select count(\*) into cnt from user\_objects where object\_name like
chr(i)||chr(j)||chr(k)||'%';
if cnt = 0 then
:prefx := chr(i)||chr(j)||chr(k);
return;
end if;
end loop;
end loop;
end loop;
end;
/
column a new_val prex
set verify off
set linesize 132
column table_name format a30
column column_name format a30
column constraint_name format a30
set pagesize 300
set feedback off
select :prefx a from dual;
column b new_val thisuser
select user b from dual;
column c new_val todaysdate
select to_char(sysdate,'DD-MON-YYYY HH24:MI') c from dual;
accept REMOTESCHEMA char prompt 'Enter remote username:'
accept REMOTEPASSW char prompt 'Enter remote password:' hide
accept connstring char prompt 'Enter remote connectstring:'
spool D:\mca_ebiz\recreate\dbdiff.txt
PROMPT
PROMPT
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT SCHEMA DEFINITION DIFFERENCES &todaysdate
PROMPT
PROMPT this schema: &thisuser
PROMPT remote schema: &remoteschema.@&connstring
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT
PROMPT
create database link &prex.lnk connect to &REMOTESCHEMA identified
by &REMOTEPASSW using '&CONNSTRING';
PROMPT
PROMPT
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT RECREATE TABLE FROM COMMON TABLES
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create table &prex.common_tables as
select table_name from user_TAbles@&prex.lnk
intersect
select table_name from user_tables;
select table_name from user_TAbles@&prex.lnk
minus
select table_name from &prex.common_tables;
create table &prex.diff_tables as
select table_name from user_TAbles@&prex.lnk
minus
select table_name from &prex.common_tables;
PROMPT
PROMPT
create table fks_ref_table(name VARCHAR2(32767));
create table fks_on_table(name VARCHAR2(32767));
create table pks_on_table(name VARCHAR2(32767));
DECLARE
v_sql VARCHAR2(32767);
begin
for cur in (select UPPER(table\_name) table\_name from &prex.common\_tables) loop
BEGIN
-- create ebiz\_temp\_tab and data with clob column using ctas from old table and to\_lob:
v\_sql := 'CREATE TABLE ebiz\_temp\_tab AS SELECT ';
FOR r IN
(SELECT DECODE
(data\_type,
'LONG', 'TO\_LOB (' || column\_name || ') ' || column\_name,
column\_name) || ','
AS column\_name
FROM all\_tab\_columns
WHERE UPPER (table\_name) = UPPER (cur.table\_name)
AND OWNER = Upper('&thisuser')
ORDER BY column\_id)
LOOP
v\_sql := v\_sql || r.column\_name;
END LOOP;
v\_sql := RTRIM (v\_sql, ',') || ' FROM ' ||cur.table\_name;
--DBMS\_OUTPUT.Put\_Line(v\_sql);
EXECUTE IMMEDIATE v\_sql;
-- create empty new table with long column using syntax from dbms\_metadata.get\_ddl:
SELECT REPLACE ( dbms\_metadata.get\_ddl('TABLE',UPPER (cur.table\_name),Upper('&remoteschema')),Upper('&remoteschema'),Upper('&thisuser')) INTO v\_sql
FROM DUAL;
--DBMS\_OUTPUT.Put\_Line(v\_sql);
-- EXECUTE IMMEDIATE 'DROP TABLE ' || cur.table\_name;
-- -------------------------------------------------------------------------
-- Copy Ref.Constraints fks\_ref\_table to specified table from Remote user
-- -------------------------------------------------------------------------
DECLARE
v\_ref\_cons\_sql VARCHAR2(32767);
CURSOR cu\_fks IS
SELECT \*
FROM all\_constraints a
WHERE a.owner = Upper('&remoteschema')
AND a.constraint\_type = 'R'
AND a.r\_constraint\_name IN (SELECT a1.constraint\_name
FROM all\_constraints a1
WHERE a1.table\_name = UPPER (cur.table\_name)
AND a1.owner = Upper('&remoteschema'));
BEGIN
FOR cur\_rec IN cu\_fks LOOP
SELECT to\_char(REPLACE (DBMS\_METADATA.get\_ddl ('REF\_CONSTRAINT', cur\_rec.constraint\_name, Upper('&remoteschema')),Upper('&remoteschema'),Upper('&thisuser')))
into v\_ref\_cons\_sql FROM dual;
DBMS\_OUTPUT.Put\_Line(v\_ref\_cons\_sql);
INSERT INTO fks\_ref\_table VALUES(v\_ref\_cons\_sql);
END LOOP;
END;
-- -------------------------------------------------------------------------
-- Dropping Ref.Constraints fks\_ref\_table from thisuser
-- -------------------------------------------------------------------------
DECLARE
CURSOR cu\_fks IS
SELECT \*
FROM all\_constraints a
WHERE a.owner = Upper('&thisuser')
AND a.constraint\_type = 'R'
AND a.r\_constraint\_name IN (SELECT a1.constraint\_name
FROM all\_constraints a1
WHERE a1.table\_name =UPPER (cur.table\_name)
AND a1.owner = Upper('&thisuser'));
BEGIN
FOR cur\_rec IN cu\_fks LOOP
DBMS\_Output.Put\_Line('ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP CONSTRAINT ' || Lower(cur\_rec.constraint\_name) || ';');
execute immediate 'ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP CONSTRAINT ' || Lower(cur\_rec.constraint\_name);
END LOOP;
END;
-- -----------------
-- Copy Ref.Constraints fks\_ref\_on\_table to specified table from Remote user
-- -----------------
DECLARE
v\_cons\_fk\_sql VARCHAR2(32767);
CURSOR cu\_fks IS
SELECT \*
FROM all\_constraints a
WHERE a.constraint\_type = 'R'
AND a.table\_name = Decode(UPPER (cur.table\_name),'ALL',a.table\_name,UPPER (cur.table\_name))
AND a.owner = Upper('&remoteschema');
BEGIN
FOR cur\_rec IN cu\_fks LOOP
SELECT to\_char(REPLACE (DBMS\_METADATA.get\_ddl ('REF\_CONSTRAINT', cur\_rec.constraint\_name, Upper('&remoteschema')),Upper('&remoteschema'),Upper('&thisuser')))
into v\_cons\_fk\_sql FROM dual;
DBMS\_OUTPUT.Put\_Line(v\_cons\_fk\_sql);
INSERT INTO fks\_on\_table VALUES(v\_ref\_cons\_sql);
END LOOP;
END;
-- -------------------------------------------------------------------------
-- Dropping Ref.Constraints fks\_ref\_table from thisuser
-- -------------------------------------------------------------------------
DECLARE
v\_cons\_fk\_sql VARCHAR2(32767);
CURSOR cu\_fks IS
SELECT \*
FROM all\_constraints a
WHERE a.constraint\_type = 'R'
AND a.table\_name = Decode(UPPER (cur.table\_name),'ALL',a.table\_name,UPPER (cur.table\_name))
AND a.owner = Upper('&thisuser');
BEGIN
FOR cur\_rec IN cu\_fks LOOP
DBMS\_Output.Put\_Line('ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP CONSTRAINT ' || Lower(cur\_rec.constraint\_name) || ';');
execute immediate 'ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP CONSTRAINT ' || Lower(cur\_rec.constraint\_name);
END LOOP;
END;
-- -----------------------------------------------------------------------------
-- Copy PK constraints pks\_on\_table from Remote user
-- -----------------------------------------------------------------------------
DECLARE
v\_cons\_pk\_sql VARCHAR2(32767);
CURSOR cu\_cons IS
SELECT \*
FROM all\_constraints a
WHERE a.table\_name = Decode(Upper(cur.table\_name),'ALL',a.table\_name,Upper(cur.table\_name))
AND a.owner = Upper('&remoteschema')
AND a.constraint\_type IN ('P','U');
-- ----------------------------------------------------------------------------------------
FUNCTION Con\_Columns(p\_tab IN VARCHAR2,
p\_con IN VARCHAR2)
RETURN VARCHAR2 IS
-- ----------------------------------------------------------------------------------------
CURSOR cu\_col\_cursor IS
SELECT a.column\_name
FROM all\_cons\_columns a
WHERE a.table\_name = p\_tab
AND a.constraint\_name = p\_con
AND a.owner = Upper('&remoteschema')
ORDER BY a.position;
l\_result VARCHAR2(1000);
BEGIN
FOR cur\_rec IN cu\_col\_cursor LOOP
IF cu\_col\_cursor%ROWCOUNT = 1 THEN
l\_result := cur\_rec.column\_name;
ELSE
l\_result := l\_result || ',' || cur\_rec.column\_name;
END IF;
END LOOP;
RETURN Lower(l\_result);
END;
-- ----------------------------------------------------------------------------------------
BEGIN
FOR cur\_rec IN cu\_cons LOOP
IF cur\_rec.constraint\_type = 'P' THEN
--DBMS\_Output.Put\_Line('ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP PRIMARY KEY;');
SELECT REGEXP\_SUBSTR(to\_char(REPLACE (DBMS\_METADATA.get\_ddl ('CONSTRAINT', cur\_rec.constraint\_name, Upper('&remoteschema')),Upper('&remoteschema'),Upper('&thisuser'))), '\[^/)\]+',1)||')' into v\_cons\_pk\_sql FROM dual;
--DBMS\_Output.Put\_Line(v\_cons\_pk\_sql);
INSERT INTO pks\_on\_table VALUES(v\_cons\_pk\_sql);
ELSIF cur\_rec.constraint\_type = 'R' THEN
-- DBMS\_Output.Put\_Line('ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP CONSTRAINT ' || Lower(cur\_rec.constraint\_name) || ';');
SELECT REGEXP\_SUBSTR(to\_char(REPLACE (DBMS\_METADATA.get\_ddl ('CONSTRAINT', cur\_rec.constraint\_name, Upper('&remoteschema')),Upper('&remoteschema'),Upper('&thisuser'))), '\[^/)\]+',1)||')' into v\_cons\_pk\_sql FROM dual;
INSERT INTO pks\_on\_table VALUES(v\_cons\_pk\_sql);
ELSIF cur\_rec.constraint\_type = 'U' THEN
--DBMS\_Output.Put\_Line('ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP UNIQUE (' || Con\_Columns(cur\_rec.table\_name, cur\_rec.constraint\_name) || ');');
SELECT REGEXP\_SUBSTR(to\_char(REPLACE (DBMS\_METADATA.get\_ddl ('CONSTRAINT', cur\_rec.constraint\_name, Upper('&remoteschema')),Upper('&remoteschema'),Upper('&thisuser'))), '\[^/)\]+',1)||')' into v\_cons\_pk\_sql FROM dual;
INSERT INTO pks\_on\_table VALUES(v\_cons\_pk\_sql);
END IF;
END LOOP;
END;
-------------------------------------------------------------------------------------------------
---- Dropping PK. Constraints pks\_on\_table from this user
-------------------------------------------------------------------------------------------------
DECLARE
v\_cons\_pk\_sql VARCHAR2(32767);
CURSOR cu\_cons IS
SELECT \*
FROM all\_constraints a
WHERE a.table\_name = Decode(Upper(cur.table\_name),'ALL',a.table\_name,Upper(cur.table\_name))
AND a.owner = Upper('&thisuser')
AND a.constraint\_type IN ('P','U');
-- ----------------------------------------------------------------------------------------
FUNCTION Con\_Columns(p\_tab IN VARCHAR2,
p\_con IN VARCHAR2)
RETURN VARCHAR2 IS
-- ----------------------------------------------------------------------------------------
CURSOR cu\_col\_cursor IS
SELECT a.column\_name
FROM all\_cons\_columns a
WHERE a.table\_name = p\_tab
AND a.constraint\_name = p\_con
AND a.owner = Upper('&thisuser')
ORDER BY a.position;
l\_result VARCHAR2(1000);
BEGIN
FOR cur\_rec IN cu\_col\_cursor LOOP
IF cu\_col\_cursor%ROWCOUNT = 1 THEN
l\_result := cur\_rec.column\_name;
ELSE
l\_result := l\_result || ',' || cur\_rec.column\_name;
END IF;
END LOOP;
RETURN Lower(l\_result);
END;
-- ----------------------------------------------------------------------------------------
BEGIN
FOR cur\_rec IN cu\_cons LOOP
IF cur\_rec.constraint\_type = 'P' THEN
DBMS\_Output.Put\_Line('ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP PRIMARY KEY;');
execute immediate 'ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP PRIMARY KEY;' || Lower(cur\_rec.constraint\_name);
ELSIF cur\_rec.constraint\_type = 'R' THEN
--DBMS\_Output.Put\_Line('ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP CONSTRAINT ' || Lower(cur\_rec.constraint\_name) || ';');
execute immediate 'ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP CONSTRAINT ' || Lower(cur\_rec.constraint\_name);
ELSIF cur\_rec.constraint\_type = 'U' THEN
-- DBMS\_Output.Put\_Line('ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP UNIQUE (' || Con\_Columns(cur\_rec.table\_name, cur\_rec.constraint\_name) || ');');
execute immediate 'ALTER TABLE ' || Lower(cur\_rec.table\_name) || ' DROP CONSTRAINT ' || Lower(cur\_rec.constraint\_name);
END IF;
END LOOP;
END;
-- ----------------------------------------------------------------------------------------------
-- Drop indexes on table
-- -----------------------------------------------------------------------------------------------
--@Drop\_Indexes &&1 &&2
DECLARE
CURSOR cu\_idx IS
SELECT \*
FROM all\_indexes a
WHERE a.table\_name = Decode(Upper(cur.table\_name),'ALL',a.table\_name,Upper(cur.table\_name))
AND a.owner = Upper('&thisuser');
BEGIN
FOR cur\_rec IN cu\_idx LOOP
execute immediate 'DROP INDEX ' || Lower(cur\_rec.index\_name) ;
END LOOP;
END;
--------------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE v\_sql;
-- insert data from temp\_tab with clob column to new table with long column:
DECLARE
CURSOR cu\_columns IS
SELECT Lower(column\_name) column\_name
FROM all\_tab\_columns atc
WHERE atc.table\_name = Upper('ebiz\_temp\_tab') and
atc.owner = Upper('&thisuser');
l\_sql varchar2(32765) ;
BEGIN
FOR cur\_rec IN cu\_columns LOOP
IF cu\_columns%ROWCOUNT !=1 THEN
l\_sql :=l\_sql|| ',';
END IF;
l\_sql := l\_sql || cur\_rec.column\_name;
END LOOP;
l\_sql := 'INSERT INTO '|| Lower(cur.table\_name) ||'('||l\_sql|| ') SELECT '||l\_sql ||' FROM ' || Lower('ebiz\_temp\_tab') ;
DBMS\_Output.Put\_Line( l\_sql);
execute immediate l\_sql;
end;
-- drop ebiz\_temp\_tab:
EXECUTE IMMEDIATE 'DROP TABLE ebiz\_temp\_tab';
end;
end loop;
end;
/
--drop database link &prex.lnk;
--drop table &prex.comcod;
--drop table &prex.diff_cols1;
--drop table &prex.diff_cols2;
--drop table &prex.common_tables;
--drop table &prex.common_indexes;
--drop table &prex.common_views;
--drop table &prex.comcons;
spool off
set verify on
set feedback on
undef prex
undef prefx
undef a
undef thisuser
undef b
undef REMOTESCHEMA
undef REMOTEPASSW
undef connstring
undef c
undef todaysdate
Regards
PKR