11 October 2022

Intervals go ISO-8601 #JoelKallmanDay

Another year, and it's time to write an entry for the #JoelKallmanDay.

My recent discovery in Oracle Database 21c, the innovation release, is how intervals can also be in ISO-8601 format.
The ISO-8601 format is an international standard for describing Date and Time formats in data exchanges.
There are two flavors of interval in the Oracle Database, "Day to Second" and "Year to Month" and there are specialized functions to convert strings in a certain format to an interval. The following example creates a "Day to Second" interval of 4 days, 3 hours, 25 minutes and 42 seconds:

SQL> select to_dsinterval ('4 3:25:42')
  2    from dual
  3* /

+04 03:25:42.000000
Feeding this "Day to Second" interval into a JSON_SCALAR function will return the ISO-8601 standard for this interval:
SQL> select json_scalar (to_dsinterval ('4 3:25:42'))
  2    from dual
  3* /

The reverse is also true, it is possible to feed an ISO-8601 interval into the TO_DSINTERVAL function and get an Oracle interval returned:
SQL> select to_dsinterval ('P7DT4H12M53S')
  2    from dual
  3* /

+07 04:12:53.000000
This works the same for "Year to Day" intervals.

04 April 2022

Interactive Grid: Filter on Column that shows an Icon

For one of our Interactive Grid we wanted to show the status of a column as an icon and still be able to filter on that column. Out of the box this is not possible. When choosing "HTML Expression" it is possible to show an icon in the column values but filtering is no longer possible.
When choosing "Text" as the column it is possible to filter on the column, but you can't specify an HTML Expression to format the column values.
Let's examine this in a little more detail and also show a solution, provided by Andreas Wismann

Let's start with the query that I will use:

select p.id as pid
      ,case p.is_complete_yn
       when 'Y' then 'Yes'
       when 'N' then 'No'
       end as is_complete
      ,case p.is_complete_yn
       when 'Y' then 'fa-check-circle u-success-text'
       when 'N' then 'fa-times-circle u-danger-text'
       end as is_complete_icon
  from eba_demo_proj_tasks p
The last two columns (is_complete and is_complete_icon) are the ones that matter in this example. IS_COMPLETE will provide a proper English word instead of a single letter representing the status. IS_COMPLETE_ICON will translate the single letter to the appropriate icon to be shown in the column values. This column (IS_COMPLETE_ICON) will be hidden because we will use it only to format the column values.

First the plain vanilla version, no icon yet. The screenshots below show the text of the status, as well as the filter that it will produce.

Next we will change the column from "Text" to "HTML Expression", this allows to change the column value to whatever HTML you can come up with. As I want to shown an icon, this is the HTML that I used:

 <span aria-hidden="true" class="fa &IS_COMPLETE_ICON."></span>
This will result in a properly formatted column, the coveted icon that we were looking for, but it is no longer possible to filter on that column. Below are screenshots of the effect that it has.

After struggling with this problem for a while, I reached out on the Twitter APEX Community and I was very happy that Andreas Wismann replied because his solution works like a charm.

