Compilation Error

[fraze]

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:

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

[gandolf989]

If you are getting the grant through a role. Try granting the privilege directly. This should solve your problem.

[fraze]

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:

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

[gandolf989]

Does 'ice' have privileges directly granted to it for icepw01.world on the remote machine?

If not that could be your problem.

[Mr.Hanky]

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.

[fraze]

ICE is the owner of organisation!

[gandolf989]

Originally posted by Mr.Hanky
The man is a genius.

Flush the poo!

[gandolf989]

Everytime I have seen this type of error it was because of some kind of rights issue.