With version 21.2 of SQLcl, you have some new, exciting toys to play with! Now, some of you may have heard via the grapevine that the big thing coming to SQLcl was built-in support for vi and emacs when writing or editing your SQL and PL/SQL code.

But before we cover that, I want to cover two additional features.

One, the ability to apply color coding to your text based on our SQL parser.

And two, there’s a new status display bar you can use to see if you have pending transactions, who you’re logged in as, how long your last query took to ran, etc.

Let me show you how that might appear before I show you how to set it up.

As I type, the editor updates the coloring…this might be a bit jarring if you’re a slow typer.

So I’ve got black text with white background comments, green keywords, yellow strings, purple identifiers – thanks Dracula theme makers for the inspiration!

Then at the bottom of my screen I have something telling me my cursor line/char pos, who I’m logged in, the service I’m logged into, my editor mode, my transaction status, and the run time of that last query.

Here are the commands you will need to investigate:

set statusbar
set highlighting
help set statusbar
help set highlighting

Use SET to turn things on or change the configuration, use the ‘help’ to get instructions and examples for doing said SETs.

I’m not going to PRINT that help text here – it’s going to be changing as new versions come out anyway – but I WILL show you my login.sql, so you can use it as a quick start.

Before that, a few more things to know –

  • you can define the ordering of the items shown in the status bar
  • the status bar doesn’t currently, continuously update the statuses
  • the status bar doesn’t currently allow you to add your own custom status items
  • these things are off by default – you must turn them on

My login.sql to setup highlighting and status bar

set statusbar on
set statusbar add editmode 
set statusbar add txn
set statusbar add timing
set highlighting on
set highlighting keyword foreground green
set highlighting identifier foreground magenta
set highlighting string foreground yellow
set highlighting number foreground cyan
set highlighting comment background white
set highlighting comment foreground black 

What this looks like, in real time…

Yes, I type real slow like…more like I didn’t know what I was going type until I typed it.

You’ll notice the status bar was changing as I was building the SQL to show the current curpos by line:character number.

Let’s talk about the status bar some more…or better yet let’s demo!

Keep your eye on the status bar items…

So after our ‘big insert’ we have ‘Changes Pending.’

But, as soon as our INDEX is created, it goes back to ‘None.’

Why? Because executing DDL is an implicit COMMIT.

Did you also notice that the text on screen and cursor didn’t start jumping around the screen as I invoked the command history (with my up arrow key?) We’ve FIXED that behavior in this release as well!

This DDL/DML scenario courtesy our AskTom hero, Connor. He’s been helping me in his spare time to ‘beta test’ SQLcl’s new editor features. Thanks Connor!

Wait, wait…what about Windows?

It works/looks exactly the same in your Windows CMD prompt! Our developer on this feature uses Windows by default, and I of course have a Windows corp machine where I do MOST of my work. Everything I’ve shown on my Mac here should appear the same in your MSFT universe.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

