Oracletool v3.0.0 released!

News
=====

Just a reminder that I no longer test Oracletool with Oracle versions < 11.1. All of the legacy HTML tags have been changed to use CSS rather than specifying style data using the old format. The size of the oracletool.pl executable has decreased dramatically, and it looks better. Let me know if there are any pages that are formatted incorrectly, it's entirely possible that I've missed something. *************** * IMPORTANT!! * *************** There are changes to the themes variables in the oracletool.sam file that need to be copied into your oracletool.ini file, or you can create a new one from the supplied oracletool.ini. If you don't do this, your fonts may look very small. If you have a problem with font rendering, such as your fonts look very small, you may need to remove a browser cookie called "OracletoolProps". Also, make sure you've made the changes to the themes in the oracletol.ini file. Enhancements ============ Added a mechanism to search for the oracletool.ini config file on IIS web servers if it's not in the same directory as Oracletool itself. Thanks to Praveen Hombaiah for the code change. Added an interface for viewing the alert log, under the DB Admin section (Finally!). This requires the creation of a procedure under the "SYS" schema. Please see the file get_alertlog_lines.sql under /sql for more information. This also checks for "ORA-" messages in the lines displayed, and highlights them in a separate display. Added a preference setting for the number of lines of the alert log to display. Changed the displays of generated DDL to use textareas with scroll bars rather than just displaying the code. Much better for cutting and pasting. Bug fixes ========= Fixed a bug where when you select Session Info / Active and hit the refresh button, it would show all of the sessions. Thanks to Bryce Tutt for pointing it out.

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&nbsp;&nbsp;','') 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.

🙂