Sequences, synonyms and db links
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Sequences, synonyms and db links

  1. #1
    Join Date
    Jul 2002
    Posts
    335

    Sequences, synonyms and db links

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by bazza
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jul 2002
    Posts
    335
    Quote Originally Posted by marist89
    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?

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Hmm, seems like a permissions thing. Maybe you're getting permission through a role?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jul 2002
    Posts
    335
    Quote Originally Posted by marist89
    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.

    Bazza

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width