12 October 2021

Everybody needs a break: APEX_WEB_SERVICE #JoelKallmanDay

When Tim Hall announced that there will be a Community blog-day in memory of Joel Kallman, I knew I wanted to participate.
For this post, I'll keep it technical (somewhat).

I'm old enough to remember what a hassle it was to call a webservice from PL/SQL and now appreciate how easy it is. So, this post is about APEX_WEB_SERVICE.
To show how easy it is to work with APEX_WEB_SERVICE and as everybody loves a holiday, I decided to combine the two.
There is a webservice which gives you public holidays (per country) which can be found at: https://date.nager.at/.

For this example I created a table which holds the date and the description of the holiday

  create table my_holidays
   (holiday date
   ,description varchar2(500)

And the procedure to populate the table for a given year is as follows:

create or replace
procedure populate_holidays (p_year in number)
   l_url      constant varchar2(500) := 'https://date.nager.at/api/v3/publicholidays';
   l_response blob;
   l_response := apex_web_service.make_rest_request_b (p_url         => l_url||'/'||to_char (p_year)||'/NL'
                                                      ,p_http_method => 'GET'
   insert into my_holidays
   select dt
     from json_table (l_response, '$[*]'
               columns (dt date path '$.date'
                       ,name varchar2(100) path '$.name'
                       ,nested path '$.counties[*]'
                            columns (county path '$')))
end populate_holidays;

Look at the elegance of the call to APEX_WEB_SERVICE, a very straightforward call to the URL of the Public Holiday API.
An INSERT-SELECT with a JSON_TABLE completes the procedure.

Calling the procedure with

   populate_holidays (p_year => 2022);
yields the following result:
select *
  from my_holidays
01-01-22	New Year's Day
15-04-22	Good Friday
17-04-22	Easter Sunday
18-04-22	Easter Monday
27-04-22	King's Day
05-05-22	Liberation Day
26-05-22	Ascension Day
05-06-22	Pentecost
06-06-22	Whit Monday
25-12-22	Christmas Day
26-12-22	St. Stephen's Day
Never knew that the second day of Christmas (yes, we have that in The Netherlands) is called St. Stephen's Day.


  1. Very Informative. Thanks for the post sir.

  2. Thanks! First actual helpful example. Question please, would the post be the same? If I wanted to send - say - the 2022 table to an API?

  3. This is awesome. Question please, silly one (never done this before).
    If you want to send info to an external API you would use the Post method, right?
    And to get the processed API info back, you would use get?
    (Do you have a post example perhaps?)