comparing the same table from diffrent schemas

[beksalur]

hi all,
I am new in PL/SQL and need code assistance for the following issue.
i have two same database on different locations.I want to compare the same table from these database schemas.I plan to use dblink to connect to databases and use MINUS for differences with the following format.
select * from '||:v_schema1||'.'||:v_table_name||'@':v_remote_dblink_name1''
MINUS
select * from '||:v_schema2||'.'||:v_table_name||'@':v_remote_dblink_name2''

I want to do it with a procedure with the template of :
compare_tables(
v_schema1 in varchar2,
v_schema2 in varchar2,
v_remote_dblink_name1 in varchar2,
v_remote_dblink_name1 in varchar2,
v_table_name in varchar2)
Example usage:

compare_tables(UHVKK,
U1BHHM,
DHVKK1.HVKK.TSK,
D1BHHM1.HVKK.TSK,
KKKD_PERSONNEL)

Thanks in advance.

 

[beksalur]

I tried to write the following code but it fails.

Create or Replace
Procedure compare_tables (
v_schema_name1 IN varchar2,
v_schema_name2 IN varchar2,
v_dblink1_name IN varchar2,
v_dblink2_name IN varchar2,
v_table_name IN varchar2
) IS
CURSOR cursor_diff IS
SELECT * FROM '||v_schema_name1||'.'||v_table_name||'@'||v_dblink1_name
MINUS
SELECT * FROM '||v_schema_name2||'.'||v_table_name||'@'||v_dblink2_name;
cursor_rec cursor_diff%ROWTYPE;
BEGIN

OPEN cursor_diff ;
LOOP
FETCH cursor_diff INTO cursor_rec;
IF cursor_diff%FOUND THEN
dbms_output.put_line(cursor_rec(0));--print first column
dbms_output.put_line(cursor_rec(1));
dbms_output.put_line(cursor_rec(2));

END IF
END LOOP;
CLOSE cursor_diff ;
END compare_tables;

 

[haste]

The concatenation in the FROM clause has a problem. It results in an unclosed string:

SELECT * FROM '||v_schema_name1||'.'||v_table_name||'@'||v_dbLink1_name

I corrected as follows

SELECT * FROM v_schema_name1||'.'||v_table_name||'@'||v_dbLink1_name

However the solution I'd advance is to try using a variable to hold the table names instead of concatenanting the table name in the FROM clause
Something like

table1 := v_schema_name1||'.'||v_table_name||'@'||v_dbLink1_name

SELECT * FROM table1

Do the same for table to.

 

[haste]

The concatenation in the FROM clause has a problem. It results in an unclosed string:

SELECT * FROM '||v_schema_name1||'.'||v_table_name||'@'||v_dbLink1_name

I corrected as follows

SELECT * FROM v_schema_name1||'.'||v_table_name||'@'||v_dbLink1_name

However the solution I'd advance is to try using a variable to hold the table names instead of concatenanting the table name in the FROM clause
Something like

table1 := v_schema_name1||'.'||v_table_name||'@'||v_dbLink1_name

SELECT * FROM table1

Do the same for table 2.