Hi all,
I am coding in a context where my database contains only my code and my application tables : the data has to go from a "source" table (from a "source" database) into a "target" table (in a "target" database).
I don't know the structure of my "source" tables : I read them on the fly and I create the target tables using "create table as select" command embedded into a "dbms_utility.exec_ddl_statement@dblink statement. This part works.
But now, I have to use a merge instruction for a source dimension table : and I can't sort it out in that context.
In the merge instruction (I have to merge the source into the target table), I have to specify :
'WHEN MATCHED THEN UPDATE SET target.'||col1||'=source.'||col1||', target.'||col2||'=source.'||col2||', ...'
and also
'WHEN NOT MATCHED THEN INSERT (target.'||col1||', target.'||col2||'...'||' VALUES(source.'||col1||', source.'||col2||', ...'
I means that I must be able to read in the ALL_TAB_COLUMNS table of the source database, all columns of my source table. And this needs to be done in a FOR LOOP : less obvious than it may look like...
My code is :
SOURCE_QUERY_DATETIME:=sysdate;
STATEMENT1 :=
'merge into '||TARGET_TABLE_OWNER||TARGET_TABLE_NAME||TARGET_DBLINK_GRAFT||' TGT using'
||' (select SRC.*, '''||SOURCE_LOC_ID||''', '||SOURCE_QUERY_DATETIME
||' from '||SOURCE_TABLE_OWNER||SOURCE_TABLE_NAME||SOURCE_DBLINK_GRAFT||' SRC) REC_TO_MERGE'
||' on (REC_TO_MERGE.LOC_ID = TGT.LOC_ID and REC_TO_MERGE.SOURCEPK = TGT.SOURCEPK)'
||' when matched then update set TGT.MERGE_DATE='||SOURCE_QUERY_DATETIME
;
for SOURCE_COLNAME in (
select COLUMN_NAME
from ALL_TAB_COLUMNS@SOURCE_DBLINK
where TABLE_NAME = SOURCE_TABLE_NAME
)
loop
STATEMENT1 := STATEMENT1||', '||'TGT.'||SOURCE_COLNAME.COLUMN_NAME||'='||'REC_TO_MERGE.'||SOURCE_COLNAME.COLUMN_NAME ;
end loop;
STATEMENT1 := STATEMENT1||' when not matched then insert(TGT.LOC_ID, TGT.LOC_LBL, TGT.MERGE_DATE';
for SOURCE_COLNAME in ( -- code similar for the WHEN NOT MATCHED part of the statement
...
execute immediate STATEMENT1;
The problem is in the FOR statement : I am not allowed to use "from ALL_TAB_COLUMNS@SOURCE_DBLINK"
I have to use another execute immediate to concatenate SOURCE_DBLINK and to execute this loop. This execute immediate statement will thus contain that FOR LOOP,
but then, I am not able to get back STATEMENT1 from it !
Your help would be very appreciated !
Thx,
Olivier