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.

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: build:
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
___________________ ____________ _________________ __________________________________________ __________________ _______________________ 
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.

12 November 2020

Automatically Not Null when adding a DEFAULT Clause

Today I learned something new about the DEFAULT ON NULL clause.
Since Oracle database release 12 it is possible to define a DEFAULT ON NULL clause which is used when a NULL is provided as value. The thing I didn't know is that the column is automatically defined as NOT NULL.
Is this documented behaviour? Yes, it is. Quote from the documentation:

When you specify ON NULL, the NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified.

SQL> select banner
  2    from v$version
  3  /

Oracle Database 19c Enterprise Edition Release - Production
SQL> create table t 
  2  (col1 varchar2(10) default on null 'hello')
  3  /

Table T created.

SQL> desc t
Name Null?    Type         
---- -------- ------------ 

And in case you are wondering if you can change the column afterwards to NULL, the answer is "no".

SQL> alter table t 
  2  modify (col1 null)
  3  /

Error starting at line : 1 in command -
alter table t 
modify (col1 null)
Error report -
ORA-01451: column to be modified to NULL cannot be modified to NULL
01451. 00000 -  "column to be modified to NULL cannot be modified to NULL"
*Cause:    the column may already allow NULL values, the NOT NULL constraint
           is part of a primary key or check constraint.
*Action:   if a primary key or check constraint is enforcing the NOT NULL
           constraint, then drop that constraint.

22 October 2020

Qualified Expressions Quite Puzzling - Part Three: Bug!

After some back and forth with Patrick Barel and Steven Feuerstein, it became clear that it most likely is a bug with Qualified Expressions.
A simple case to reproduce the bug is below

SQL> set serveroutput on

SQL> declare
  2     type tbl_t is table of pls_integer
  3        index by pls_integer;
  4     l_tbl tbl_t;
  5     l_idx pls_integer;
  6  begin
  7     for i in 1..3
  8     loop
  9        l_tbl := tbl_t (i => i);
 10     end loop;
 11     --
 12     l_idx := l_tbl.first;
 13     while l_idx is not null
 14     loop
 15        sys.dbms_output.put_line (to_char (l_idx)
 16                                 ||' - '||
 17                                 to_char (l_tbl(l_idx))
 18                                 );
 19        l_idx := l_tbl.next (l_idx);
 20     end loop;
 21  end;
 22  /

1 - 1
2 - 2
3 - 3

PL/SQL procedure successfully completed.
Why it is the above a bug? If you do the a similar assignment, but without a loop, the outcome is different (and most likely correct)
SQL> declare
  2     type tbl_t is table of pls_integer
  3        index by pls_integer;
  4     l_tbl tbl_t;
  5     l_idx pls_integer;
  6  begin
  7     l_tbl := tbl_t (1 => 1);
  8     l_tbl := tbl_t (2 => 2);
  9     l_tbl := tbl_t (3 => 3);
 10     --
 11     l_idx := l_tbl.first;
 12     while l_idx is not null
 13     loop
 14        sys.dbms_output.put_line (to_char (l_idx)
 15                                 ||' - '||
 16                                 to_char (l_tbl(l_idx))
 17                                 );
 18        l_idx := l_tbl.next (l_idx);
 19     end loop;
 20  end;
 21  /

3 - 3

PL/SQL procedure successfully completed.


21 October 2020

Qualified Expressions Quite Puzzling - Part Two

After I published my blog about Qualified Expressions, my dear friend Patrick Barel made a comment that my remarks about the Cursor For Loop was not at all what he expected to happen.

This is the code block that Patrick referred to.

 10     for r in (select * from t)
 11     loop
 12           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 13                                                    ,num2 => r.id));
 14     end loop;
with these results
1 - 1
2 - 2
3 - 3
A different way of looking at this code example might expect the outcome to be only one record, being the last one.
Each iteration would replace the complete Associative Array with a new instance of it.

