Tons of bug fixes, mostly.

You can now ask for your working directory via the PWD command.

And of course don't forget we let you CD inside of SQLcl...
And of course don’t forget we let you CD inside of SQLcl…

Download is here.

Scroll down to the bottom, link is on the right side.

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.

27 Comments

  1. Hi Jeff,

    I downloaded sqlcl-18.4.0.007.18181 and when trying to run on Red Hat Enterprise Linux Server release 7.6 (Maipo)
    it giving below error:

    [pts/20][15:56:31:oracle@inchnsirhobbl4 ] ~/sqlcl-18.4.0.007.1818/sqlcl/bin>./sql
    Exception in thread “main” java.lang.UnsupportedClassVersionError: Bad version number in .class file
    at java.lang.ClassLoader.defineClass1(Native Method)
    at java.lang.ClassLoader.defineClass(ClassLoader.java:620)
    at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:124)
    at java.net.URLClassLoader.defineClass(URLClassLoader.java:260)
    at java.net.URLClassLoader.access$100(URLClassLoader.java:56)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:195)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
    at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)

    • Yeah, I changed my mind on that.

      the SQL formatters, a la SET SQLFORMAT, bypass all of the SQL*Plus reporting SET stuff by design

      So use one, or use the other, but if you’re going to use both, then be aware of the SQLFORMAT

  2. Hi,

    I would like to use oci permanently. But it doesn’t work when I paste “-oci” after the java call within sql.bat:

    java %JAVA_OPTS% %STD_ARGS% %DEBUG% -cp “%CPFILE%” oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli -oci %*

    It always tries the thin jdbc until I give the “-oci” option on the command line directly: sql -oci user/password@service

    Any idea?

    Cheers, Markus

  3. Hi Jeff

    After the upgrade, I no longer see the error messages when trying to connect to a database, like “invalid username/password”. The only thing sqlcl shows is:

    sql dbacsa@sidd

    SQLcl: Release 4.2.0.16.112.0616 RC on Tue May 03 08:25:46 2016

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

    Password? (**********?) *********
    ERROR
    dbacsa
    ERROR
    dbacsa
    ERROR
    dbacsa
    ERROR
    dbacsa
    Username? (RETRYING) (‘dbacsa/*********@sidd’?)

  4. Norbert Kiesel Reply

    Another question: why are “insert” statements omitted from the history? You said in a earlier post that the default for the “nohistory” blacklist is empty, ie everything should be entered. However, it seems that at least “insert” and “help” never make it into the history. Can that be changed?

    I often create small test tables from scratch and fill them with some data (yes; I should script that). For this, not being able to edit and resubmit “insert” statements is very inconvenient.

  5. Norbert Kiesel Reply

    While the command line editing is 1000 times better than sqlplus, there is still room for improvement (with suggested key bindinsg from readline):
    – deleting words [meta-d]
    – deleting the entire line (ctrl-u)
    – deleting from current position to end-of-line (ctrl-k)

    My biggest grief is that an [enter] in the middle of a command enters a newline instead of executing the command. This is nearly never what I want, and different from readline/bash or zsh do. [ctl-v][enter] or [ctrl-q][enter] could be used to really insert a newline.

    • control-R will execute immediately

      i think most folks are more used to hitting enter to get to the next line of their code…unless they’re obviously done by using a ‘;’ or ‘/’ ?

    • Norbert Kiesel

      Did not know about [ctrl-r], that is definitely helpful. Can you share a list of supported key-bindings?

      Regarding “most folks are more used…”: not so sure about that. I guess it depends on whether you have a text editor metaphor in mind or more a “shell/single line commands” metaphor. But might also be just the way I use sqlcl: I use sqldeveloper for writing fancy methods and packages, and sqlcl for quick checks.

      One more question: why does commands loose their ‘;’ when pulling them out of the history, and why does a [ctrl-r] works on these w/o adding the ‘;’? I can even add whitespace after such a line and [enter] will execute it. However, after a [crtl-a][ctrl-e] (i.e. just moving cursor around w/o changing anything), [enter] will insert a newline.

    • SQL> help edit

      Most folks, being most SQLDev folks, so GUI minded people.

      Maybe we’re not being consistent on the ‘;’ bit, but in the history, it shows what’s executed, and the ‘;’ isn’t actually sent over the JDBC to be executed, so I’m guessing we’re stripping that out for the history as well.

      SQLcl goes until it sees a ‘;’ or ‘/’ and we’re not supporting SET SQLTERMINATOR to null at this point…it seems to be that allowing for blank lines while writing SQL statements is MORE user-friendly, but I’ll check to see if that’s configurable.

    • Norbert Kiesel

      [ctrl-r] is really nice. It also never shows the problem of having the column names on the same line as the command instead of as a new line in any sqlformat I tested (including ansiconsole).

      I think I would be really happy if I could somehow switch what [enter] and [ctrl-r] are doing. Or even add [ctrl-enter] as an alternative for [ctrl-r]. This would be even consistent with SQLDeveloper, no?

  6. Norbert Kiesel Reply

    A bug for `sqlformat ansiconsole` that is not fixed:
    – often when entering a query command for the first time, the output does not start on the next line but is instead appended to the entered query. This will not happen when running the same query again using .

    One way to reproduce this:
    1. enter `select 42 as answer from dual;`
    2. use ;

    This will produce
    SQL>
    2* select 42 as answer from dual ;ANSWER
    42

    • Norbert Kiesel

      Seems the html-stripper this was eating my input. Using [] now. The 2. from above should be

      2. use [ctrl-p][ctrl-a][enter][ctrl-e];[enter]

    • I run into this a few times, but I’ve never found the triggering/reliable scenario to expose it. Your example doesn’t ‘work’ for me, that is, doing what you’re saying, it just works.

    • Norbert Kiesel

      hmm, strange. Are you sure you have ansiconsole selected when trying? I can’t reproduce either when using the default sqlformat.

      Otherwise might be terminal emulator dependent. If you really can’t reproduce I will investigate further because this hits me quite often (especially with longer queries).

  7. Found one annoying little thing with SQLcL, It looks like it does not support IFILE= in the tnsnames.ora

    Anyone found a workaround other than merging everything into a single file?

  8. Hi,
    have the same problem as Enrico.

    In additition: When pressing Tab to evaluate, whether autocompletion of path works, sqlcl crashes immediately.

    On your screenshot a “ls” command is visible but on windows neither “ls” nor “dir” is working.

    Regards,
    Dirk

  9. Two issues noticed :
    1. Its not able to read tnsnames.ora if there are multiple alias for the same definition.
    e.g. when my tnsnames.ora has entry like below:
    mydb, mydb.world =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dddd)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = dddd_srvc.world)
    )
    )

    2. If there are multiple tnsnames.ora* files (e.g. prior copies of the file) then it chooses one of them to read.

    3. Compared to sqlplus of yesteryears, it appears to be slow

    Still looks cool though 🙂

  10. Nandakumar Reply

    A small suggestion on info and info+ commands for tables which shows index details. Can you please include VISIBILITY column for indexes? This will be very useful when someone does troubleshooting of production issues where there are more chances that a index is invisible and goes unnoticed.

  11. Hi,

    the new sqlcl command pwd works well, but I have a problem with cd.
    When I try to change directory
    [email protected]:1521/ORASVIL> cd C:\Users\betacom\Documents
    I receive the error:
    CD-001: C:UsersbetacomDocuments non è una directory.

    But If I “escape” the commad all goes well
    [email protected]:1521/ORASVIL>cd C:\\users\\betacom\\documents
    [email protected]:1521/ORASVIL>pwd
    C:\Users\betacom\Documents

    Enrico

Write A Comment