Load Knowledge Module Oracle to Oracle using database links
Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
ODI comes with an LKM to extract data from another Oracle database instance using db links via a view that is created at the source system.
The existing LKM Oracle to Oracle (DBLINK)
I see two problems with the way this KM is implemented.
1. It creates a database link on the fly. What if you want to run multiple interfaces in parallel that rely on this db link? You are fecked.
2. This is bad practice as far as I am concerned. What if the source DBA does not give you privileges to create views?
In summary this LKM is not usable.
I have created an LKM Oracle to Oracle using a db link that creates a view on the target rather than the source. It also does not generate the db link on the fly. A C$ view is used rather than a C$ table as this is similar to extracting from another Oracle schema and in theory no LKM should be required at all.
How to use the improved LKM Oracle to Oracle (DBLINK View Target)
You need to create a database link in the work schema of your target.
You then need to append the name of this db link to the Resource Name of your source data store(s). In the example below our db link is @PVPROD.
That is it. Now download the LKM Oracle to Oracle (DBLINK View Target) and use it.
If you don’t want to hard code the name of the database link into the Resource name in your datastore you can use the following technique.
Declare the variable in your Interface Diagram, e.g.
And in your Resource name print out this variable
For various reasons you can’t use ODI variables in the Resource name. One of them is that the getfrom() method in the ODI substitution API does not give you the full control that would be needed to set this up correctly. So you are stuck with the technique above.
Need ODI Training? Learn ODI from the experts!
I am proud to announce that Sonra are now offering ODI classes.
Value-priced. Customised. Onsite-Offsite. Online.
Five day standard training course.
Online.
Starts 24 February 2014.
Book before 15 January 2014. Pay $949.
More information on our ODI courses.
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
