Reaching out: the database as Web-browser

Many people may not know of the possibility to use the database as browser. Oracle dumped the RDBMS as database since the introduction of Oracle 8i, and made it some Jack-of-all-Trades. The browser capabilities were introduced even earlier, in release 7.3.3.

Why?

Indeed, you may want to ask: why? What use could this possibly have? Well, for one: gathering statistics about something you do not have direct access to. The weather, for example. Combined with PHP, you could deliver very sophisticated graphs on temperature, rain, moisture, etc, etc. In fact, I used it for such an application log time ago: prevent firefighters from getting asphyxiated by smoke from chemical fires by plotting a safe route to the fire.

METAR

OK, in order to have firefighters use safe access roads, I need to know the current wind direction - how to get that? Luckily, there's something called METAR (flyboy speak for Meteorological Aerodrome Report), and all (military) airfields provide one, as well as all automated weather stations, (weather-)ships and buoys.
The beauty of METAR is that it is released every half-hour, 24*7*365. I suspect the background of METAR was telex; these messages were distributed by telex, later by teletype machines. Just look how there pages are formatted:

ZCZC
SA171155 EHFS
METAR EHFS 171155Z AUTO 26015KT 9999NDV NCD 05/04 Q1016=

ZCZC
SA171325 EHAK
METAR EHAK 171325Z AUTO 13010KT 050V200 9999NDV SCT003/// OVC004///
04/04 Q1011=

ZCZC
SA171325 EHAM
METAR EHAM 171325Z 28015KT 9999 FEW012 07/05 Q1014 NOSIG=

Nowadays, these pages can be found on the internet. A couple of years ago, when I coded for this project, the teletype look-alike was still there. By now, those pages have to be "official", and carry links and graphical elements.

Decode

Let's take a look at a METAR, and take the last one from the previous example:

ZCZC
SA171325 EHAM
METAR EHAM 171325Z 28015KT 9999 FEW012 07/05 Q1014 NOSIG=

ZCZC is the attention signal, the (radio-)teletype "Start of Message" code. Is not a part of the METAR itself, but is a part of the communications protocol.
SA171325 EHAM SA used to signify this is a Surface Aviation observation, but the coding would differ from the METAR standard. Not sure what it signifies here. 171325 is the date (17th) and time (13:25) on which this message was released. Time is Zulu, or GMT.
EHAM is the international designation of the location, according to ICAO (International Civil Aviation Organization) standard. It just so happens to be Amsterdam Airport, Schiphol (or Ships Hell)
METAR Indicates this is METAR coded, as opposed to SA-format.
EHAM See above: designates origin.
171325Z See above: date and time (GMT)
28015KT Wind speed and direction: 15 Knots, from 280 degrees. North being 0 degrees, so this would be West-North-West-Western (WNWW), as West would be 270. Sometimes followed by G33, meaning Gusts of wind up to 33 knots.
9999 Visibility (in meters). 9999 is excellent, better than 10 Km. Sometimes entries like 1000SE 5000N: 1000 meters South East, 5000 meters in Northerly direction.
FEW012 Cloud indication: there are a few (12.5~25%) clouds from 1200 feet upwards. May be suffixed with type of cloud (like CB: cumulonimbus, or thunderstorm cloud)
07/05 Temperature readings (in Celsius): 07 is the current temperature, 05 is the thaw temperature. These can be used to deduct the Relative humidity. Possibly prefixed with the letter 'M' to indicate minus. Zero filled, two position numbers.
Q1014 Pressure in hPa. Current air pressure in Amsterdam is 1014 hPa. Zero filled, four position number.
NOSIG No Significant changes in weather. Civilian code; military use color codes.
= The End-of-Communication indicator. Used to be NNNN.

Eindhoven airport is a military airport; this is their METAR:

ZCZC
SA171425 EHEH
METAR EHEH 171425Z AUTO 27010KT 9999NDV SCT017 BKN020 07/04 Q1016
WHT WHT TEMPO BLU=

There's a nice table here, explaining the color codes.

Of course, there's much more with METAR. The internet has plenty examples, and this is always a nice start. At least, now I know where to look for wind direction, and possibly a forecast.

utl_http

In order to get the internet downloaded into the database, I use utl_http. Basically, the code consisted of only a few lines:

procedure weerrapport is
l_piece   utl_http.html_pieces;
l_url1   varchar2(80) := 'http://www.knmi.nl/product/luchtv/nedactbe.htm';
l_url2   varchar2(80) := 'http://weather.noaa.gov/cgi-bin/mgetmetar.pl?cccc=EHLE';
l_tmp   varchar2(32767);
l_line   varchar2(256);
dummy   boolean;
begin
  l_piece := utl_http.request_pieces (l_url1, 10);
  for i in 1..l_piece.count
-- Piece the lot together again...
  loop
    l_tmp := l_tmp||l_piece(i);
  end loop;
-- Discard everything not in the BODY...
     l_tmp := substr(l_tmp, instr(upper(l_tmp), '<BODY'),32676);
-- Get Deelen (EHDL) and Lelystad info (EHLE)
     into_weer(l_tmp,'EHDL');
  into_weer(l_tmp,'EHLE'); 
  commit;
end weerrapport

Lots of unsafe programming here: I simply work on the basis (not an assumption, back then!) that the page returned will not exceed 20kB. Yes - that is 20kB, not 32kB, as I only fetch 10 pieces (of 2000 byte) of HTML code. By now, the page has increased in size, but it still is about 14kB.
Nevertheless, some mechanism to handle overflows should have been incorporated.

The into_weer procedure is simply a further stripping of unnecessary text, and saving the metar to the database:

procedure into_weer(p_string IN varchar2, p_station IN varchar2)
IS
 l_line varchar2(256);
 begin
 if instr(upper(p_string),p_station) > 0
 then
   l_line := replace(substr(p_string,
                     instr(upper(p_string),p_station,1,2)
                     ,256
                    ),
     chr(10),' ');
   l_line := substr(l_line, 1, instr(upper(l_line),'ZCZC') - 1);
   begin
     insert into weer (bron, site, dag, metar) values
          ('KNMI', p_station, to_date(substr(l_line,6,6),'DDHH24MI'), l_line);
   exception
     when others then
       NULL;
   end;
 end if;
end into_weer;

Again, very bad programming: an exception handler that hides any exception: "I do not wanna know about any mishaps"

Follow-up

Of course, there could be a follow-up: how to get from METAR to ordinary spoken weather forecast, or how to get from temperature and thew point to relative humidity. May be I will.