Re: ORA-00904 when selecting from database link

From: Tim X <timx_at_spamto.devnul.com>
Date: 08 Feb 2003 14:52:16 +1100
Message-ID: <87hebfsa33.fsf_at_tiger.rapttech.com.au>

>>>>> "S" == S Pamela McBait <spmcbait_at_hotmail.com> writes:

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

 S> SELECT column1,
 S>        column2, ...

 S> INTO ... FROM schema.table_at_dblink;

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

 S> PL/SQL: ORA-00904: invalid column name

 S> for EVERY column listed in the select statement.

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

 S> Also, in the environment that is having the problem, I can create

 S> a local view of the remote table, and modify the package to select  S> from the local view - when I do this, it compiles fine.

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

What are the db versions? Are they all the same with the same patches applied?

What roles and grants do the two users have? Are they both the same?

My gut feeling would be a permissions difference of some kind between the two users in your test and production db.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!

Received on Sat Feb 08 2003 - 04:52:16 CET