ora 904 invalid identifier 10g

bkreynolds48
bkreynolds48 used Ask the Experts™
on
I have two database links going from one database to two others.
I do a

select owner.function_name@database_link ('50458093010') from dual;

one works the other doesn't
other than the owner and the database link everything is the same including the function

have no clue as to why

the database links test successfully
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
It looks like the function on the one server isn't correct/working.

Author

Commented:
I tried taking the function that works and putting it in the other database but still have the same error
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Log into the other database and make sure the function works.
Double check the database link is using the correct user and thus executing the correct function.

Are the database versions between the working server and non-working server the same?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
it runs fine in the destination server but when it is run in database one with the database link it doesn't work - link is fine - very weird  - database version / os are the same - will go and check the user in the link
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
As a sanity check, on the failed server add a dbms_output.put_line('Hello World'); to the function.  Then "set serveroutput on" on the calling session and call the function.

You should see the output to make sure you are calling the correct function.

Author

Commented:
will give that a try tomorrow - thanks
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
if one function works, and the other one reports "invalid identifier", you may be missing execute permissions on the other one ...

Author

Commented:
the permissions were granted through a role and work fine elsewhere - this is a new build so something was missed

Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial