Tips & Tricks - DB-Links, JOINS
The examples below shows how a JOIN set up, and how to create a Db-Link.
Jobs using procedure MIGRATE_SOURCE_DATA may specify any Oracle data-sources in Source Name. In this example we read data from a database link, JOINing PART_CATALOG and INVENTORY_PART.
First Lets create the DB Link.
Follow these to create a DB Link.
CREATE DATABASE LINK <dblink name> CONNECT TO <user> IDENTIFIED BY <passw> USING '<connect string>'
The connect string can be the name of the DB in tnsnames.ora (on the Oracle server), or it can be a complete SQL*Net address, like
Defining a JOIN in the Migration Job
- Create a Migration Jobs using procedure MIGRATE_SOURCE_DATA.
- In this example we read data from a database link, JOINing PART_CATALOG and INVENTORY_PART. So set the Source Name in Database Information tab. Ex: PART_CATALOG@DBLINK A, INVENTORY_PART@DBLINK B.
- Use the Where Clause to join the two tables PART_CATALOG and INVENTORY_PART. Ex: A.PART_NO = B.PART_NO
- If you check the select Stament from Show Select Statment in header, it will look like,
SELECT <COLUMN_NAMES> ROWID FROM PART_CATALOG@DBLINK A, INVENTORY_PART@DBLINK B WHERE A.PART_NO = B.PART_NO
Adding Methods/Mapping Columns
As we enter rows in the Method List, we know that the columns from View Name will be added to tab Source Mapping. Now, we would like table alias A and B from Source Name above to be prefixed to the Source Column. Last column on the Method List is named Source Name, and is intended for this purpose.
Here you can enter a source that will be matched with View Name. You may also apply an alias for your source.
Note that the columns in Source Column are mapped from the current database, and is not necessarily present at the remote database.
You must therefore always check the select (RMB Show Select Statement in header) by executing it in PL/SQL Developer
Also note that OBJID is not prefixed, you have to do that manually.