4

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?

jva
  • 2,797
  • 1
  • 26
  • 41
  • 5
    Can you post some example code please! – Tim Jan 18 '11 at 08:52
  • 2
    Are there any views? From what you've said I'd guess you've got an exception handler for all errors (`when others`) that prints `SQLERRM`; if you don't have that the exception will be raised anyway and will give more information on where it's coming from. – Alex Poole Jan 18 '11 at 08:53
  • 5
    The function that you identified, and anything that called in turn, had no dynamic SQL either? Does anything in the chain have an `authid current_user` clause, which could affect behaviour between compile and run time (privileges and resolution)? Are you relying on any role-granted privs, which don't apply in packages? – Alex Poole Jan 18 '11 at 15:14
  • "Now there is a case where..." does that mean that it does not always produce this error? – René Nyffenegger Feb 15 '11 at 13:28
  • @Rene It produced that error only in one particular case. We worked around that (no code changes, no data changes) and we have not seen that problem anymore. – jva Feb 16 '11 at 13:09
  • Do you use some load balanced environment? – Eggi Apr 15 '12 at 12:47

2 Answers2

2

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
0

Since you already found the solution, this wasn't your problem. But I wanted to add a note, that you get this error if the package function has a body, but there's no function signature in the Spec sheet.

NL3294
  • 984
  • 1
  • 10
  • 27