As the code doesn't show properly in the screenshot from Twitter, and also for easy Copy-Paste, this is the code that you put in the JavaScript Initialization Code of the column where you want to show the icon
function( config ) {
    config.defaultGridColumnOptions = {
        cellTemplate: '<span aria-hidden="true" class="fa &IS_COMPLETE_ICON."></span>'
    return config;
And the final result in pictures:

17 March 2022

ORA-24017: cannot enable enqueue on QUEUE, %s is an exception queue

Generating documents can take quite some time, which I prefer to do in the background. To facilitate this we use Advanced Queueing (DBMS_AQ). We place the orders that we want to have a document for in a Queue. There is a callback function on the Queue which will take care of the document generation (we use APEX Office Print).
If the above is abacadabra to you, I suggest you read up on DBMS_AQ and callback functions on this blog by Adrian Billington.

Normally this works flawlessly, until recently when there was a typo in the URL to call AOP.
When the callback function would try to call AOP, it was unable to do so. Eventually this would cause that the message in the Queue would be moved to the Exception Queue.

So now we have all these messages in the Exception Queue and still no documents. The plan was to Dequeue the messages from the Exception Queue to the Normal Queue and have them processed again, of course after the typo was corrected.
By default it is not possible to Dequeue messages from the Exception Queue. So step one would be to enable Dequeue for the Exception Queue.

First step: find out the name of the Exception Queue:

  SQL> select name
  2        ,queue_table
  3        ,queue_type
  4    from user_queues
  5* /

                       NAME              QUEUE_TABLE         QUEUE_TYPE 
___________________________ ________________________ __________________ 

Next step, enable the Dequeue on the Exception Queue:

SQL> begin
  2     dbms_aqadm.start_queue (queue_name => 'AQ$_ORDER_DOCUMENTS_QUEUE_E'
  3                            ,dequeue    => true
  4                            );
  5  end;
  6* /

Error starting at line : 1 in command -
   dbms_aqadm.start_queue (queue_name => 'AQ$_ORDER_DOCUMENTS_QUEUE_E'
                          ,dequeue    => true
Error report -
ORA-24017: cannot enable enqueue on QUEUE, AQ$_ORDER_DOCUMENTS_QUEUE_E is an exception queue
ORA-06512: at "SYS.DBMS_AQADM", line 747
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 8626
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 124
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 8540
ORA-06512: at "SYS.DBMS_AQADM", line 742
ORA-06512: at line 2
24017. 00000 -  "cannot enable enqueue on QUEUE, %s is an exception queue"
*Cause:    User tried to enable enqueueing to an exception queue.
*Action:   None.

However this doesn't work, as you can see above.
As I didn't read the error message properly, I thought it said that Dequeue was not allowed on an Exception Queue, but it was complaining about the Enqueue.
The default value for the Enqueue argument in DBMS_ADADM.START_QUEUE is true, and this is not allowed.

SQL> begin
  2     dbms_aqadm.start_queue (queue_name => 'AQ$_ORDER_DOCUMENTS_QUEUE_E'
  3                            ,enqueue    => false
  4                            ,dequeue    => true
  5                            );
  6  end;
  7* /

PL/SQL procedure successfully completed.

Including the Enqueue argument in the call to DBMS_AQADM.START_QUEUE remedies this.

Now it is trivial to Dequeue from the Exception Queue and Enqueue on the Normal Queue:

   l_dequeue_options    dbms_aq.dequeue_options_t;
   l_message_properties dbms_aq.message_properties_t;
   l_message_handle     raw(16);
   l_payload            document_queue_ot;
   l_enqueue_options    dbms_aq.enqueue_options_t;
   for r in (select q.msgid
               from order_documents_queue q
              where q_name = 'AQ$_ORDER_DOCUMENTS_QUEUE_E')
      l_dequeue_options.msgid := r.msgid;
      dbms_aq.dequeue (queue_name         => 'AQ$_ORDER_DOCUMENTS_QUEUE_E'
                      ,dequeue_options    => l_dequeue_options
                      ,message_properties => l_message_properties
                      ,payload            => l_payload
                      ,msgid              => l_message_handle
      dbms_aq.enqueue (queue_name         => 'documents_queue'
                      ,enqueue_options    => l_enqueue_options
                      ,message_properties => l_message_properties
                      ,payload            => l_payload
                      ,msgid              => l_message_handle
   end loop;

After the processing of the Normal Queue, there were still a few messages that popped back into the Exception Queue. After careful examination, it was determined that they could be purged from the Exception Queue:

   po_t dbms_aqadm.aq$_purge_options_t;
   dbms_aqadm.purge_queue_table ('ORDER_DOCUMENTS_QUEUE'
                                ,'qtview.queue = ''AQ$_ORDER_DOCUMENTS_QUEUE_E'''
                                , po_t


02 March 2022

Modify a JSON structure with a single Update

Recently there was a question on the Oracle Community Forum related to JSON, more specifically how to change the structure of it. The link to the Question is at the bottom of this post.
The starting point is the following JSON:

   "tktKey": "123456",
   "attlist1": [
         "attkey": "123",
         "attval": "abc"
         "attkey": "456",
         "attval": "def"
   "attlist2": [
         "attkey": "098",
         "attval": "xyz"
The task at hand was to combine both attlist1 and attlist2 into a single attlist.
To do this the JSON object that is contained in attlist2 need to be extracted and place in attlist1. After that the element attlist2 can be removed from the structure. When this is done, there is an element with attlist1 with three JSON objects in it. Finally the element attlist1 needs to be renamed to attlist.
All of these actions can be done with a single SQL statement using JSON_TRANSFORM.

Let's first create a table to hold the original JSON
   SQL> create table js
  2  (str clob 
  3  )
  4* /

Table JS created.
And insert the JSON Object in there.
   SQL> insert into js values ('{ "tktKey": "123456", "attlist1": [ { "attkey": "123",   "attval": "abc"  },  { "attkey": "456","attval": "def"  } ],  "attlist2": [{"attkey": "098", "attval": "xyz"  } ]}')
  2* /

1 row inserted.
Here comes the magic moment:
   SQL> update js
  2     set str= json_transform
  3                (js.str
  4                ,append '$.attlist1'
  5                    = json_query (js.str, '$.attlist2[*]')
  6                ,remove '$.attlist2'
  7                ,rename '$.attlist1' ='attlist')
  8* /

1 row updated.
With JSON_QUERY (line 5) I extract the JSON Object from attlist2, which is added (APPEND, line 4) to attlist1.
Attlist2 is removed from the JSON object (line 6) and finally on line 7 attlist1 is renamed to attlist.

And to view the results, in a nice readable format:
SQL> select json_query (j.str, '$' returning clob pretty) as result
  2    from js j
  3* /

  "tktKey" : "123456",
  "attlist" :
      "attkey" : "123",
      "attval" : "abc"
      "attkey" : "456",
      "attval" : "def"
      "attkey" : "098",
      "attval" : "xyz"

The more I use the JSON functionality that the Oracle database has to offer, the more I like them!


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.