Compilation Error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Compilation Error

  1. #1
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360

    Compilation Error

    I'm going mad with an odd compilation error:

    When I try and compile a procedure it fails with ORA-00942: table or view does not exist. The table in question (organisation) is on a remote database and the synonym is a private one. The db link used connects as the owner of ORGANISATION on the remote database and is a private one. I can also do a select count(*) from the table in an SQL Plus session:

    Code:
    SQL> select count(*) from ORGANISATION;
    
      COUNT(*)
    ----------
            73
    But yet the procedure doesnt seem to be able to see the remote table???? Confused!
    Oh - both local and remote databases are 9.2.0.4 on HPUX

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    If you are getting the grant through a role. Try granting the privilege directly. This should solve your problem.

  3. #3
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    But the table is on a remote machine. There is a private synonym that uses a db_link connecting as the table owner of the remote table, so privs arent an issue:

    Code:
    SQL> create user blah identified by blah;
    
    User created.
    
    SQL> grant CREATE SESSION, create synonym, create database link to blah;
    
    Grant succeeded.
    
    SQL> conn blah/blah
    Connected.
    
    SQL> create database link fc.world connect to ice identified by **** using 'icepw01.world';
    
    Database link created.
    
    
    SQL> create synonym blah for organisation@fc.world;
    
    Synonym created.
    
    SQL> select count(*) from blah;
    
      COUNT(*)
    ----------
            76
    Last edited by JPnyc; 06-03-2009 at 08:29 AM.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Does 'ice' have privileges directly granted to it for icepw01.world on the remote machine?

    If not that could be your problem.

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by gandolf989
    Does 'ice' have privileges directly granted to it for icepw01.world on the remote machine?

    If not that could be your problem.
    The man is a genius.
    I remember when this place was cool.

  6. #6
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    ICE is the owner of organisation!

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Originally posted by Mr.Hanky
    The man is a genius.
    Flush the poo!

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Everytime I have seen this type of error it was because of some kind of rights issue.

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