Hi All,
Need your help please.
I am doing data comparison between the source and the target table. Below is part of my query where i do 'minus' between the source and the target and storing it to 'temp' variable and if the rownum=1 it will say rows returned and print failed if not pass.(I used Toad to run)
We are accessing the source table through DB link(@dw_et). I have access to the db link and i can just query the table, but, when i try to store the values of the inner query in temp variable it shows 'ORA-16000: database open for read-only access' .I mean i can only read data of db link tables but not able to store values in temp and do check.
Can some one please re-frame below query in a way to compare data between source and target and print 'Fail' if there is difference and 'Pass' if there is no data difference.
declare temp number;
begin
select 1 into temp from(select col_A,col_B,col_C from source@dw_etl minus select col_A,col_B,col_C from target_table) where rownum=1;
dbms_output.put_line('SOURCE MINUS TARGET FAILED');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('SOURCE MINUS TARGET PASSED');
end;
I sincerely request to help me with this to find an alternate solution. I am stuck and need your help.
Let me know please if any further info needed.
Warm Regards
Bhaskar.S