OWB – Reuse Mapping for Different Data Sources
By David Allan-Oracle on Apr 06, 2011
A common query over the years is how to do more dynamic changes at runtime, with the core OWB mappings, a common technique which has been used through the years is to use Oracle database synonyms (see forum entries such as this). This is useful for when you are doing cookie cutter style data movement from many replicated systems. The synonym always points to a common relational shape that is processed by a mapping, its just that in one execution it may point to schema X table T, and in another point to schema Y table T or even schema Z table T2 over a database link.
For example we may have an ORDERS table in schemas XWEEK and XWEEK_ANO, if we define a synonym ORDERS which points to the XWEEK.ORDERS table we will see how it is used in OWB.
In the database metadata import wizard you should check the ‘Use a synonym to look up objects’ option, this will show private synonyms from USER_SYNONYMS;
When a synonym is imported we get the table metadata imported for the object the synonym points to.
So we get all the columns and keys etc.
When the object is used in a mapping then the synonym name is used in the local schema, so for example we get SELECT … FROM ORDERS which is really using whatever the synonyms is pointing to.
At runtime we can change the synonym definition and point to any schema so long as the shape is the same and the mapping will execute and process the data. Below I have changed the synonym to point to XWEEK_ANO.ORDERS
Executing the mapping again will now consume the data from XWEEK_ANO.ORDERS. Check out the forum post for how to incorporate this into a mapping.