26 Comments

  1. TheOtherJeff Reply

    Hi Jeff,

    I’m somewhat new to SQLCL and I’ve set up my login.sql using the entries from one of your previous posts (https://www.thatjeffsmith.com/archive/2021/07/oracle-sqlcl-syntax-highlighting-and-the-status-bar/). Here are the details from my command window (SQLCL version 21.4):

    SQLcl: Release 21.4 Production on Fri Feb 18 09:50:22 2022

    Copyright (c) 1982, 2022, Oracle. All rights reserved.

    Last Successful login time: Fri Feb 18 2022 09:50:26 -06:00

    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.12.0.0.0

    SQL> show login
    Glogin possible locations
    ——————–
    C:\Oracle\product\19.3\client_1\sqlplus\admin\

    Login possible locations
    ——————–
    .\
    C:\Oracle\product\19.3\client_1\
    C:\Oracle_x86\product\19.3\client_1\
    C:\DEV\SQL\SQLCL\APP\

    login.sql
    ———————
    C:\DEV\SQL\SQLCL\APP\login.sql
    SQL> show highlighting
    HIGHLIGHTING: on
    DEFAULT: foreground default background default
    COMMENT: foreground black background white
    STRING: foreground yellow background default
    NUMBER: foreground cyan background default
    PUNCTUATION: foreground default background default
    KEYWORD: foreground green background default
    IDENTIFIER: foreground magenta background default
    SQL> –why is this not matching my highlighting

    Impossible to see here of course but what is displayed on the screen does not match my settings. The ‘–‘ are white while the text “why is this not matching my highlighting” is magenta, and there is no background highlighting at all. I’m currently running windows 10 Enterprise Edition.

    Thanks!

  2. many developers complaining that since version 20 it is impossible to work with sqldeveloper.
    It hangs inconsistently – sometimes just during typing !
    I moved to version 21 – but it is worse !!
    I am working with sqldeveloper since version 3 and I like it so much I made all my company use it for years
    and since version 20 and 21 it is TERRIBLE !! keeps hanging inconsistently

    • Have you opened a support ticket? Have you reported it to the forums?

      I’m not aware of any open bugs that would explain your experience with 21.2, but that doesn’t mean I don’t believe you.

      I need more data to investigate your issue. That probably includes a jstack dump taken while the app has hung.

      Other things would be about your machine, what you’re doing, and the version of Oracle Database you’re working with.

  3. I love the highlightning feature. But on my OEL VM, the colors are very dim, not as bright as in the screenshot above. Any idea how to fix this?

    • My guess is that your Java doesn’t have access to the Nashorn engine.

      What version of Java are you using in SQLcl?

  4. I’m using 21.2, jre1.8.0_291, WSL Ubuntu 20.04.
    2 issues detected, so far.
    1) After terminating sqlcl with “exit/quit” a leading blank is added to my Linux prompt. After hitting an Enter the blank disappears.
    2) “set statusbar” overriddes scrollback buffer. My testcase ( my monitor is very small, only 40 rows, so adjust the test case accordingly):
    seq 20
    sql usename/pw
    select level col1 from dual connect by level the output of “seq 20” is not more visible

    • thanks Jay, we saw the issue with Windows and will add your test case with Linux. Appears to be a bug with the underlying library we’re using.

  5. Great post explaining great enhancements – Thanks!

    When launching SQL*Plus on the same machine, the new SQLcl (g)login.sql entries produce warnings/errors.
    Is there a way to use separate/cascading login scripts between SQL*Plus and SQLcl?

    This would be very helpful for testing scripts before shipping to (legacy) systems where only SQL*Plus is installed.

  6. Ahmed AbdelFattah Reply

    OS: Windows 10
    Version of Java (used by SQLcl): java version “1.8.0_261”
    Amount of physical memory: 24GB

    • OK, there’s no reason you should be seeing this message or needing to set any flags. let me ping the dev for ideas.

  7. From the 21.2 README.md, I see:
    Memory settings:
    We’ve put the max memory settings to 2gb now to accommodate very large buffers

    I changed the following line in the sql file:
    AddVMOption -Xmx2G
    to
    AddVMOption -Xmx64M
    but the issue still there.

  8. Something strange with the latest version 21.2, I got the following errors while trying to run it:

    C:\Users\win>d:\sqlcl212\bin\sql sys/oracle@localhost:1523/prod as sysdba
    Error occurred during initialization of VM
    Could not reserve enough space for 2097152KB object heap

    On the same machine, I can run the previous version 21.1 successfully:

    C:\Users\win>d:\sqlcl211\bin\sql sys/oracle@localhost:1523/prod as sysdba
    SQLcl: Release 21.1 Production on Tue Jul 06 18:18:58 2021
    Copyright (c) 1982, 2021, Oracle. All rights reserved.
    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0

    SQL> exit

    I have enough memory in my machine (24GB).
    I tried to set the JAVA_OPTIONS environment variable, but this didn’t fix the issue.
    What may be the issue here?

    • Tell me about your machine –
      OS,
      Version of Java (used by SQLcl),
      Amount of physical memory

      Jeff

  9. Doesn’t seem to work, the SET options are not accepted:

    SP2-0158: unknown SET option beginning “highlighti…”

    Regards
    Nitish

  10. Thanks Jeff for sharing this nice new features.
    Regarding Windows, I have some challenges getting the same output there.

    Let me paste here the output:

    C:\Users\win>d:
    D:\>cd sqlcl211

    D:\sqlcl211>ls
    21.1.1.113.1704 bin login.sql
    README.md lib

    D:\sqlcl211>cat login.sql
    set statusbar on
    set statusbar add editmode
    set statusbar add txn
    set statusbar add timing
    set highlighting on
    set highlighting keyword foreground green
    set highlighting identifier foreground magenta
    set highlighting string foreground yellow
    set highlighting number foreground cyan
    set highlighting comment background white
    set highlighting comment foreground black

    D:\sqlcl211>
    D:\sqlcl211>bin\sql sys/oracle@localhost:1523/prod as sysdba

    SQLcl: Release 21.1 Production on Mon Jul 05 00:27:39 2021

    Copyright (c) 1982, 2021, Oracle. All rights reserved.

    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0

    login.sql found in the CWD. DB access is restricted for login.sql.
    Adjust the SQLPATH to include the path to enable full functionality.
    SP2-0158: unknown SET option “statusbar”
    SP2-0158: unknown SET option “statusbar”
    SP2-0158: unknown SET option “statusbar”
    SP2-0158: unknown SET option “statusbar”
    SP2-0735: unknown SET option beginning “timing”
    SQLPLUS command failed – not enough arguments
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0268: numwidth option not a valid number
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0158: unknown SET option beginning “highlighti…”

    SQL> exit
    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0

    D:\sqlcl211>set SQLPATH=D:\sqlcl211

    D:\sqlcl211>bin\sql sys/oracle@localhost:1523/prod as sysdba

    SQLcl: Release 21.1 Production on Mon Jul 05 00:28:16 2021

    Copyright (c) 1982, 2021, Oracle. All rights reserved.

    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0

    SP2-0158: unknown SET option “statusbar”
    SP2-0158: unknown SET option “statusbar”
    SP2-0158: unknown SET option “statusbar”
    SP2-0158: unknown SET option “statusbar”
    SP2-0735: unknown SET option beginning “timing”
    SQLPLUS command failed – not enough arguments
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0268: numwidth option not a valid number
    SP2-0158: unknown SET option beginning “highlighti…”
    SP2-0158: unknown SET option beginning “highlighti…”

    — If I executed the set commands directly from within the sqlcl, most of them run successfully, but I didn’t get the color coding or the status bar:

    SQL> set statusbar on
    SQL> set statusbar add editmode
    SQL> set statusbar add txn
    SQL> set statusbar add timing
    SP2-0735: unknown SET option beginning “timing”
    SQLPLUS command failed – not enough arguments
    SQL> set highlighting on
    SQL> set highlighting keyword foreground green
    SQL> set highlighting identifier foreground magenta
    SQL> set highlighting string foreground yellow
    SQL> set highlighting number foreground cyan
    SP2-0268: numwidth option not a valid number
    SQL> set highlighting comment background white
    SQL> set highlighting comment foreground black
    SQL>
    SQL> — comment
    SQL> select sysdate from dual;

    SYSDATE
    ____________
    05-JUL-21
    SQL>

    I’m using the normal Windows’s “Command Prompt” program.
    — I have captured a video of the above short demo, If applicable, I can send it to you. —

    What may be the reason behind these issues?

    Thanks
    Ahmed

Write A Comment