Using a DBLink Location for ETL
By Jean-Pierre Dijcks on Jul 30, 2008
One of the things that might be interesting to have a play with is the ability to use a predefined dblink as a substitute for the OWB db links generated when you create two locations pointing to two separate hosts.
There are roughly three ways of doing this in OWB:
1) Configure a mapping and set a dblink and schema name property on each mapping (note is not the recommended way)
2) Supply a database link name on the connector between the target module and the source module
3) Use a DBLink type location for the source
Since the third one is most complex one, but used in the EBS cases in house, I figure, let's start with that one.
First thing to do is create the DB link location itself (it will specify the database link to use and the schema etc.)
To explain this dialog a little bit more, the From Location is the target location in which the mapping will live, the database link is the name of the db link as found in the target schema. The dblink of course connects to your source schema as a normal db link.
Next you would associate the dblink location you just created with the SOURCE (!) module, as shown here, and then set the configuration for the source module to use this dblink location.
Note that you cannot use the DBLink location to import metadata.
This completes the work on the source and on the dblink location.
Once you have done this, you have associated the target with the source because in the dblink location you specify the from (target) and now by adding this to the source, the to is also known. OWB now generates you a connector on the target using the dblink. Open the connector to see the actual dblink details as specified on the dblink location.
Since this target location is already registered, all info above is grayed out...
Next is the target, where nothing else is really required. You create a mapping from source table to target table and generate code.
As you can see in line 337 the source schema we specified in dblink location and the dblink are taken into the generated code....
Now that you are using this setup, your credentials to for example production schemas are hidden behind the dblink. A DBA will set up the link, you use it without knowing the details for production data.
I'll see if I can quickly add the #2 option at a later point. It is a bit simpler, in that you on the connector override the database link name with your own. However it allows the source to still use full credentials and therefore potentially gives data access to the wrong people.
BTW, not what I was thinking when I started but you can apply the same to a "remote target". Simply deploy the mapping in what I have as a target here. Create a remote location and a dblink loc for target (e.g. data going to that). Drag that operator into the mapping and now you go DBLink two ways...
Great for writing to other DBs like SQL Server of course...