Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Updated SQLcl: nicer inline editing and column completion

2 Mins Read

We updated the Early Adopter offering of SQLcl today.

Go get it.

But first, I’m assuming you’ve heard of SQLcl? If not –

Read the slidedeck, watch the movie.

We’ve put in a ton of enhancements and even more bug fixes.

And Kris showed off a new feature today that mimic’s our SQL Developer grid preference for displaying NULL ‘values’…

But what I want to talk about are the insight feature and the inline editor.

SQLcl HAS AN EDITOR BUILT-IN!

Yes, you can still call the ‘edit’ command, and bring up Notepad or vi, both defaults for Windows and *NIX.

Or you can set it to ‘inline’ and it will just launch the same editor you see when you try to arrow through the buffer of a statement you’re currently typing or have pasted in.

Some HELP might be in order at this point:

Note the DEFINE bit.
Note the DEFINE bit.

So in my login.sql, I have this:

SET sqlprompt "_user '@' _connect_identifier >"
SET sqlformat ansiconsole
define _EDITOR=inline
SET pagesize 25

Also, did you notice the bit about the Ctrl key sequences while editing in the inline editor?

Do you remember PC games from the 80’s? Up was always W, down was always S, and so on. Well that works now too. And at any time you want to just execute what you have in the editor, you have a hotkey for that too.

Let’s see a quick demo.

It's much more fun to DO vs WATCH - so get the EA and start coding!
It’s much more fun to DO vs WATCH – so get the EA and start coding!

Expect lots and lots and lots of DEMO and some free stickers at KScope15 next week from Kris and I.

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.

Related Posts

24 Comments

    • thatjeffsmith Post Author

      On my mac, without making any editor changes, running ‘edit’ launches vi session there in the terminal, with the contents of my buffer.

  2. Neil Reply

    The editing feature looks great. Not sure if this is an okay place to post this question (some time after the blog post… but I’ll give it a go!)

    Any idea how to get it working with Sublime Text 3 on Windows 7? (64bit).

    I have it working fine by default, with it sending the text to Notepad and reflecting the changes once Notepad saves the .buf file.

    I can edit my login.sql file and then it sends the text to Sublime, but when I save it, nothing gets picked up in the SQLcl session… I checked the location of the file and it’s the same in both cases (eg C:\Users\\afiedt.buf ). Perhaps it’s something that Sublime isn’t doing quite correctly, but is this feature known to work okay with other editors (aside from Notepad) on Windows 7?

    Thanks,
    Neil

    • Neil Stoker

      Hi Jeff,

      Any news on the external editor situation in sqlcl?

      My own install of sqlcl install had got messed up when I switched to Java 8 fairly recently (similar to Rajeshwaran here: http://www.thatjeffsmith.com/archive/2015/12/sqlcl-optimizing-your-history-lists/ I was getting the exact same JNI error) but seeing that there was a new version released (sqlcl-4.2.0.16.049.0843-no-jre.zip) I switched to that and sqlcl itself works properly now.

      However, unfortunately, I now find that neither Notepad nor Sublime Text 3 work as external editors (before Notepad used to work, but not Sublime). If I type “ED”, it will cause them to start with a file called “afieldt.buf” opened (in the relevant editor) but then when you make changes, save the file and close it, those never get back to sqlcl itself. Subsequent attempts with “ED” (or “EDIT”) then simply open the same “afield.buf” file again (as it was previously saved), again not feeding back into sqlcl when it’s finished.

      I’d be happy to provide any useful diagnostic info etc if there’s anything you could advise I check/provide?

      This does stop me using sqlcl, but I’m hoping it’ll be a simple fix as I’m really enthused about this as a great alternative to the full SQL Developer (which has far too many bells and whistles for my needs and which runs painfully slowly for me, despite attempts at tuning it)

      Many thanks,
      Neil

  4. Sam Powell Reply

    Hi Jeff,
    I’ve been playing around with sqlcl a bit, version 4.1.0.15.278.1216 and it never writes a afiedt.buf. I have vi setup as my default editor and when I type ed I get the “SP2-0223: No lines in SQL Buffer” error. If I up arrow though, I see the sql.
    Thanks
    Sam

    • thatjeffsmith Post Author

      i just tried it and it works for me…so, what OS are you running on, what shell are you in?

    • Sam Powell

      windows 7 64bit, tried with cmd and with power shell.

    • thatjeffsmith Post Author

      so when you say vi, you mean something like VIM. if you set your default editor to Notepad, does it work then?

    • Sam Powell

      sqlcl only creates the file in the sqlcl\bin directory, so if I cd to another directory it can’t find it.
      if I stay in the sqlcl\bin then it does create a afiedt.buf. I’m never in that directory though, as I have scripts I run and also write sql.

    • thatjeffsmith Post Author

      Hmmm, i see a problem. Using CD is changing up where we’re looking for the buffer file and it should be consistent, cause i’m getting different buffer contents than expected depending on my directory location when i write the query…

    • Sam Powell

      and if i run the same query with sqlplus from the same directory, it works as expected, writes a afiedt.buf file to that directory.

    • Sam Powell

      I figured it out, stupid me, the file was being written to where sql was started from, not where I’d cd’ed to.
      Thank you for this blog.
      Sam

  5. Wisconsin Wes Reply

    Any chance you can get sqlcl to work inside Mintty (Cygwin)? When I try it starts up and gets to the User prompt and then hangs and uses lots of CPU and memory.
    Looks like it works in PowerShell, basically anything is better than cmd prompt.

    • thatjeffsmith Post Author

      agreed on cmd prompt – that’s what I have ConEmu64

      i’ve been told that there’s a decent terminal/shell in Windows 10

    • Wisconsin Wes

      So in cygwin (mintty or xterm) I put in…

      wwf@PK2PYBG ~ $ sql

      SQLcl: Release 4.2.0.15.286.1242 RC on Wed Oct 14 16:27:52 2015

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

      Username? (”?) wwf

      And there java hangs using 25% of my CPU and 600mb of memory. I can log in using sql usr/pwd@db and it logs in fine. But again it hangs when I try and do anything. If I then control-c (kill) it I get the last thing I put into sqlcl as the very next thing it runs back in at the cygwin level. If I run it in dos (cmd) it runs perfectly.
      I am running 64 bit Cygwin.
      The download from is still giving me 4.2.0.15.286.1242, so perhaps the Oracle site hasn’t had the sqlcl download updated yet?

    • thatjeffsmith Post Author

      that’s the right version

      as soon as i enter my user, it prompts for pwd

      what if you try to launch it with a complete connect string?

    • Wisconsin Wes

      I can start it with username/password@database and it logs in just fine from the command prompt. Once I am in sqlcl it no longer accepts any input.

  6. Kate Wheeless Reply

    I am loving sqlcl and the /* csv */ feature, too! However, when I try to execute this statment – to generate a select statement – it does something kind of odd.
    SELECT ‘select /*csv*/ * from (‘ FROM DUAL;

    In sqcl the result is this:
    SQL> SELECT ‘select /*csv*/ * from (‘ FROM DUAL;

    “‘SELECT/*CSV*/*FROM(‘”
    “select /*csv*/ * from (”

    in sqlplus the result is this (the “good” result)
    v717627dba@rasuai3> SELECT ‘select /*csv*/ * from (‘ FROM DUAL;

    ‘SELECT/*CSV*/*FROM(‘
    ————————-
    select /*csv*/ * from (

    Any suggestions for a workaround?

    Thanks again for providing all this info on sql developer and the wonderful new sqlcl!
    Kate

Write A Comment