So, there was another Puzzling thing in the original blogpost that I hadn't noticed before, probably because I wasn't looking for it. Why isn't the whole Associative Array replaced? It seems that this code would append values in the Associative Array and this was my objective. Strange indeed.
Anyhoo, probably the better way of working with Qualified Expressions to append values to the Associative Array, the index should be on the other side of the assignment:
 10     for r in (select * from t)
 11     loop
 12        l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id
 13                                         ,num2 => r.id);
 14     end loop;

When inserting the pointless conditions inside the For Loop, all values from the table are inserted into the Associative Array

 10     for r in (select * from t)
 11     loop
 12        if r.yes_no = 'Y'
 13        then
 14           l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id
 15                                            ,num2 => r.id);
 16        else
 17           l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id
 18                                            ,num2 => r.id);
 19        end if;
 20     end loop;
with all records from the table that get populated in the Associative Array:
1 - 1
2 - 2
3 - 3

20 October 2020

Qualified Expressions Quite Puzzling

Qualified expressions for PL/SQL were introduced in version 18 of the Oracle database. They provide a clearer way of working with collections. At the bottom of this post is the link to the official documentation.
However recently I encountered something that left me puzzled and I still can't explain the reason why. So this blogpost is just about my observations and I welcome any comments.

First a short demo of how you can work with Qualified Expressions.

  SQL> select banner
  2    from v$version;

Oracle Database 18c EE High Perf Release - Production   
  SQL> set serveroutput on

SQL> declare
  2     type rec_t is record
  3         (num1 number
  4         ,num2 number);
  5     type tbl_t is table of rec_t
  6        index by pls_integer;
  7     l_tbl tbl_t;
  8     l_idx pls_integer;
  9  begin
 10     for i in 1..3
 11     loop
 12        l_tbl := tbl_t (i => rec_t (num1 => i
 13                                   ,num2 => i));
 14     end loop;
 15     --
 16     l_idx := l_tbl.first;
 17     while l_idx is not null
 18     loop
 19        sys.dbms_output.put_line (to_char (l_tbl(l_idx).num1)
 20                                 ||' - '||
 21                                 to_char (l_tbl(l_idx).num2)
 22                                 );
 23        l_idx := l_tbl.next (l_idx);
 24     end loop;
 25  end;
 26  /
On line 2 - 4 a Record Type is declared with two numeric attributes. Line 5 and 6 define an Associative Array of the Record Type.
The Qualified Expression is on lines 12 and 13.
In the loop we assign the loop index variable to the Record Type using the Qualified Expression syntax.
To view the content of the Associative Array, lines 16 through 24 are used. When running this block of code the output is:
1 - 1
2 - 2
3 - 3
No surprises here, it works as expected. Well, actually it is surprising take a look at the follow-up blog.

Cursor For Loop

The previous example used a Numeric For Loop, but I observed strange things when using a Cursor For Loop.

  SQL> drop table t purge
  2  /

Table T dropped.

SQL> create table t
  2  (id number
  3  ,yes_no varchar2(1)
  4  )
  5  /

Table T created.

SQL> insert into t values (1, 'Y');

1 row inserted.

SQL> insert into t values (2, 'N');

1 row inserted.

SQL> insert into t values (3, 'Y');

1 row inserted.

SQL> commit;

Commit complete.
The table contains three records with different Yes/No-indicators. Instead of showing the complete block of code I will only show the changes that I made using the previous example.
 10     for r in (select * from t)
 11     loop
 12           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 13                                                    ,num2 => r.id));
 14     end loop;
The Numeric For Loop is removed and a Cursor For Loop is inserted in the code block. Instead of using the index-variable from the first code example, I am using the current count of the Associative Array and add one to that (l_tbl.count + 1).
The results are the same as the first code block.
1 - 1
2 - 2
3 - 3

...but you said Strange Things?

Yes, I did... but so far, it seems normal.
The strange thing happens when a condition is added inside the For-Loop.

 10     for r in (select * from t)
 11     loop
 12        if r.yes_no = 'Y'
 13        then
 14           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 15                                                    ,num2 => r.id));
 16        else
 17           l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id
 18                                                    ,num2 => r.id));
 20        end if;
 21     end loop;
