How to invalidate pages in Webcache using database procedure

To invalidate dynamic pages in the webcache, you need send an XML file with the URL of the pages that you want to invalidate to web cache

The advantage to use database procedure to invalidate is that should be invalidated as soon as the data in the database is updated. The better way is create trigger on tables and after update or insert data, trigger can call a procedure to send invalidation message do WebCache.

1. Firt of all you need know what the webcache invalidation port :

example:

$ opmnctl status -l

return:

Processes in Instance: webcache.help2ora.com
-------------------+--------------------+---------+----------+------------+----------+-----------+------
ias-component      | process-type       |     pid | status   |        uid |  memused |    uptime | ports
-------------------+--------------------+---------+----------+------------+----------+-----------+------
dcm-daemon         | dcm-daemon         |     N/A | Down     |        N/A |      N/A |       N/A | N/A
WebCache           | WebCache           |   31709 | Alive    |   47252097 |  1761360 |  23:10:48 | statistics:4002,invalidation:4001,http:80,https:443
WebCache           | WebCacheAdmin      |   32045 | Alive    |   47252068 |    18932 | 511:08:50 | administration:4000

 

2. Create 2 procedures and change in INVALID_RESULT sys.wxvutil.invalidate_exec  (Host IP of WebCache, Port, and Admin Password) :

example: ‘10.70.1.45’, 4001, ‘welcome1’

NOTE: If you need call direct in browser using HTTP Server and DAD, please uncomment the line “htp.p(resultado);” in INVALID_RESULT and call direct INVALID_CALL.

 

INVALID_CALL :

CREATE OR REPLACE PROCEDURE PORTAL_AUX."INVALID_CALL"
 is
 BEGIN
 htp.p('
<script language=JavaScript>
function verify_white(param)
{
    test_parameter = "false"; //Test blank spaces
    tam_parameter = param.length;
    if (tam_parameter != 0)
    {
        for (i = 0; i < tam_parameter; i++)
            {if (param.charAt(i) != " ")
                {
                    test_parameter = "true"; /*Blank space exists*/
                }
            }
        if (test_parameter == "false")  //All Blanks
        {
            return false;
        }
        else
        {
            return true;
        }
    }
    else
    {
        return false;
    }

}

function valida() {
  if (!verify_white(document.invalid.site_name.value)){
     alert("Select the URL !");
     document.invalid.site_name.focus();
     return false;
  }
  if (!verify_white(document.invalid.uri.value)){
     alert("Inválid file or path !");
     document.invalid.uri.focus();
     return false;
  }
   return true;
}

function enviar() {
 if (valida())  {
     document.invalid.submit();
 }
}

function logout() {
     window.location.href="/logout";
}

</script>
 ');
 htp.p('<html><body><p> </p>');
 htp.p('<form name=invalid action=invalid_result method=POST>
        <table border=1 align=center width=60% bgcolor=#E0F0FF cellpadding=1 cellspacing=2>
           <tr><td colspan=2 bgcolor=#FFFFFF align=Center><h4><img src="img.jpg" align=Middle> Invalid Cache ! </h4></td></tr>
           <tr><td colspan=2><h4>Select the URL and give the file or path to be invalidate !</h4></td></tr>
           <tr>
           <td><b>URL:</b>
               <select name=site_name id=site_name>
                 <option selected value=>>>Selecione<<</option>
                 <option value=www.help2ora.com>www.help2ora.com</option>
                 <option value=systems.help2ora.com>systems.help2ora.com</option>
                 <option value=test.help2ora.com>est.help2ora.com</option>
              </select>
           </td>
           <td><b>File/Path:</b> <input type=text name=uri size=30 ></td>
           </tr>
           <tr><td colspan=2> </td></tr>
           <tr>
           <td colspan=2 align=center><input type=button value=Enviar onclick="enviar();"> <input type=reset value=Clear> <input type=button value=Logout onclick="logout();"></td>
          </tr>
        </table>
        </form>
 ');
 htp.p('</html></body>');
 END;
/

 

INVALID_RESULT :

 

CREATE OR REPLACE PROCEDURE PORTAL_AUX."invalid_RESULT"
 (
  site_name   in varchar2,
  uri          in varchar2
) is
 resultado varchar2(32767);
 a_pos1 integer;
 a_pos2 integer;
 parte varchar2(255);
 BEGIN
 sys.wxvutil.invalidate_reset;
 sys.wxvutil.invalidate_adv(null, uri, site_name, null, null, null, null, null,0, null);
 sys.wxvutil.invalidate_exec('10.70.1.45', 4001, 'welcome1', resultado);
 a_pos1 := instr(resultado, 'NUMINV');
 a_pos2 := instr(resultado, 'NUMINV', a_pos1 + 5);
 parte := substr(resultado, a_pos1+8, 5);
 a_pos1 := instr(parte, '"');
 htp.p('<html><body><p> </p><br><h3><center>');
 htp.p('Total of Invalid Objects: ' || substr(parte, 0, a_pos1-1));
 htp.p('<p> </p> <form action=invalid_call METHOD=post><input type=submit value=Voltar></form>');
 htp.p('</center></h3></body></html>');

--dbms_output.put_line(resultado);
--htp.p(resultado);
 END;
/

 

 

3. Create trigger in table to call invalidate

CREATE OR REPLACE TRIGGER PORTAL_AUX.tg_invalidcache
 AFTER INSERT OR UPDATE OR DELETE
 ON PORTAL_AUX.TB_NEWS REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
begin
 PORTAL_AUX."invalid_RESULT('www.help2ora.com:80','header.jpg');
end;
/

 

NOTE:

Other informations you can see in http://download-west.oracle.com/docs/cd/B14099_19/caching.1012/b14046/invalidate.htm#sthref1629 .
The samples and wxvutil package you can see in $ORACLE_HOME/webcache/toolkit

 

 

Good Luck !

4 Comments to "How to invalidate pages in Webcache using database procedure"

  1. 09/05/2011 - 1:31 PM | Permalink

    I would like to thnkx for that efforts you’ve set in composing this website. I’m hoping the same high-grade web site submit from you in the upcoming as well. In fact your creative producing abilities has inspired me to acquire my own website now. Truly the running a blog is spreading its wings quickly. Your write up is actually a great example of it.

  2. 09/15/2011 - 11:17 PM | Permalink

    Incredible! This weblog looks just like my old 1! Its on a completely different subject but it has quite much exactly the same page layout and design. Great choice of colors!

  3. 09/16/2011 - 8:00 AM | Permalink

    Fine function close friend! Happened without a doubt!

  4. 09/30/2011 - 4:22 PM | Permalink

    Excellent points altogether, you just gained brand new reader. What would you advocate in regards to your submit that you produced some days ago? Any certain?

Leave a Reply