Printable View
Please don't ask why I want to do the following, I know its crap.
But is it possible to do:
From DB1, query remote synonym x, via a db link on DB2.
On DB2, synonym X references by another db link a sequence on DB3
I've tried setting this up but keep getting ora-2289. Looking this up, leads me to believe this isn't possible. I know I could just go from DB1 to DB3, but this will involve some code changes (trust me, you don't want to know why).
Thoughts welcome.
Bazza
If you have the permissions, conceptually it would work.Quote:
Originally Posted by bazza
Right, got this working...sort of.Quote:
Originally Posted by marist89
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?
Hmm, seems like a permissions thing. Maybe you're getting permission through a role?
Thanks for the tip however I've fixed it by creating a public synonym on database 1 that points to the synonym on database 2. Inexplicably this works, and to be honest I don't understand why.Quote:
Originally Posted by marist89
Bazza