If you know how to edit your code in SQL*Plus, you already know how to do it in SQLcl. But I get asked this a lot, do you support EDIT? Do you support CHANGE? And what does SQLcl offer over those?
So let’s tackle the old and then the new…
C/Old/New
SQL*Plus has the C[HANGE] command.
C[HANGE] sepchar old [sepchar [new [sepchar]]]
Changes the first occurrence of the specified text on the current line in the buffer. The buffer has no command history list and does not record SQL*Plus commands.
We support that.
EDIT filename
It also has the EDIT command.
ED[IT] [file_name[.ext]]
where file_name[.ext] represents the file you wish to edit (typically a script).
Invokes an operating system text editor on the contents of the specified file or on the contents of the buffer. The buffer has no command history list and does not record SQL*Plus commands.
Enter EDIT with no filename to edit the contents of the SQL buffer with the operating system text editor.
We support that.
Inline Editing
SQLcl additionally offers an inline editor.
You can go through your history, find what you want, and then just arrow around the buffer, and make live changes. When you’re ready to execute the statement, you can just hit Ctrl+R.
Help Edit
HR@orcl? >help edit EDIT --------- Invokes an operation system text editor ON the contents OF the specified file OR ON the contents OF the SQL buffer. ED[IT] [file_name[.ext]] The DEFINE variable _EDITOR can be used TO SET the editor TO USE IN SQLcl, _EDITOR can be SET TO "inline". This will SET the editor TO be the SQLcl editor. This supports the following shortcuts ^R - Run the CURRENT buffer ^W - GO TO top OF buffer ^S - GO TO bottom OF buffer ^A - GO TO START OF line ^E - GO TO END OF line HR@orcl? >
35 Comments
Hi Jeff,
inline editor is the last thing which is anoying me a little bit on after I returned to work directly on OSX (before I used to connect to windows with remote desktop and work there) in SQLcl
I have feeling it worked well on 10.11 or somehwere. However now (OSX 11 or OSX 12 etc), ^W and similiar shortcuts do not work for me. It seems like nowadays OSX default terminal app overrides shortucts and takes ^W for deleteing last word (“Delete backwards to the beginning of the word”) https://support.apple.com/en-ie/guide/terminal/trmlshtcts/mac. I am trying to ask you Jeff, as I know you are a (happy) OSX user and you helped me with “host” command in SQLcl. I have seen some older youtube wideos on OSX where it worked (but as I wrote at the begin, it worked for me in the past as well on older OSX release).
Did not find any soulution where to disable OSX terminal shortuts.
OK, it is not MAC OSX terminal relating thing. I have tested on windows, last SQLcl 23.1 and define _EDITOR=inline does not work even on WINDOWS. I mean mainly shortcuts Ctrl+W, Ctrl+S, Ctrl+A, Ctrl +E … nothing. For sure it used to work witk SQLcl 19 on WIN…
mac or windows for me, 23.1, both ctrl+r and ctrl+w work for me
something else is happening with your machine
well surpringly 23.1 SQLcl inline editing does not work for me as well on WINDOWS. Ctrl+W and Ctrl+S not working for moving to the top or bottom of the buffer.
Can you open a SR with MOS so we can get you sorted?
Well raising SR is nor my favorite job. However, I will do that. I have tested also on AIX, SQLcl 19.1 CTRL+W, CTRL+S works like a charm, SQLcl 23.1 not. It seems it was not working in SQLcl even in 22.3 (so they changed it or introduced a bug somehwere between 19.1 and 22.3).
AIX isn’t a supported platform because there’s no oracle Java available there…
AIX is supported. SQLcl works there. IBM OpenJDK for AIX available, no problem at all https://developer.ibm.com/languages/java/semeru-runtimes/downloads/
Right but we don’t support OpenJDK.
You support IBM Java for AIX even for Weblogic Server. “For version 11 and onward the IBM SDK, Java Technology Edition has been renamed as the IBM Semeru Runtime™ Certified Edition” and that you support.
Anyway from 11 onwards, there are very little differences in OpenJDK vs OracleJDK. We have quite many complex application and we did not run into any compatibility issue sofar.
oracle full client 19c comes with bundled IBM J9 VM (build 2.9, JRE 1.8.0 AIX ppc64-64-Bit ) and 19.1 SQLcl works with that. So we will see which java will full client 23c have, for sure it will be 11+ and that will be supported for SQLcl on AIX.
We’re not the weblogic team.
That’s strange as I have downloaded SQL Developer 23.1 for windows, with bundled java (from oracle official page) . And it is OpenJDK. So why jou ship your product with unsupported java?
java -version
openjdk version “11.0.8-internal” 2020-07-14
OpenJDK Runtime Environment (build 11.0.8-internal+0-adhoc..jdk11u)
OpenJDK 64-Bit Server VM (build 11.0.8-internal+0-adhoc..jdk11u, mixed mode)
Well SQL Developer might have sense to have support only for OracleJDK (as you would probably use it only on win/linux/osx) . But if we should forgett sqlplus, we need sqlcl also in AIX, HPUX, zLinux … and oracle does not make OracleJDK for that platforms and has no plan for that.
we’re shipping the Oracle JDK, you can see this if you do ‘java -version’ at a cmd prompt
❯ cd C:\sqldev\231\sqldeveloper\jdk\jresqldeveloper\jdk\jre
❯ cd bin
jdk\jre\bin
❯ java -version
java version "11.0.18.0.2" 2023-02-17 LTS
Java(TM) SE Runtime Environment 18.9 (build 11.0.18.0.2+1-LTS-3)
Java HotSpot(TM) 64-Bit Server VM 18.9 (build 11.0.18.0.2+1-LTS-3, mixed mode)
C:\Users\431968\Downloads>sql.exe /nolog
Picked up JAVA_TOOL_OPTIONS: -Duser.language=en -Duser.country=US -Dfile.encoding=CP1250
SQLcl: Release 23.1 Production on Wed May 31 22:01:08 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
SQL> show java
Java Detail
———–
java.home= C:\Users\431968\Downloads\sqldeveloper\jdk\jre
java.vendor= Oracle Corporation
java.vendor.url= https://openjdk.java.net/ <<<<< —-HERE
java.version= 11.0.18.0.2
It’s lying?
I see that CTRL-w is go to top of buffer. I find that the BASH CTRL-w (delete preceding “word” ) is a really nice feature if you are, say, re-executing a grant command, but you want to just replace the target object_name. Is there a sqlcl line editor equivalent for delete preceding word?
on OSX CTRL + W is (probably) terminal shortcut a and it is overiding SQLcl CTRL+ W, which is quite annoying for me 🙂 Another thing missing in sqlcl for me is possibility to search in command history… (just like CTRL+R in BASH)
select 1
from dual — ctrl+W
gives me
select 1
from
This with Java 17, SQLcl 23.1, and Big Sur (11.4)
So it is not exected, is it? CTRL+W also removed last word on your side.
Right, ctrl+W worked here for me on both Windows and my Mac.
but CTRL+W works now NOT according to manual:
help edit
EDIT
———
Invokes an operation system text editor on the contents of the
specified file or on the contents of the SQL buffer.
ED[IT] [file_name[.ext]]
The DEFINE variable _EDITOR can be used to set the editor to use
In SQLcl, _EDITOR can be set to “inline”. This will set the editor to
be the SQLcl editor. This supports the following shortcuts
^R – Run the current buffer
^W – Go to top of buffer
^S – Go to bottom of buffer
^A – Go to start of line
^E – Go to end of line
^W now works for deleting current word. ^S does not work at all. On 19.1 it does work in accordance with manual, from 22.4 (windows, aix, mac not).
for you it obviously also do not work
“select 1
from dual — ctrl+W
gives me
select 1
from
This with Java 17, SQLcl 23.1, and Big Sur (11.4)”
it should not delete “dual”…
So I have filed: “SR 3-33168943901 : SQLcl buffer shortcuts does not work after upgrade from 19.1 to 23.1
“.
The response from analyst is: “Tested issue and duplicated the issue. Checking if there are any known issues.” – so they have confirmed they can reproduce and ctrl+w should not delete current word (according to docmunetation). So I am waiting for update from dev.
Sorry, there’s been serious confusion here. Ctrl+W is SUPPOSED to delete the previous word.
The keyboard shortcuts have CHANGED.
See ‘show keymap’ for list of shortcuts.
I thought you were saying it wasn’t deleting the word…
Oh, what a misunderstanding 🙂 I was just surprised to find 2 years old post by Justin Warwick, where he wished to have shortcut for killing current word. And suddelny it was working this way and I lost my Ctrl+A, Ctrl+E, Ctrl +W and similiar shortucts.
So I tried “show keymap” and I am simpoly amazed. Now I have found also “set statusbar on”… Why we are missing it in documentation? Your blog perfect, if you exactly know what you are searching for, your blog answers many questions. However if should have been mentioned in documenation as well. Also “help edit” could be canged, I guess
Like very much viins and vicmd mode. Emacs fans could”set editor emacs” adn they get also buffer edit in emacs mode by default.
Maybe last thing which I am missing. Do we have some inerface to search in history (just like in bash ksh). If I would like to find my last command containing T_HARDWARE table, I would like to find it with help of history search.
Agreed – the docs should discuss keyboard mappings and setting up the statusbar, I’m working on that now.
There’s no search built-in for HISTORY, but it’s on my list.
Thanks Jeff!!! I have also mentioned in SR on MOS, that mentioned new (very nice!) features should be documented.
“There’s no search built-in for HISTORY, but it’s on my list.” – Appreciate, thanks for running this blog, it helped me with SQLcl much. I think it is definitely time to get rid of sqlplus and work with SQLcl only.
They have raised also this bug for me:)
Doc 35449390 – REQUEST TO UPDATE DOCS TO INCLUDE INFORMATION FOR SHOW KEYMAP, STATUS BAR, HELP EDIT
Hi,
In the age of IPhones and UX, I am quite surprised to see that Oracle is trying to revive sqlplus. It was not user friendly tool and still it is, what would be the reason to spend resources to such a tool?
I already *LOVED* SQLplus and will love SQLcl even more. But then again, I don’t see the value of IPhones.
that’s ok, neither do I – I have an Android phone 🙂
So, Jeff – SQLcl is cool, but it is still officially an Early Adopter release. It has been EA for a long time – more than a year, I think.
I’m nervous about encouraging my developers to use anything that isn’t OFFICIALLY a supported product. Is that EVER going to happen for SQLcl?
It’s still in Early Adopter. Yes, it’s been a long time. It will be officially made available soon.
I move between SQL Developer, and SQLcl .
The command ^R is different in SQLcl
^R – Run the CURRENT buffer
and SQL Developer
^R – Relpace
It would be really nice if F9 in SQLcl could run current buffer as SQL Developer does
Hi Jeff,
I noticed that using EDIT will create two afiedt.buf files – one in the sqlcl folder (containing the buffer) and one in my user directory (empty). EDIT will always open the empty one in the user directory. Is there any way around this behavior?
Lars I have not had this issue but my log login.sql does
set editf /tmp/sqlbuf.sql
Actually I dynamically set the name to the session.