The second call can now get the same or a new connection, right?Right. If your load didn't change much lately it is very likely to reuse an existing one from pool as well.
If it's a new, the call should succeed.Right.
If it's the first connection, shouldn't the call be done again?I'd expect to see the call actually is issued again. But it's still the same session in the database, and as mentioned before, the database gathers privileges only once, i.e. when the session is created. So the database will still refuse because the session "profile" is still the same. You could try that in sqlplus or SQL Developer as well - you need to reconnect to make new grants effective.
Now it seems that connection looks at the url and says "I've tried that one and it didn't work, here's the error you got then"...I don't think that acutally happens, unless you have some caching mechanism in between that doesn't work well. There is a possibilty that something like Web Cache would store the error page as result, but usually error pages aren't cached.
I'd expect to see the call actually is issued again. But it's still the same session in the database, and as mentioned before, the database gathers privileges only once, i.e. when the session is created. So the database will still refuse because the session "profile" is still the same. You could try that in sqlplus or SQL Developer as well - you need to reconnect to make new grants effective.That's not true, in my experience. In Oracle, grants ("grant XXX on YYY to ZZZ") are immediately visible to a connected session, no need to reconnect.
I don't think that acutally happens, unless you have some caching mechanism in between that doesn't work well. There is a possibilty that something like Web Cache would store the error page as result, but usually error pages aren't cached.I believe the Listener caches the "signatures" of procedures (probably for the lifetime of the Listener process, not the individual database connections), to avoid looking up the metadata (parameter names and types) for every call. Maybe the wrong signature is cached if the very first call is wrong. This is just speculation on my part, since I don't know how the Listener implements this (but having written a mod_plsql replacement for Microsoft IIS myself, see http://code.google.com/p/thoth-gateway/ , I am familiar with the topic).
That's not true, in my experience. In Oracle, grants ("grant XXX on YYY to ZZZ") are immediately visible to a connected session, no need to reconnect.Shame on me, you're right. We do a lot with roles and recently had troubles with that - I probably mixed that up. Of course, even roles are available immediately, it's just that not every client is capable of enabling a new role at runtime.
I believe the Listener caches the "signatures" of procedures (probably for the lifetime of the Listener process, not the individual database connections), to avoid looking up the metadata (parameter names and types) for every call. Maybe the wrong signature is cached if the very first call is wrong. This is just speculation on my part, since I don't know how the Listener implements this (but having written a mod_plsql replacement for Microsoft IIS myself, see http://code.google.com/p/thoth-gateway/ , I am familiar with the topic).Just to assume your speculation is truth and there is metadata caching: I'd expect no metadata to be returned if you don't have access rights, so I'm not sure how anything could be cached towards backend. This would only be possible if the entry is created before the result was fetched and stays empty because it's not being removed upon exception...
why would the listener look up metadata for the procedure to call?Hi Tomas,
Either the user has the correct procedure name and parameter names - the call succeeds.
Or the name or some parameter is wrong - the call fails and is reported to the user.
