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.

JSON_TABLE and the Top 2000

Oracle database 12c provides native JSON parsing. It is relatively easy to go from data in a JSON format to a relational representation.
In this example I will use data from the Top 2000. Every year in The Netherlands a playlist is produced by popular vote and fully broadcast between Christmas and the last day of the year, with the number one being played just before the New Year. You can see the complete list of 2017 on the official NPO website.
The Top 2000 list that I'll use for this example is the one from 2017.

The data from the JSON file looks like this:

Only the first part of the JSON file is shown, with the first two songs.
At the highest level there are three name-value pairs ("success", "message" and "messages") and an array named "data".
The "data" array contains another array with JSON objects containing information about the songs.
Each of these JSON objects contain name-value pairs, with very short none descriptive names, like "s" and "a". You might guess what these names would mean.
Even though the "data" attribute is a nested array, at the top level it is only one level deep.
The complete JSON-file can be downloaded by following this link.

At first I want to get the JSON file into the database, before I extract the values that I need.
First create the table and a check constraint to make sure that is JSON.

create table temp2000 
(complete_list clob);

alter table temp2000
add constraint list_is_json check (complete_list is json)
/

This table will hold the complete JSON file containing the Top2000. The check constraint on the column will verify that only correctly formatted JSON is allowed.
Now add the JSON-file to the table, the easiest way to do this is by adding a row using SQL Developer and copy-paste the complete JSON.

What I want to extract from the complete JSON file is the information about the artist, songtitle, release year, current position in the list, as well as the position in the list from last year.
My guess would be that the "a", "s", "yr", "pos", "prv" are the attributes that I need.
With a CTAS (Create Table as SELECT) and the JSON_TABLE operator I can transform the JSON to relational representation.

create table top2000
as
select songtitle
      ,artist
      ,release_year
      ,curr_position
      ,prev_position
  from temp2000 t
      ,json_table (t.complete_list format json, '$.data[0][*]'
         columns (
           songtitle     varchar2(150) path '$.s'
          ,artist        varchar2(150) path '$.a'
          ,release_year  number path '$.yr'
          ,curr_position number path '$.pos'
          ,prev_position number path '$.prv'
         )
      )
/

Because the song information is contained in the "data" array, and only in the nested array, I need to address that array as follows:

'$.data[0][*]'

Zero (in square brackets) representing the first array in the "data" attribute, and a wildcard (the asterisk) to address the other objects in the nested array.

To help with the discovery of the structure (and the paths to the values) of the JSON, Oracle Database 12c Release 2 introduced the JSON_DATAGUIDE function. Unfortunatelly I don't have Release 2 at my disposal right now, so I leave that for a later time.

Links

• NPO Top2000 2017
• Top 2000 JSON from 2017
• Oracle documentation: JSON_DATAGUIDE

SQL Developer tip: Don't open the table

One of things I don't like about Oracle SQL Developer is that if you are browsing and clicking on tables, it can automatically open the table on the right side of the IDE.
There is a simple way to switch that off, just heard how to switch this feature off. Navigate to the Preferences settings:

Goto Database >> Objectviewer, and uncheck the tickbox labelled "Open Object on Single Click"

And that's it.

Oracle 11gR2: Editions and SQL Developer

I know the correct term is Edition Based Redefinition, but that would make the title of this blog post a bit too long.
Over the last weekend Oracle 11gR2 was released on the Windows platform, good news for me. On my laptop I run Windows so I could finally upgrade my demo database from Release 1 to Release 2. I had been doing demo's with a virtual machine, which was ok. Like the one I did last year during the Planboard Symposium
While I was preparing for that presentation, the support for Edition Based Redefinition was added to SQL Developer 2.1, at least that's what the New Features stated. But since March 1st there is SQL Developer 2.1.1 which does have support for Edition Based Redefinition.
This blogpost is not going to explain Edition Based Redefinition in detail, but will show you the support that SQL Developer has for Edition Based Redefinition.

Importing an Excel into an Oracle database

There are several ways to do this, but I will only show you how to do this using SQL Developer. Why? Because I just came across this functionality the other day.
However there are some things you have to pay attention to, you might get frustrated with it otherwise.

SQL Developer: Backspace not working properly

Nowadays I use SQL Developer on a daily basis, and I like it more and more. But every now and then something rears its ugly head, and that can be annoying.

Just this morning I had one of those encounters. The "Backspace"-key stopped working. Not only the backspace, but DELETE, ENTER, navigation with the arrow keys...

I tried re-installing SQL Developer, reboot the computer... no success.

"Google is your friend" at times like these. One of the first hits I came across was this blog post and that solved my problem.


The only thing you need to do is go from the menu "Tools -> Preferences" and in the Preferences window to "Accelerators", push the button "Load Preset" and pick the Default. And that's it.

Now it works like a charm again.

Copy and Paste: Clipboard Items

By accident I discovered something in SQL Developer. Another one of those things I need to remember for future reference. Hope you can use this feature too.

You know you can use CTRL + C for copying and CTRL + V for pasting. Nothing new there, but what is really neat is that you can use SHIFT + CTRL + V to see the contents of all your clipboard items.

Because I needed to do a lot of copying today, this came in really handy.

Display Images with SQL Developer

Images can be stored as a BLOB in the database. SQL Developer can show them as well.

Step 1) Create a Table to store the image in

create table t
(img blob);

Step 2) Upload an Image




Choose an image from your file system, and commit your changes.

Step 3) On the "Data" tab, click the "..." button with the BLOB column.


Step 4) In the dialog box, check the "View as Image" box


And that's it... Looks a lot better than SQL*Plus would display images :)

SQL> select img
  2    from t
  3  /
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL> 

Debugging Associative Arrays

My favorite development tool is PL/SQL Developer by Allround Automations. The Debugger included with this tool is great, I really like it… but… not for Associative Arrays.
Let’s take a look at some code we want to debug

type emp_rt is record
(ename    emp.ename%type
,hiredate emp.hiredate%type
,sal      emp.sal%type
);
type emp_tt is table of emp_rt
index by pls_integer;

First we declare a Record, based on some of the columns of the infamous EMP table. Next we declare the Associative Array based on the Record structure.
Then we need a (packaged) procedure, which we are going to debug. The type declaration and the procedure are both in a Package, of course.

procedure test
is
emps emp_tt;
idx  pls_integer;
begin
select ename
    , hiredate
    , sal
 bulk collect
 into emps
 from emp
;
idx := emps.first;
while idx is not null
loop
  dbms_output.put_line ('Employee: '||emps(idx).ename);
  idx := emps.next(idx);
end loop;
end test;

PL/SQL Developer has a Test Window, where you can run an anonymous block. It is possible to call this window whenever you feel like, no need to be in the context of a Package like in SQL Developer. Nor does the package need a breakpoint. And this is a good thing, 'cause most of time I forget to set breakpoints.

When you are stepping through the code and you want to see the content of the Associative Array, you will get the message:

And that's too bad.

SQL Developer to the Rescue

SQL Developer, Oracle's free IDE, also has a Debugger. And what is really nice about this debugger, is that you can view the content of the Associative Array.

1. Set a Breakpoint in the procedure (this can only be done when you are in edit mode)
   
2. Compile the Package in Debug (with the black compile button
3. Choose Run --> Debug from the menu

4. Run the procedure from the Debug window
5. Execution halts at the Breakpoint

You can look on the "Smart Data" tab to see the content of the Associative Array.

Simply Brilliant! Great Job, Sue!