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) is -- l_url constant varchar2(500) := 'https://date.nager.at/api/v3/publicholidays'; l_response blob; -- begin 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 (holiday ,description ) select dt ,name 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
begin populate_holidays (p_year => 2022); end; /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 DayNever knew that the second day of Christmas (yes, we have that in The Netherlands) is called St. Stephen's Day.