My first Apex 4 plugin: Flight Info from Web Service

One of the exciting new features in Apex 4 is the support for plugin regions and items. This feature has huge potential, and will make development with Apex even more efficient, productive, and fun. There are already several plugins out there, and I think we will see a lot of interesting work in this area after Apex 4 is released.

Here is my own first attempt at a (useful) plugin: A region plugin that displays up-to-date flight information for airports in Norway, based on public flight data provided by Avinor, the company that operates the Norwegian airport network.

Avinor has a simple web service that provides flight information in XML format.

I am sure there are similar (web) services for flight information in other countries (feel free to leave a comment below if you know of any).

Here is the PL/SQL code behind the plugin:

procedure render_my_plugin (
p_region              in apex_plugin.t_region,
p_plugin              in apex_plugin.t_plugin,
p_is_printer_friendly in boolean )
as
l_clob clob;
l_airport_code varchar2(20) := p_region.attribute_01;
l_direction    varchar2(20) := p_region.attribute_02;
begin

l_clob := apex_web_service.make_rest_request(
p_url => 'http://flydata.avinor.no/XmlFeed.asp',
p_http_method => 'GET',
p_parm_name => apex_util.string_to_table('airport:direction'),
p_parm_value => apex_util.string_to_table(l_airport_code || ':' || l_direction )
);

if l_direction = 'D' then
htp.p('<p><b>Departures from ' || l_airport_code || '</b></p>');
else
htp.p('<p><b>Arrivals to ' || l_airport_code || '</b></p>');
end if;

htp.p('<table width="100%">');
htp.p('<tr><td>AIRLINE</td><td>FLIGHT</td><td>AIRPORT</td><td>TIME</td><td>GATE</td></tr>');

for l_rec in (
SELECT *
FROM XMLTABLE ('//airport/flights/flight'
PASSING XMLTYPE(l_clob)
COLUMNS unique_id       varchar2(100) path '@uniqueID',
airline         varchar2(10) path 'airline',
flight_id       varchar2(20) path 'flight_id',
airport         varchar2(20) path 'airport',
schedule_time   varchar2(100) path 'schedule_time',
gate            varchar2(100) path 'gate')
ORDER BY airline, flight_id) loop

htp.p('<tr><td>' || l_rec.airline || '</td><td>' || l_rec.flight_id || '</td><td>' || l_rec.airport || '</td><td>' || l_rec.schedule_time || '</td><td>' || l_rec.gate || '</td></tr>');

end loop;

htp.p('</table>');

htp.p('<a href="http://www.avinor.no">Flight data from Avinor.</a> Last updated: ' || to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss'));


end render_my_plugin;

The code illustrates several concepts:

• How to render a region plugin using the PL/SQL Web Toolkit (HTP.P) calls
• How to retrieve values from the attributes defined for the plugin
• Using the new APEX_WEB_SERVICE.MAKE_REST_REQUEST function to retrieve a web page as a CLOB
• Using the XMLTABLE function to transform XML into a recordset that can be used in a SELECT

An export of my plugin can be downloaded here, and installed into your own Apex 4 application.

After the plugin has been installed, using the plugin is as simple as adding a Region (of type Plugin) to the page, and configuring the values for Airport and Direction (the plugin attributes) in the region definition.



You can see a live demo of the plugin here (public page, does not require authentication):

http://tryapexnow.com/apex/f?p=test4ea:plugin_demo:0

Note that for this page, I've also taken advantage of the built-in region caching feature of Apex. The region cache duration is set to 10 minutes, which prevents us from hitting the remote web service for every page view. I really like that you can switch on region caching in Apex without writing a single line of code.

Conclusion: Apex 4 plugins rock!