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.
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…
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!
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.
26 Comments
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!
NOTE: I used two dashes for the comment but when I posted the question one was removed.
Me again! FYI, I tested this in version 21.2 and it works for me but does not work in version 21.4.
How about posting this to the Oracle forums where you can easily embed pictures.
This blog post I wrote – I’m using the same highlighting scheme in 21.4, and it’s working just fine for WIN10.
Done!
https://community.oracle.com/tech/developers/discussion/4495182/issue-with-highlighting-in-sqlcl-21-4-1-windows-10/p1
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.
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?
I’ll take a look, will send you a slack…
Looks like calling a script is broken:
https://community.oracle.com/tech/developers/discussion/4486461/issue-running-js-scripts-in-sqlcl-21-2
My guess is that your Java doesn’t have access to the Nashorn engine.
What version of Java are you using in SQLcl?
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.
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.
Put your login somewhere only SQLcl will see it.
If you run ‘show login’ it will tell you where we look.
Another great new command – Thanks!
You’re very welcome!
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.
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.
So you’re not going to answer my questions…ok?
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
Doesn’t seem to work, the SET options are not accepted:
SP2-0158: unknown SET option beginning “highlighti…”
Regards
Nitish
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
You need version 21.2, released last week.