How can I get "ORA-00904: : invalid identifier" from a valid package?

Question

I have a procedure that is valid and has in it an insert..select statement. Now there is a case where execution of this procedure produces "ORA-00904: : invalid identifier" error from this statement. How is that even theoretically possible? There are no triggers or dynamic SQL.

Also, the ORA-00904 text in sqlerrm is without pointer to any specific identifier that Oracle considers invalid.

Oracle version 9.2.0.8

edit2:

Turns out there was a problem with a function that was called from within that select (replaced it with constants and everything worked). Probably that was the reason that ORA-00904 did not give an identifier. Still, the question remains - how can that be that precompiled code with no dynamic sql gives this error?

Answer 1

I think this kind of error might happen when you access a package where the package is valid but the body needs compilation and throws the exception.

Another reason might be code with authid current_user it runs with the privileges of the current user (not as normal with the privileges of the owning user). Such a procedure might fail when called with one and succeed when executed with another user.