SQLcl doesn't respect NLS_LANG & NLS_DATE_FORMAT environment variables the same way that SQL*Plus does…
Brand new Oracle AI Database 26ai installed on Oracle Linux 9:
[oracle@databaseserver DB:my26con 02:52:19 ~]
$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=
Without NLS_LAN & NLS_DATE_FORMAT environment variables, SQL*Plus and SQLcl work the same, but (of course) use the abomination from the 1960s of DD-MON_RR as the default date format.
I also like SQL*Plus' very nice _connection value compared to SQLcl's _connection value for a bequeath connection to the root container… Can we get that too?
[oracle@databaseserver DB:my26con 03:08:30 ~]
$ sqlplus / as sysdba
SQL*Plus: Release 23.26.1.0.0 - Production on Thu Feb 12 03:08:38 2026
Version 23.26.1.0.0
SYS@my26con AS SYSDBA > select sysdate;
SYSDATE
---------
12-FEB-26
[oracle@databaseserver DB:my26con 03:09:53 ~]
$ sql / as sysdba
SQLcl: Release 25.4 Production on Thu Feb 12 03:10:00 2026
SYS@CDB$ROOT AS SYSDBA > select sysdate;
SYSDATE
____________
12-FEB-26
Let's now set the two environment variables and look at the different behavior.
[oracle@databaseserver DB:my26con 03:10:08 ~]
$ export NLS_LANG=AMERICAN_AMERICA.UTF8
[oracle@databaseserver DB:my26con 03:11:05 ~]
$ export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
[oracle@databaseserver DB:my26con 03:11:22 ~]
$ sqlplus / as sysdba
SYS@my26con AS SYSDBA > select sysdate;
SYSDATE
-------------------
2026-02-12 03:11:30 <-- Beautiful, I'm a computer scientist, not some crazy person from the 1960s
tyring to save two characters in dates that couldn't see how much is
going to break in 2051 which is only 25 years away, and additonally caused
so many people to not know that Oracle dates always contain a Century, Year,
Month, Day, Hour, Minutes, and Seconds no matter what your actual date
display format is.
[oracle@databaseserver DB:my26con 03:11:36 ~]
$ sql / as sysdba
SYS@CDB$ROOT AS SYSDBA > select sysdate;
SYSDATE
____________
12-FEB-26 <-- Sadness... I'm still living in the 1960s. :(
It would be awesome if SQLcl worked the same way as SQL*Plus…
And, yes, I do understand that I could modify my glogin.sql to add an alter session set nls_date_format command, but of course, I would actually need a session. Since I'm doing this on a database server, I might not have a session, which again, I understand that I could fix by creating multiple login scripts and only running some commands when I have a session vs not having a session, but that's quite a bit of work, and this feels like it should ‘just work’.
BTW, SQLcl is life-changing and an awesome tool. Thanks so much for everything that you all do with the tool. The SQLcl team has been much more innovative than the SQL*Plus team.