title: Cache-control with Oracle ORDS
body: <p>My DB gets new data only between 1pm-2pm. Users jump back and forth, often looking at the same thing multiple times, I want to use caching to cut network load.  My response type is:</p>
  
  <pre><code>source_type_collection_item
     Executes a SQL Query returning one row of data into a ORDS Standard JSON representation. 
     Available when the HTTP method is GET. 
     Result Format: JSON
  </code></pre>
  
  <p>single row SYS_REFCURSOR response with multiple nested CURSORS.</p>
  
  <pre><code>FUNCTION my_func RETURN SYS_REFCURSOR
  AS
     my_cursor SYS_REFCURSOR;
  BEGIN
     OPEN my_cursor FOR
     SELECT value, CURSOR(SELECT value_2, CURSOR(SELECT ... FROM table)
  ...
  </code></pre>
  
  <p>I want to set differing cache-control based on server time:</p>
  
  <ul>
  <li>Request at 9:30am - cache 3.5 hrs</li>
  <li>Request at 1:30pm - cache 0 mins</li>
  <li>Request at 3:30pm - cache 21.5 hrs</li>
  </ul>
  
  <p>The problem is that I can't figure out how to set caching <strong>at all</strong> for an ORDS endpoint returning HTTP GET.  I have one working fine for an unrelated PL/SQL service</p>
  
  <pre><code>   source_type_plsql :-
        Executes an anonymous PL/SQL block and transforms any OUT or IN/OUT parameters into a JSON representation. 
        Available only when the HTTP method is DELETE, PUT, or POST. 
        Result Format: JSON
  </code></pre>
  
  <p>using a defined parameter:</p>
  
  <pre><code>    ords.define_parameter(
         p_module_name         =&gt; l_module_name,
         p_pattern             =&gt; l_pattern,
         p_method              =&gt; l_method,
         p_name                =&gt; 'Cache-Control',
         p_bind_variable_name  =&gt; 'cache_control',
         p_source_type         =&gt; 'HEADER',
         p_param_type          =&gt; 'STRING',
         p_access_method       =&gt; 'OUT',
         p_comments            =&gt; '');
  </code></pre>
  
  <p>But this won't work for the GET case because:</p>
  
  <ol>
  <li>HTTP GET is unsupported by PL/SQL</li>
  <li>SYS_REFCURSOR as ORDS PL/SQL out parameter unsupported</li>
  </ol>
  
  <hr>
  
  <p>The only option I could figure out was a static cache setting in Tomcat rather than ORDS.</p>
  
  <pre><code>&lt;filter&gt;
     &lt;filter-name&gt;ExpiresFilter&lt;/filter-name&gt;
     &lt;filter-class&gt;org.apache.catalina.filters.ExpiresFilter&lt;/filter-class&gt;
     &lt;init-param&gt;
        &lt;param-name&gt;ExpiresByType application/json&lt;/param-name&gt;
        &lt;param-value&gt;access plus 30 minutes&lt;/param-value&gt;
     &lt;/init-param&gt;
  &lt;/filter&gt;
  &lt;filter-mapping&gt;
     &lt;filter-name&gt;ExpiresFilter&lt;/filter-name&gt;
     &lt;url-pattern&gt;/ords/*&lt;/url-pattern&gt;
     &lt;dispatcher&gt;REQUEST&lt;/dispatcher&gt;
  &lt;/filter-mapping&gt;
  </code></pre>
  
  <p>But that's undesirable for a number of reasons:</p>
  
  <ol>
  <li>It applies to ALL services under ORDS but caching is a bad idea for most other endpoints.  I was unable to filter more precisely than this - from Tomcat's point of view it seems that ORDS is a monolith and gets one cache control setting.</li>
  <li>It is a fixed value.</li>
  </ol>