The table has a Yes_No column and inside the For Loop, the value of this column is used.
When you examine the code above, you will notice that it will assign a value to the collection regardless of the value of the column. The assignment is exactly the same in lines 14-15 and 17-18. The content of the Associative Array after the loop should be the same as before, but this is not the case.
The result looks like this:
  1 - 1
  3 - 3
What happened to the second record?... Your guess is as good as mine.

I've tried several variations, like using a local counter to assign instead of the collection.count + 1, but that doesn't make a difference.
Like said before: I welcome your comments for more insight into this strange behavior.

The same is reproducable on Oracle 19...

  SQL> select banner 
  2    from v$version
  3  /

Oracle Database 19c Enterprise Edition Release - Production  


05 June 2020

Conditional Compilation; One Dynamic Package Specification

When you want to make a distinction between different databases (like Development, Test, Acceptance, and Production) and have a single code base, conditional compilation can help out. Most of the time I read about conditional compilation when the use case is to eliminate code from the production environment, like instrumentation, which is of course a legit scenario. Some functionality should never run on Production, but likewise some functionality should never run on Dev, Test or Acceptance.

For example; we sent out emails to customers about their order statusses. This functionality should only be run on the Production database, and never-ever on any of the other databases. Initially there was a toggle in the application dictating if the emails should go to customers or to a dummy email address within the company. How fool-proof is this solution? Short answer: it's not. Sooner or later there will be someone who sets the toggle to "Sent to Customers" in the wrong environment. Of course there are many more examples like this one.

Anyway, to determine the environment which can be used for conditional compilation, I use a package specification which is compiled dynamically. Using the database name, which can be found in the USERENV context, the value is determined. To output a textual representation of a boolean value, I use LOGGER.TOCHAR to output "true", "false" or "null", but of course you can also write your own boolean-to-text-translation.

   l_pack_spec varchar2(32767);
   l_env varchar2(25);
   l_env := sys_context ('userenv', 'db_name');
   l_pack_spec := 'create or replace package environment_pkg'||chr(10);
   l_pack_spec := l_pack_spec ||'is'||chr(10);
   l_pack_spec := l_pack_spec ||'   --=='||chr(10);
   l_pack_spec := l_pack_spec ||'   -- Environment Information, useful for conditional compilation'||chr(10);
   l_pack_spec := l_pack_spec ||'   development constant boolean := '||lower (logger.tochar (l_env = 'DEV'))||';'||chr(10);
   l_pack_spec := l_pack_spec ||'   test        constant boolean := '||lower (logger.tochar (l_env = 'TST'))||';'||chr(10);
   l_pack_spec := l_pack_spec ||'   acceptance  constant boolean := '||lower (logger.tochar (l_env = 'ACC'))||';'||chr(10);
   l_pack_spec := l_pack_spec ||'   production  constant boolean := '||lower (logger.tochar (l_env = 'PRD'))||';'||chr(10);
   l_pack_spec := l_pack_spec ||'   --=='||chr(10);
   l_pack_spec := l_pack_spec ||'end environment_pkg;'||chr(10);
   execute immediate l_pack_spec;

When you run the code above, the resulting code will be something like:

package environment_pkg
   -- Environment Information, useful for conditional compilation
   development constant boolean := true;
   test        constant boolean := false;
   acceptance  constant boolean := false;
   production  constant boolean := false;
end environment_pkg;

Indicating that the current database is the Development database.

Now you can use conditional compilation to include or exclude certain code sections, like:

$if environment_pkg.production $then
   -- Here comes the code to send an email to customers
   -- Here comes the code to send an email to a dummy internal address

To see the effect of conditional compilation, what does the code actually look like, you can use the built in package DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE which output the result to DBMS_OUTPUT

set serveroutput on
   dbms_preprocessor.print_post_processed_source (object_type    => 'PACKAGE BODY'
                                                 ,schema_name    => user
                                                 ,object_name    => 'MAIL_PKG' -- package where you use the conditional compilation