This discussion is archived
8 Replies Latest reply: Oct 20, 2011 1:56 AM by TomasAlbinsson RSS

Initial error in call to procedure is cached

TomasAlbinsson Newbie
Currently Being Moderated
Hi,

I'm running Apex 4.1.0.00.32, with Apex Listener 1.1.3.243.11.40 in WebLogic 10.3.3.0.
I've noticed that if I run a procedure (/apex/schema.procedure) and the first call is unsuccessful (because of an error in the procedure or lack of privilege), all later calls will also fail.

Example, create this procedure:

create or replace procedure bug as
begin
htp.p('Hello bug');
end;
/

Try to run it using the url http://<server>/apex/<schema>.bug
The web page will show just "Not Found". The listener error log will show
HTTP Status Code: 404 Requested url http://<server>/apex/<schema>.bug is not found.

The problem now is lack of grant. So, do a "grant execute on bug to apex_public_user;".
Refresh the web page but "Not Found" persists.

There are now two options:
- change the url to (for instance) http://<server>/apex/<schema>.Bug (ie change case)
- restart the whole weblogic server (stopping the Apex deployment is not possible)
As changing the case of the url is not practical if the application is generating links, a restart is the option left.

Is this "caching" of initial failure the expected behaviour or have I missed some configuration?
I have no caching enabled in the listener.


Kind regards

Tomas Albinsson
Stockholm, Sweden
  • 1. Re: Initial error in call to procedure is cached
    Udo Guru
    Currently Being Moderated
    Hi Tomas,

    new grants usually only work immediately after a user reconnects to the database. This also affects technical users like APEX_PUBLIC_USER.
    For performance reasons, the APEX Listener uses connection pools, i.e. it doesn't open a new connection for each request, but reuses existing ones managed in a pool. OHS uses a similar concept, which is why you'll always see a certain amount of database sessions for that technical user, no matter how many parallel requests you currently have. The number of sessions present depends on your pool configuration. You can influence that configuration in the JDBC settings of the APEX Listener.
    I'm not sure why restarting the APEX Listener deployment isn't possible on your WLS, but I'm not a WLS expert... Certainly a restart (or redeployment) would flush the connection pool, so your problem would be gone.
    I guess you only have rare cases where such things happen, so I wouldn't recommend to change the JDBC settings to much faster recreation of database sessions, because this can influence your performance significantly. It might be worth considering that on a development system if you are in an early stage of developing a new application. You could use the APEX Listener in standalone mode for such purposes, which could be restarted without caring about other people...

    -Udo
  • 2. Re: Initial error in call to procedure is cached
    TomasAlbinsson Newbie
    Currently Being Moderated
    Thanks Udo,

    I'm with you all the way.
    First call gets a (new or random "pooled") connection. The call gets an error (or is not possible due to privs).
    Then the error or grant is fixed.
    The second call can now get the same or a new connection, right?
    If it's a new, the call should succeed.
    If it's the first connection, shouldn't the call be done again?

    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"...
    Reusing a session is one thing, reusing the result of a previous database call is another. Or...?

    [slightly off topic: my Apex deployment is always in state Prepared. Stopping (any type) gives an error, starting works fine (but has no effect), but it's working fine]


    Tomas
  • 3. Re: Initial error in call to procedure is cached
    Udo Guru
    Currently Being Moderated
    Hello Tomas,

    your scenario fit's to what will happen:
    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.
    The APEX Listener doesn't cache like that. You can specify file caching for certain procedures, but it doesn't sound like you've enabled this feature (and it is disabled by default).

    I still think as long as you don't hit the reuse count for the connections (it's 1000 requests per default in the current APEX Listener release) or get fresh connections due to high load (i.e., the existing amount of connections isn't sufficient to serve all requests and your pool definition allows to increase the number of active sessions) you'll probably always get that error. In the latter case, you might still get that error if you get a connection that was opened before the new grant was issued.

    -Udo
  • 4. Re: Initial error in call to procedure is cached
    mobra Journeyer
    Currently Being Moderated
    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).

    - Morten

    http://ora-00001.blogspot.com
  • 5. Re: Initial error in call to procedure is cached
    Udo Guru
    Currently Being Moderated
    Hi Morten,
    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...
    But I don't think this happens in APEX Listener. Perhaps I'll have time to put on a simple test case for this in a few days when I get back to my test environment... Shouldn't be too hard to reproduce the scenario and see if it's sufficient to do some reconfiguration to the pool to get new connections fast, or if it's really necessary to restart the APEX Listener...

    -Udo
  • 6. Re: Initial error in call to procedure is cached
    TomasAlbinsson Newbie
    Currently Being Moderated
    Hi Morten,

    why would the listener look up metadata for the procedure to call?
    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.


    Kind regards

    Tomas
  • 7. Re: Initial error in call to procedure is cached
    mobra Journeyer
    Currently Being Moderated
    why would the listener look up metadata for the procedure to call?
    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.
    Hi Tomas,

    Let me just repeat again that I don't know exactly how the Apex Listener implements its database calls.

    My comments are based on my own experiences from writing the Thoth Gateway, which essentially does the same thing as mod_plsql and the Apex Listener, except it is written in .NET and runs on Microsoft IIS. See http://code.google.com/p/thoth-gateway/ for more details.

    One reason for caching the procedure metadata is this:

    Let's say you have a procedure with a parameter which is a "table of varchar2" (such as the f01, f02, etc. parameters in the "wwv_flow.accept" procedure in Apex). These can accept zero, one or more values, depending on how many times you have repeated that input element name on the HTML page.

    So the Listener receives a call for the "wwv_flow.accept" procedure with a single value for the "f01" parameter. Without looking in the data dictionary (see DBA_ARGUMENTS), there is no way the Listener can know that the f01 parameter is actually a "table of varchar2" (and needs to be bound as that datatype when invoking the stored procedure). If the value is bound as a string (plain varchar2) parameter, the procedure call fails.

    Now, there are at least two ways to handle this: You can either catch the error, assume it was really a "table of varchar2" parameter, and try again with that; or you can look it up in the data dictionary beforehand to avoid the error. In either case, it's not good for performance to do this for every procedure call.

    The Thoth Gateway actually works this way: It first checks whether it has cached metadata for the procedure. If not, it tries to call the procedure based on just the names and values. If this fails, it silently catches the error and then looks up the procedure metadata from the data dictionary to make sure the parameter types are correct. It then calls the procedure again (and hopefully that should be successful). Then the metadata is cached. Which means that any subsequent invocations of the same procedure don't need to go through the same work again.

    Looking up (and caching) the metadata also has another benefit: If there are input elements on the HTML page that do not correspond to parameter names in the procedure (perhaps some client-side JS framework added some input fields dynamically), then the procedure call would fail. You can see this in action using mod_plsql and DBMS_EPG (you get an error along the lines of "arguments in form not in procedure..."). However, the Thoth Gateway (and I believe also the Apex Listener) checks the incoming parameter names against the metadata, and just discards any parameter names/values that are not in the procedure.


    - Morten

    http://ora-00001.blogspot.com

    Edited by: mobra on Oct 20, 2011 10:52 AM
  • 8. Re: Initial error in call to procedure is cached
    TomasAlbinsson Newbie
    Currently Being Moderated
    Good explanation, Morten!
    So metadata lookup is needed and the result could be cached. Fine.
    In the end, the call should still be made. That's my opinion until someone convinces me of something else.

    The way it (my installation) works now, it seems one of these options is happening
    -after the grant, the new call does not get the grant, so it keeps on failing
    -the error of the first call is cached and returned without making a new call
    -something else :)


    Tomas

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points