Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2, with additional filtration and joining with another table called LKUP.CTL_RWA_VERSION Below is the syntax that I have that is running in DEV.
begin
for r in (select DISTINCT TABLE_NAME from all_tab_columns where owner = 'DDSHIST' and COLUMN_NAME = 'SNAPSHOT_DT')
loop
begin
execute immediate 'INSERT INTO ||r.table_name|| @DBLINKSIT2
select a.*
from DDSHIST.||r.table_name|| a
INNER JOIN LKUP.CTL_RWA_VERSION b ON a.SNAPSHOT_DT = b.SNAPSHOT_DT and a.DDS_VERSION = b.DDS_VERSION
WHERE b.GOLDEN_COPY = 'N'';
exception when others then null;
end;
end loop;
end;
I put COLUMN_NAME = 'SNAPSHOT_DT' because some of the tables do not contain this column. So the joining condition is both SNAPSHOT_DT are the same, and DDS_VERSION are the same, WHERE golden copy in LKUP table = 'Y'. then loop the script, and insert into @DBLINKSIT2.
But I can't get the script to run and I don't know where I am getting this wrong.
Any help would be appreciated. Thank you.
