ORA-00904 when selecting from database link

I've recently run into a strange problem. We have two separate Oracle database, one for test and one for prod. Both have the same PL/SQL package, which contain procedures that execute SQL statements that look like:

SELECT column1,

       column2,
       ...

The dblink has been created in both databases; both connect to the same remote user in the same remote database. However, when I try to compile the package in one of the two environments, I get the error message:

PL/SQL: ORA-00904: invalid column name

for EVERY column listed in the select statement.

If I copy the select statement out of the package and execute the select statement in a client such as SQL*Plus or SQL Navigator, the select statement executes as expected in both environments, without any of the ORA-00904 errors.

Also, in the environment that is having the problem, I can create a local view of the remote table, and modify the package to select from the local view - when I do this, it compiles fine.

Does anyone have any ideas about what could be causing this (IMHO) bizarre behavior?

Thanks in advance! Received on Fri Feb 07 2003 - 16:23:11 CET