Skip to Main Content
Forums

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Direct procedure call cached?

Tomas AlbinssonNov 7 2013 — edited Nov 28 2013

Hi,

running Listener 2.0.2.133.14.47 under WLS.

I have asked a similar question before but was not given a good answer, https://forums.oracle.com/thread/2292910

I have a packaged procedure that prints a web page using htp.p().

Sometimes (I cannot reproduce at will) the procedure's result is not updated when I refresh the page in the browser.

That is, pressing F5 (or Ctrl+F5) gives an old result.

I wrote and granted to apex_public_user this package:

CREATE OR REPLACE PACKAGE BODY callpack AS




PROCEDURE calltest (param varchar2 default null) IS


BEGIN


  htp.p('<div>You gave me "'||nvl(param,'&lt;null&gt;')||'". The time is '||to_char(sysdate,'HH24:MI:SS')||'</div>');


  for i in 1..1000 loop


    htp.p('<div>1000 rows of meaningless content</div>');


  end loop;


END calltest;




END callpack;


/

I called this from my browser like this: http://server/apex/my_schema.callpack.calltest

Initially I got a new time with every update (F5). After leaving it for a few minutes and trying again it got "stuck". Every refresh now gives me the same response.

The response header for this apparently cached page looks like this

Accept-Ranges    bytes


Age    1250


Connection    Keep-Alive


Content-Encoding    gzip


Content-Length    423


Content-Type    text/html; charset=UTF-8


Date    Thu, 07 Nov 2013 10:20:22 GMT


Vary    Accept-Encoding


X-ORACLE-DMS-ECID    632c140cd74ad62b:281ea775:1410befd01e:-8000-0000000000d03396


X-Powered-By    Servlet/2.5 JSP/2.1

I have also compiled and granted the above packaged procedure as standalone. This has (yet) not been cached. The response headers there:

Connection    Keep-Alive


Content-Length    49


Content-Type    text/html; charset=UTF-8


Date    Thu, 07 Nov 2013 10:20:08 GMT


X-ORACLE-DMS-ECID    632c140cd74ad62b:281ea775:1410befd01e:-8000-0000000000d04f52


X-Powered-By    Servlet/2.5 JSP/2.1

Besides from restarting the server, the only way I know of to make the listener actually call the procedure when caching is to change case in the url.

So if http://server/apex/my_schema.callpack.calltest is cached, I can get a new page using http://server/apex/My_schema.callpack.calltest

Or I can add a parameter (?param=X) that I change when I want new content.

Has anyone noticed this?

Is there some caching mechanism in the listener (when doing direct pl/sql-calls)? Or is it WebLogic doing something... sometimes...

Kind regards

Tomas Albinsson
This post has been answered by Tomas Albinsson on Nov 28 2013
Jump to Answer

Comments

Tomas Albinsson

After having a call being cached I left the window open, and a few hours later came back and just did a refresh (F5).

Now the page was redrawn once, just to be cached again at the next refresh.

Feels like a time-out of some sort or a session swap perhaps.

So my packaged procedure above seems to be "cache-prone" but not the stand-alone procedure, this has never been cached yet.

Tomas

Tomas Albinsson

Here is the latest news on this subject: procedures get cached depending on their output!

I took my stand-alone procedure and added the meaningless content-loop.

Before this every refresh (F5) gave a new time.

After adding the loop every refresh gives the same (old) time. Until I change the parameter value sent in or change some caSinG in the url.

So I now commented out the loop in the packaged procedure and recompiled. F5 still gave the 1000 lines and old time.

When I changed case of one letter in the url I got the time-only result and now I get a new time with every F5.

I took the url for the package, changed back the cas to original all-lower and opened it in Internet Explorer (as opposed to my usual Firefox).

I got a 10 minute old time stamped page with 1000 rows. Change one letter case and the page is just the time and it updates with every F5 here as well.

I have been told the load balancer does no caching for this server. In that case there is only Weblogic or the Listener left.

Tomas

AndyH 
TomasAlbinsson wrote:




Hi,




running Listener 2.0.2.133.14.47 under WLS.


The response header for this apparently cached page looks like this
  1. Accept-Ranges    bytes 
  2. Age    1250 
  3. Connection    Keep-Alive 
  4. Content-Encoding    gzip 
  5. Content-Length    423 
  6. Content-Type    text/html; charset=UTF-8 
  7. Date    Thu, 07 Nov 2013 10:20:22 GMT 
  8. Vary    Accept-Encoding 
  9. X-ORACLE-DMS-ECID    632c140cd74ad62b:281ea775:1410befd01e:-8000-0000000000d03396 
  10. X-Powered-By    Servlet/2.5 JSP/2.1 

I have also compiled and granted the above packaged procedure as standalone. This has (yet) not been cached. The response headers there:

  1. Connection    Keep-Alive 
  2. Content-Length    49 
  3. Content-Type    text/html; charset=UTF-8 
  4. Date    Thu, 07 Nov 2013 10:20:08 GMT 
  5. X-ORACLE-DMS-ECID    632c140cd74ad62b:281ea775:1410befd01e:-8000-0000000000d04f52 
  6. X-Powered-By    Servlet/2.5 JSP/2.1 

The first set of headers seem to indicate that part of your network flow recognizes that the content that it is returning to you was originally generated 1250 seconds ago - so something is caching.

Doing the same test on my Glassfish/Listener combination produces the following response heading:

X-Powered-By: Servlet/3.0 JSP/2.2 (Oracle GlassFish Server 3.1.2.2 Java/Oracle Corporation/1.7)


Server: Oracle GlassFish Server 3.1.2.2


Content-Type: text/html;charset=UTF-8


Content-Length: 63


Date: Wed, 20 Nov 2013 12:33:45 GMT




200 OK

This might indicate that the caching is occurring within your Weblogic setup or some other cache in the network?

1 person found this helpful
Tomas Albinsson
Answer

Hi Andy,

yes, you were right, something was caching.

When I used the real server name instead of the official DNS name it no longer cached.

Turned out some caching was done by the load balancer that answered on the official name.

This has now been turned off and the problem is solved.

Kind regards

Tomas

Marked as Answer by Tomas Albinsson · Sep 27 2020
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 26 2013
Added on Nov 7 2013
4 comments
2,307 views