Dynamic table comparision issue

From: Sudhir <nshanmugam_at_gmail.com>
Date: Mon, 22 Feb 2010 15:08:30 -0800 (PST)
Message-ID: <33565551-a8be-45b1-a755-55d440693a4f_at_m37g2000yqf.googlegroups.com>

I'm trying to write a stored procedure which will compare two tables dynamically and will display the difference. The input parameter are two table names and columnames(optional)

I get the column names from all_tab_cols table and dynamically form a select statement, when executed will give me the difference between those two tables (using unionall)

Since I dont know the table and column type before hand, how do I run the SQL statement and fetch the result?
Here is the script I have. sql_stmt is the variable that have the complete sql statement when executed will give the difference. Can anyone of you help me to run the sql statement and fetch the result to a table or DBMS_OUTPUT window?

CREATE OR REPLACE PROCEDURE cstouser.sp_comparetables (tablea IN varchar2,tableb IN varchar2,columnlist IN VARCHAR2 DEFAULT NULL

)

IS

sql_stmt VARCHAR2 (32767);

TYPE r_cursor IS REF CURSOR;

c_1 r_cursor;

i NUMBER := 0;

j NUMBER := 0;

t1columnlist VARCHAR2 (32767) := NULL;

t1 VARCHAR2 (32767) := NULL;

t2columnlist VARCHAR2 (32767) := NULL;

groupby VARCHAR2 (5000);

cname VARCHAR2 (20);

str1 VARCHAR2 (32767);

str2 VARCHAR2 (32767);

table1 VARCHAR2 (100);

table2 VARCHAR2 (100);

CURSOR c1 (table1 IN varchar2)

IS

SELECT column_name, data_type, data_length

FROM all_tab_cols

WHERE table_name = table1

ORDER BY column_name;

CURSOR c2 (table2 IN varchar2)

IS

SELECT column_name, data_type, data_length

FROM all_tab_cols

WHERE table_name = table2

ORDER BY column_name;

BEGIN table1 := UPPER (tablea);

table2 := UPPER (tableb);

IF columnlist IS NULL

THEN FOR c_c1 IN c1 (table1)

LOOP t1columnlist := t1columnlist || c_c1.column_name || ',';

i := i + 1;

END LOOP; FOR c_c2 IN c2 (table2)

LOOP t2columnlist := t2columnlist || c_c2.column_name || ',';

j := j + 1;

END LOOP;
-- To remove the comma in the end

t1columnlist := SUBSTR (t1columnlist, 1, LENGTH (t1columnlist) - 1);

t2columnlist := SUBSTR (t2columnlist, 1, LENGTH (t2columnlist) - 1);

• compare if both the tables have same number of columns

IF i = j

THEN groupby := t1columnlist;

ELSE DBMS_OUTPUT.put_line(' The column list doesnt match between these two tables. Script terminates');

END IF; ELSE t1columnlist := columnlist;

t2columnlist := columnlist;

groupby := columnlist;

END IF; sql_stmt :=

'SELECT '''
|| table1

|| ''' AS TableName, '

|| t1columnlist

|| ' FROM '

|| table1

|| ' UNION ALL SELECT '''

|| table2

|| ''' As TableName, '

|| t2columnlist

|| ' FROM '

|| table2;

sql_stmt :=

'SELECT MIN(TableName) as TableName, '

|| t2columnlist

|| ' FROM ('

|| sql_stmt

|| ') A GROUP BY '

|| groupby

|| ' HAVING COUNT(*) = 1';
DBMS_OUTPUT.put_line ('sql_stmt' || sql_stmt); Received on Mon Feb 22 2010 - 17:08:30 CST