[SOLVED] use PL/SQL API through two databaselinks

use PL/SQL API through two databaselinks

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?

Solution

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.