Table comparison stored procedure very slow

I have written a stored procedure to compare data between two tables for all tables in the schema at one shot....

The procedure works fine but it takes too long, as it is doing comparison of every field in every row. Here's my procedure. Please let me know what modification I can to make this procedure faster.

CREATE OR REPLACE PROCEDURE schema_compare
AS
	CURSOR cur_tab IS SELECT table_name FROM dba_constraints 
	WHERE table_name LIKE 'S%'
	AND table_name NOT LIKE '%$'
	AND owner = 'schema1'
	AND constraint_type = 'P';

--     This cursor is for all columns without primary key column for a table

	CURSOR cur_tab_col(p_tabname1 IN VARCHAR2, p_owner in VARCHAR2) IS SELECT column_name FROM
	dba_tab_columns dtc WHERE 
	column_name NOT IN(SELECT dcc.column_name FROM dba_constraints dc, dba_cons_columns dcc
	WHERE dc.constraint_TYPE = 'P'
	AND dcc.constraint_name = dc.constraint_name
	AND dcc.table_name = dc.table_name
	AND dcc.owner = dc.owner
	AND dc.OWNER = 'schema1'
	AND dc.table_name like 'S%'
	AND dc.table_name not like '%$'
	AND dcc.table_name like 'S%'
	AND dcc.table_name not like '%$')
	AND dtc.table_name like 'S%'
	AND dtc.table_name not like '%$'
	AND dtc.owner = 'schema1'
	AND dtc.table_name = p_tabname1
	AND dtc.owner = p_owner
	ORDER BY table_name, column_id;

--     This cursor fetches only priamry key columns for a table.

	CURSOR cur_pri(p_tabname3 IN VARCHAR2,p_owner in VARCHAR2 ) IS SELECT dcc.column_name
	FROM dba_constraints dc, dba_cons_columns dcc
	WHERE dc.constraint_TYPE = 'P'
	AND dcc.table_name = dc.table_name
	AND dcc.constraint_name = dc.constraint_name
	AND dcc.owner = dc.owner
	AND dcc.owner = 'schema1'
	AND dcc.table_name = p_tabname3;

	ndummy		NUMBER := 0;
	vdummy		NUMBER := 0;
	v_WHERE		VARCHAR2(4000);
	v_select1	VARCHAR2(4000);
	v_select2	VARCHAR2(4000);
	l_query		VARCHAR2(4000);
	l_row		VARCHAR2(4000);	
	l_result1 	VARCHAR2(4000);
	l_result2	VARCHAR2(4000);
	TYPE rc 	IS REF CURSOR; 
	l_cursor   	rc; 

--  The idea is to built select statement for each row for one table in one schema and then compare the two statements
--  based on primary key.
BEGIN
	FOR rec_int IN cur_tab
	LOOP

		EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM schema1.'||rec_int.table_name INTO ndummy;
		EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM schema2.'||rec_int.table_name INTO vdummy;
   		DBMS_OUTPUT.PUT_LINE('Table Name: '||rec_int.table_name);
		IF ndummy <> vdummy THEN
			dbms_output.put_line('First schema has ' || ndummy ||' Rows Second schema has ' ||vdummy|| ' Rows');
		END IF;
		
		v_WHERE := '';
		
		FOR rec_pri IN cur_pri(rec_int.table_name, 'schema1')
		LOOP
			IF v_WHERE = '' OR v_where IS NULL THEN
				v_where := rec_pri.column_name;
			ELSE
				v_where := v_where || '||' || rec_pri.column_name;			
			END IF;
		END LOOP;
		
		v_select1 := '';
		v_select2 := '';

		l_query := ' SELECT ' || v_where || ' FROM schema1.' || rec_int.table_name;

		BEGIN 
			OPEN l_cursor FOR l_query; 
			LOOP 
				FETCH l_cursor INTO l_row; 
				EXIT WHEN l_cursor%NOTFOUND; 

				FOR rec_non_pri IN cur_tab_col(rec_int.table_name,'schema1')
				LOOP

					v_select1 := 'SELECT ' || CHR(39) || CHR(39) || '||' 										|| rec_non_pri.column_name || ' FROM schema1.'|| rec_int.table_name ||
					 ' WHERE '	|| v_where || ' = ' || CHR(39) || 										l_row || CHR(39);
					
					v_select2 := 'SELECT ' || CHR(39) || CHR(39) || '||' 										|| rec_non_pri.column_name || ' FROM schema2.'|| rec_int.table_name ||
					 ' WHERE '	|| v_where || ' = ' || CHR(39) || 										l_row || CHR(39);

					EXECUTE IMMEDIATE  v_select1 INTO l_result1;
					EXECUTE IMMEDIATE  v_select2 INTO l_result2;


					IF l_result1 <> l_result2 THEN
					   DBMS_OUTPUT.PUT_LINE('Columns value do not match '||l_result1||' -- '||l_result2); 
					END IF;
				END LOOP;

			END LOOP; 
			CLOSE l_cursor; 
		END; 

	END LOOP;	
END;
/

It seems to me you're doing an awful lot of work that is unnecessary. Why not just change your procedure to use an approach like this:

First, create a query where you end up with a select that looks something like this:

SELECT 'schema1.table', column_name
  FROM 
	(SELECT column_name  from schema1.table
	 MINUS
	 SELECT column_name FROM schema2.table ) t1
UNION
SELECT 'schema2.table', column_name
	(SELECT column_name  FROM schema2.table 
	 MINUS
	 SELECT column_name from schema1.table ) t2  ;

As you've already proven, your method works but is slow and time consuming. The key is to reduce the steps you have to take in order to get the result you want. This is one way to do that.

Hope that helps!

For More Information

• Dozens more answers to tough Oracle questions from Karen Morton are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.