Scrape a value from a web page from within Oracle using utl_http

Let’s say I want to get the current available version of Oracletool..


Create or replace procedure get_ot_version

as

   req                  UTL_HTTP.REQ;
   resp                 UTL_HTTP.RESP;
   matchcount           number;
   string               varchar2(1024);
   version              varchar2(20);
   errmsg               varchar2(1000);

Begin
 
   --
   -- Set the proxy server, if needed.
   -- 
   --utl_http.set_proxy('http://192.168.1.10:8080');
   --
   -- Make the HTTP request
   --
   req := utl_http.begin_request('http://www.oracletool.com');
   utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
   --
   -- Loop through the response pieces
   --
   resp := utl_http.get_response(req);
   loop
      utl_http.read_line(resp, string, TRUE);
      --
      -- Look for a line with "Latest version is" in it, and parse out the version string.
      --
      Select instr(string,'Latest version is') into matchcount from dual;
      if (matchcount > 0) then
         Select regexp_replace(string,'.*Latest version is  ','') into string from dual;
         Select regexp_replace(string,' - Released.*','') into version from dual;
         dbms_output.put_line('Version: '||version);
      end if;
   end loop;
Exception
   when utl_http.end_of_body then
      utl_http.end_response(resp);
   when others then
      errmsg := substr(sqlerrm,1,1000);
      dbms_output.put_line('Exception raised during page scrape! '||errmsg);
End;
/

And then..

SQL> Set serveroutput on;
SQL> Exec get_ot_version;
Version: 2.3.3

PL/SQL procedure successfully completed.

🙂

Leave a Reply

Your email address will not be published.