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
– APC, Commented May 8, 2016 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
– Jeffrey Kemp, Commented May 11, 2016 at 2:59

Gary Myers · Accepted Answer · 2016-05-08 23:45:02Z

2

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.

answered May 8, 2016 at 23:45

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4252720 Over a year ago

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.

Collectives™ on Stack Overflow

use PL/SQL API through two databaselinks

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related