Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: Referencing foriegn table

    I have an oracle instance on my local machine and on a remote machine on the same network. To avoid duplication, we moved our common tables to the remote machine. We run procedures from our local machines that will need to reference the tables on the remote machines.

    The network machine's instance has a schema named AUDIT_Master that contains a lookup table. I have the following statment:
    v$lookup_code AUDIT_MASTER.lookups.lookup_code%TYPE;

    It cant find the AUDIT_MASTER table. I have tried adding the database name to it:
    <databasename>.AUDIT_MASTER.lookups.lookup_code%TY PE;
    but it doesn't like that either.

    I'm fairly new to oracle but not programming in general. I'm probably missing something fairly basic but cant find out whats wrong.

    I'm assuming this can be done - across databases on different machines.
    How?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    did you create a dblink to the remote database?
    once that is done make sure you grant proper access permissions and have a proper tnsnames entry
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    (i'm using TOAD)
    I created a DBlink called Master_Prod.
    Then tried:
    lookups@master_prod.lookup_code%TYPE;
    it still tells me that: identifier 'MASTER.US' must be declared

    I checked and the Link Name is really : Master_Prod.us.oracle.com
    so I tried that
    lookups@master_prod.us.oracle.com.lookup_code%TYPE;
    same error.


    we use the same username/password on both boxes.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    To simplify things, I would create local synonyms that pointed to the real tables over the database link. This will greatly simplify your coding.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Works like a champ...Thanks!

Posting Permissions

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