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


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

   -- Set the proxy server, if needed.
   -- Make the HTTP request
   req := utl_http.begin_request('');
   utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
   -- Loop through the response pieces
   resp := utl_http.get_response(req);
      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;
   when utl_http.end_of_body then
   when others then
      errmsg := substr(sqlerrm,1,1000);
      dbms_output.put_line('Exception raised during page scrape! '||errmsg);

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.