Lets consider two databases - db_a (Oracle 12.2) and db_b (Oracle 9.2). The way of connection is from A to B. I hadn't managed to connect from B to A using below steps. If you know how, please, share in the comments.
We need proxy database in order to organize supported cascading database links. In my case it was 11.2 database (let name it proxy_db).
What we need to do ? The action plan as in the following :
1. Create empty schema proxy_user in the proxy_db.
2. Create database link proxy_to_db_b connecting newly created schema (1) to the schema in the target (9.2) database.
3. Create private synonyms using database link (2) for all (or for all needed) objects of schema of the target (9.2) database.
4. Create database link db_a_to_proxy_db in the source (12.2) database.
Now you can address tables via table_name@db_a_to_proxy_db clause. BUT, if you're using database links to target (9.2) database inside PL/SQL, you need to do more, as in the following, otherwise you'll not be able to compile your PL/SQL objects.
5. Create empty schema (metadata only and needed PL/SQL objects) target_user in the source database 12.2 (db_a).
6. Create database link (in source 12.2 database) from app schema to schema from item (5) using database link name as in item (2) (proxy_to_db_b).
Now you will be able to modify and compile PL/SQL which depends (via db_link) on objects in the destination (9.2) database.
Look at My Oracle Support note 453754.1 for additional information. This document also says you
you can use dynamic SQL inside PL/SQL to work around (the database link is not resolved at compile time but at run time).
Look at My Oracle Support note 453754.1 for additional information. This document also says you
you can use dynamic SQL inside PL/SQL to work around (the database link is not resolved at compile time but at run time).
Good Luck
