use PL/SQL API through two databaselinks

Question

There is a Oracle database (version 8) acting as PL/SQL API, providing access to different procedures and tables in that database.
I have another Oracle database (version 10g) which uses these procedures and tables through database link.
I upgraded my database to version 12g, but apparently there are compatibility issues using database link between versions 8 and 12g. So came up with a plan to create a third database with Oracle version 10g and put it between 8 and 12g as a "proxy".
Made database links from v12g to v10g and from v10g to v8. Created synonyms in "proxy" (v10g) database for tables and procedures in v8 database. I can make a standard SELECT clause from v12g:

    select column from table@dblink;

But putting it into anonymous block:

    declare
     sVar varchar2(200);
    begin
      select column into sVar from table@dblink;
      dbms_output.put_line(sVar);
    end;

gives an error: "PL/SQL: ORA-00980: synonym translation is no longer valid". Is there a way i could access v8 procedures and tables from v12g via v10g - that means through 2 databaselinks?

And the reason why you can't upgrade the v8 database to a version from this millennium is....? — APC, May 08 '16 at 21:56
Another solution, depending on how current the data needs to be, is to create materialized views on the 10g database that regularly pull the data from the 8 via its db link, then query those MVs from 12g. — Jeffrey Kemp, May 11 '16 at 02:59

score 2 · Accepted Answer · answered May 08 '16 at 23:45

Assuming there's a major obstacle preventing the Oracle 8 (8.0/8.1.x) db being upgraded, I think you're going to need to forget the database links. Even if you put a lot of effort into making it work just a bit longer, you've got no guarantee it will work at the next patch.

I'd look at building a Java wrapper round the Oracle 8 functionality. Your 12c database simply makes calls to the Java layer (maybe even as a Java Stored Procedure).

If you must use dblinks, you could try looking at DG4ODBC, so you'd be treating the Oracle 8 DB as a generic 'foreign' database rather than an Oracle DB.

Thank you for your thoughts. "Forget the database links" (that is to say dblinks through 2 db's are not going to work) seems to be the correct answer in this case. — , May 09 '16 at 06:21

use PL/SQL API through two databaselinks

1 Answers1