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.