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.

FETCH FIRST - you know about this nifty 12c syntax, yes?
FETCH FIRST – you know about this nifty 12c syntax, yes?

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.

edit with something in the buffer...
edit with something in the buffer…
you can change it to EMACS if you want to feel special a la DEFINE _EDITOR =  "vi"
you can change it to EMACS if you want to feel special a la DEFINE _EDITOR = “vi”

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.

the '*' shows which line the cursor is on in case you lose track of where you are...
the ‘*’ shows which line the cursor is on in case you lose track of where you are…

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? >
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.

35 Comments

  1. Pavol Babel Reply

    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.

    • Pavol BabeL

      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

    • Pavol BabeL

      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.

    • Pavol Babel

      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).

    • Pavol Babel

      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.

    • Pavol Babe

      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\jre

      sqldeveloper\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)

    • Pavol Babel

      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

  2. Justin Warwick Reply

    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?

    • Pavol Babel

      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)

    • Pavol BabeL

      So it is not exected, is it? CTRL+W also removed last word on your side.

    • Pavol Babel

      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”…

    • Pavol Babel

      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…

    • Pavol Babe

      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.

    • Pavol Babel

      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.

    • Pavol Babel

      They have raised also this bug for me:)

      Doc 35449390 – REQUEST TO UPDATE DOCS TO INCLUDE INFORMATION FOR SHOW KEYMAP, STATUS BAR, HELP EDIT

  3. 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?

  4. 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.

  5. 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

  6. 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.

Write A Comment