Thursday, February 4, 2010

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 )
l_clob clob;
l_airport_code varchar2(20) := p_region.attribute_01;
l_direction    varchar2(20) := p_region.attribute_02;

l_clob := apex_web_service.make_rest_request(
p_url => '',
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>');
htp.p('<p><b>Arrivals to ' || l_airport_code || '</b></p>');
end if;

htp.p('<table width="100%">');

for l_rec in (
FROM XMLTABLE ('//airport/flights/flight'
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('<a href="">Flight data from Avinor.</a> Last updated: ' || to_char(sysdate, ' 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):

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!


Jon Trøstheim said...

Nice demo Morten!, thank you for sharing your work


Patrick Wolf said...

Hi Morten,

I think this plug-in will make a perfect example for a "Process" type plug-in which will be possible with EA2.

The plug-in could do the web service call and write the result of the different columns into an APEX collection. (The collection name should be an attribute of the plug-in) That will give the user a great flexibility how he wants to display the data and he can again use all the components APEX provides.

What do you think?

Morten Braten said...

@Patrick: The concept of a "process" type plugin sounds good. I assume that the normal "Conditions" section would apply to the process, so that the developer (ie. plugin user) can use this to control when the plugin/process fires... ?

Patrick Wolf said...

Yes, these process type plug-ins do have conditions, authorization and build config support as well.

You can already try it out on We just have refreshed it to EA2.