Load Knowledge Module Oracle to Oracle using database links

Uli Bethke Oracle Data Integrator (ODI)

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.

db_link_odi

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.

odi_dblink0

And in your Resource name print out this variable

odi_dblink

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.

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.