| Lesson 9 | Overview of Oracle's topology solution |
| Objective | How do I join tables from remote sites in a SQL query? |
Inter Database Communication
Join Oracle Tables from Remote Sites
We can now include any tables from these remote sites by qualifying their remote site name in the SQL query.
This example joins three tables:
- a local ORDER table in Denver,
- a CUSTOMER table in London, and
- a ORDERLINE table in Paris.
SELECT
customer.customer_name,
order.order_date,
orderline.quantity_ordered
FROM
customer@london,
order,
orderline@paris
WHERE
customer.cust_number = order.customer_number
AND
order.order_number = orderline.order_number;
Create Synonym for Remote Tables
The local ORDER table will access the CUSTOMER table at London, and the ORDERLINE table in Paris will also be included.
You can also hide the fact that the CUSTOMER and ORDERLINE tables are remote by creating a synonym that hides the database link name:
Create public synonym customer for customer@london;
Create public synonym orderline for orderline@paris;
The SQL could now be written as if the tables were local to the database.
The next lesson takes a look at the transparent network substrate (TNS).
Overview Toplogy - Exercise