DBLink is the one of the most powerful attributes, if it is needed to load data from one Oracle database to other Oracle database especially when you are doing extraction. If you want to use DBLink, that you already implemented in Topology as “Instance / DBLink (Data Server)”, in a Knowledge Module in Oracle Data Integrator (ODI), there are not fancy options.In standart ODI KM, a view and a synonym are created in the source system, DBLink is created in the target system and view is selected through target system. But, what if your source system admins don’t give you the permission to create a view and a synonym (mostly they don’t)? And since you typed your DBLink name under Topology, why not to use it?
As I’ve googled how people implemented DBLink in ODI, I obtained the result of using tables in model. Adding DBLink name to the table, like using COMPANY@SRC_LINK instead of COMPANY as the table name in ODI model. But, what if your dba changes the name of the DBLink or your loading strategy may change? It should be flexible, simple and manageable.
So, I have worked a bit to how to insert DBLink name between table name and table alias when creating insert into target_table select * from source_table SQL statement. See below image.
After some effort, I found it impossible to replace DBLink Name instead of space character between table name and table alias. Asked this question to my Oracle friends. Finally, thanks to Salvatore Alastra, which solved my issue with a simple set of code and help me to save the date. It was below code which I have created this Knowledge Module on 16th March 2009.
Since the output of this Knowledge Module would be a simple “INSERT /*+ INSERT_HINT */ INTO target_table (column_list) select /*+ SELECT_HINT */ column_list from source_table@dblink_name table_alias“, everything is done in source side (LKM section), so it is not need to select any Knowledge Module on the staging or target side (STG or SRC section) or select a dummy Knowledge Module that does nothing.
This Knowledge Module also supports many tables from different DBLinks (you don’t need to unload all the tables to C$ tables), since it produces a simple insert into … select … statement. Supports filters, joins, group by and having statements just like all other Knowledge Modules in ODI.
Click version names to download LKM Oracle to Oracle (DBLink, v2) for ODI 10G or ODI 11G or alternatively download Knowledge Module exports for ODI 10G or ODI 11G and rename the files’ surname to *.xml.
Remember that, use these files at your own risk, backup your current environment and this Knowledge Modules are not supported by Oracle. 🙂