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)
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 Day
Never knew that the second day of Christmas (yes, we have that in The Netherlands) is called St. Stephen's Day.

14 July 2021

SQLCL 21.2: APEX LIST Command gives error (and a work around)

Recently I've upgraded to the latest SQLCl release, currently 21.2, and found a small bug.
To quickly export APEX applications I often use the "APEX"-commands in SQLCl. Exporting an APEX application is done by issuing the following command:

  apex export 1000
  
where you pass in the number of the application that you want to export.
This still works fine, but I don't always remember the application id, so I use "APEX list" to show a list of APEX-applications. This command is failing in this release unfortunatelly.
To demonstrate that this is happening:
SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.2.0.0 build: 21.2.0.169.1529
  
The "APEX" and "APEX list" commands interchangable.
SQL> apex
2021-07-14 08:55:35.327 SEVERE oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run java.base/java.util.Collections.nCopies(Collections.java:5027)
java.lang.IllegalArgumentException: List length = -3
        at java.base/java.util.Collections.nCopies(Collections.java:5027)
        at oracle.dbtools.raptor.utils.AnsiColorListPrinter.printHeaders(AnsiColorListPrinter.java:90)
        at oracle.dbtools.raptor.utils.ListPrinter.print(ListPrinter.java:96)
        at oracle.dbtools.raptor.utils.ListPrinter.print(ListPrinter.java:68)
        at oracle.dbtools.raptor.utils.ListPrinter.print(ListPrinter.java:62)
        at oracle.dbtools.raptor.utils.ListPrinter.printListofList(ListPrinter.java:208)
        at oracle.dbtools.raptor.newscriptrunner.commands.ApexCmd.printList(ApexCmd.java:250)
        at oracle.dbtools.raptor.newscriptrunner.commands.ApexCmd.handleEvent(ApexCmd.java:173)
        at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:346)
        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:226)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:341)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1049)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:369)
  
As you can see using the "APEX" command in SQLCl raises some kind of Java-exception.
And if you run into an exception, what do you do? Of course you run to tweet about it... knowing that Jeff Smith, Product Manager at Oracle is always listening (or at least it seems that way). He replied with a solution to my problem
And using the ALTER SESSION command to set the NLS_DATE_FORMAT, things work again.
SQL> alter session set nls_date_format = 'DD-MON-RR HH:MI:SSpm'
  2* /

Session altered.
SQL> apex
       WORKSPACE_ID    WORKSPACE    APPLICATION_ID                           APPLICATION_NAME       BUILD_STATUS         LAST_UPDATED_ON 
___________________ ____________ _________________ __________________________________________ __________________ _______________________ 
1500550789501687    ALEX      1000              Master Application                         Run and Develop    18-JUN-21 11:42:49am    
And no, it doesn't have to be that particular format mask for NLS_DATE_FORMAT. You can also a different format mask, like 'dd-mm-yyyy hh24:mi'. It changes how the last column is formatted in your output.

Thanks to Turloch O'Tierney to come up with this soluttion.