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?
It's because PL/SQL *compiles* things over "1 hop" (for lack of a better term).
From MOS note 453754.1
---------------------------
SYMPTOMS
A PL/SQL block fails with error: ORA-00980: synonym translation is no longer valid, when selecting data from a remote database. The following code demonstrates this issue:
On DB3 (create the table)
CONNECT u3/u3
DROP TABLE tab;
CREATE TABLE tab(c1 number);
INSERT INTO tab VALUES (1);
COMMIT;
On DB2 (create a synonym to the table on DB3)
CONNECT u2/u2
DROP DATABASE LINK dblink2;
CREATE DATABASE LINK dblink2 CONNECT TO u3 IDENTIFIED BY u3 USING 'EMT102U6';
SELECT * FROM global_name@dblink2;
DROP SYNONYM syn2;
CREATE SYNONYM syn2 FOR tab@dblink2;
SELECT * FROM syn2;
On DB1 (create a synonym to the synonym on DB2)
CONNECT u1/u1
DROP DATABASE LINK dblink1;
CREATE DATABASE LINK dblink1 CONNECT TO u2 IDENTIFIED BY u2 USING 'EMT102W6';
SELECT * FROM global_name@dblink1;
DROP SYNONYM syn1;
CREATE SYNONYM syn1 FOR syn2@dblink1;
SELECT c1 from syn1;
This works in SQL but fails when called from PL/SQL
DECLARE
num NUMBER;
BEGIN
SELECT c1 INTO num FROM syn1;
END;
/
ERROR at line 4:
ORA-06550: line 4, column 3:
PL/SQL: ORA-00980: synonym translation is no longer valid
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
CAUSE
This issue was reported in Bug 2829591 QUERING FROM A PL/SQL PROCEDURE IN 9I -> 8I-> 7.3.4, GETTING ORA-980. This bug was closed as 'NOT A BUG' for the following reasons
PL/SQL cannot instruct middle database (DB2) to follow the database link during the compilation phase. Therefore in order for this PL/SQL block to compile and run, both database links dblink1 and dblink2 should be defined on the front end database - DB1. During runtime database link dblink2 will be looked up in DB2 as expected.
SOLUTION
To implement the solution, please execute the following steps:
1. Create a database link dblink2 on DB1 pointing to DB3
SQL> create database link dblink2 connect to u3 identified by u3 using 'EMT102U6';
2. Create and compile the PL/SQL block on DB1.
CREATE DATABASE LINK dblink2 CONNECT TO u3 IDENTIFIED BY u3 USING 'EMT102U6';
SELECT * FROM global_name@dblink2;
DECLARE
num NUMBER;
BEGIN
SELECT c1 INTO num FROM syn1;
END;
/
PL/SQL procedure successfully completed.
TIP: Another option is to use dyanmic SQL in the PL/SQL block as a work around. When using dynamic SQL the database link is not resolved at compile time but at runtime.
---------------------------