Sequences, synonyms and db links

[bazza]

If you have the permissions, conceptually it would work.

Right, got this working...sort of.

database 1, user 1, accesses a public database link to database 2.

The database link user on database 2 then looks at a public synonym (still on database 2). This public synonym then via a second database link goes to database 3.This second database link hooks into the database 3 as the schema owner of the sequence in question.

Yes, the security implications are horrific. Anyhow, when I run the following on database 1 as user1:

SQL> select seq.nextval@database_link1 from dual;

It comes back with the right value.

However if I run an anonymous plsql block (as the same user):

DECLARE
...
BEGIN
....
SELECT seq.NEXTVAL@database_link1
INTO variable
FROM DUAL;
....
END;
/


I get:

ERROR at line 1:
ORA-06550: line 432, column 24:
PL/SQL: ORA-02289: sequence does not exist

Any ideas?