Hi guys,
I trying to access a table from a remote database but im having some problems with the synonyms that point to the relevant table.
I created synonyms as shown here..
CREATE SYNONYM CCS_RA_INTERFACE_DIST FOR CCS_RA_INTERFACE_DIST@SUK_CCS;
CREATE SYNONYM CCS_RA_INTERFACE_LINES FOR CCS_RA_INTERFACE_LINES@SUK_CCS;
The database link is active because if i run select sysdate from dual@suk_ccs i get the system date back.
If i try to access the table using the synonym as shown below i get the results back
select * from ccs_ra_interface_lines/
if i also try to bypass the synonym as shown below i get the results back
select * from ccs_ra_interface_lines@suk_ccs
where rownum<3
/
But i have a problem if i try to access the same synonym from within pl/sql rather than sql. Here is an example
declare n number;
begin
select count(*) into n from ccs_ra_interface_lines;
end;
The above generates the following error
select count(*) into n from ccs_ra_interface_lines;
*
ERROR at line 2:
ORA-06550: line 2, column 30:
PL/SQL: ORA-00980: synonym translation is no longer valid
ORA-06550: line 2, column 2:
PL/SQL: SQL Statement ignored
If i try to access the table directly and bypass the synonym via pl/sql i get a different error.
1 declare n number;
2 begin
3 select count(*) into n from ccs_ra_interface_lines@suk_ccs
4 where rownum<3;
5* end;
10:08:45 > /
select count(*) into n from ccs_ra_interface_lines@suk_ccs
*
ERROR at line 3:
ORA-06550: line 3, column 29:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
What causes the above error? I have a feeling its a permissions issue but i cant figure out what it is. The same owner owns the database link, the synonyms and running the pl/sql modules.
Thanks in advance