SQLcl: Optimizing Your History Lists

thatjeffsmith SQL Developer 9 Comments

Tell Others About This Story:

Since SQLcl is a command line tool, it’s not super helpful to have a huge command history list. How do I mean? Well, in the SQL Developer GUI, we have a History panel. It allows for sorting, filtering, scrolling, etc. So you can go ‘crazy’ and set your History limit to 1000 if you want. But, in SQLcl, we limit the user to their last 100 commands.

Now there are many commands you’ll be running in SQLcl that you won’t necessarily want to be stored in the history. For example, do you want to reserve 1% of your history to remember how to clear the screen?

So in the latest drop, available today, you can now tell SQLcl NOT to store certain things.

Scroll down to the bottom of the SQL Developer downloads page to get to the 12MB SQLcl package.

But, by default, the history will store EVERYTHING. So you have to take extra step(s) to prevent things going into the history.

Blacklisted Commands

This has changed with the official 4.2 SQLcl Release – please go here to see how to manage your BLACKLIST.

The developers know that there will be lots of questions as they had new features, above and beyond what SQL*Plus offers. So you’ll find lots of good stuff in the HELP.

give us a comma delimited list of commands you don't want to go into your history

give us a comma delimited list of commands you don’t want to go into your history

Ok, so I don’t want to see SET commands or where I go to clear the screen, and a couple of other things.

NEW items won’t go into the history if they start with these words.

NEW items won’t go into the history if they start with these words.

If you already have a SQL History, you’ll need to clear it first, and then going forward these items won’t go in. Or just let them age out…

HISTORY CLEAR — this will clear your history 🙂

Failures

Ok, now maybe you’re going to work on a query. And you know it’s not going to work for awhile. Or, maybe you’re like me and you rarely get the SQL just right on your first try. In that case you MAY not want to save the failures.

It's a toggle. Run it to switch the mode.

It’s a toggle. Run it to switch the mode.

So I’m going to disable bad SQL going into the history.

Remember, by default, we record EVERYTHING.

Remember, by default, we record EVERYTHING.

I don’t think I’ll run in this mode that often. If I do turn it on, it will be on a short term basis, and then I’ll probably turn it off.

Bumping Up your SQLcl History Limit

If you don’t like 100, you can change it.

script
 
   var MultiLineHistory =Java.type("oracle.dbtools.raptor.console.MultiLineHistory"); 
   MultiLineHistory.getInstance().setMaxItems(250);
 
/

Add this to your login.sql script. 250 will be your new max.

Tell Others About This Story:

Comments 9

  1. `help nohistory` shows that the way to use this is to issue `nohistory load` etc. However, it seems you have to use `set nohistory load`

    Also, insert statements seem to be always on the nohistory blacklist (which I consider a bug).

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author
      2. I am on JDK8, but still the error. Advice me in case of any errors.

        C:\Users\179818>java -version
        java version “1.8.0_60”
        Java(TM) SE Runtime Environment (build 1.8.0_60-b27)
        Java HotSpot(TM) Client VM (build 25.60-b23, mixed mode, sharing)

        C:\Users\179818>sql /nolog
        Error: A JNI error has occurred, please check your installation and try again
        Exception in thread “main” java.lang.NoClassDefFoundError: oracle/dbtools/raptor/utils/IExceptionHandler
        at java.lang.Class.getDeclaredMethods0(Native Method)
        at java.lang.Class.privateGetDeclaredMethods(Class.java:2701)
        at java.lang.Class.privateGetMethodRecursive(Class.java:3048)
        at java.lang.Class.getMethod0(Class.java:3018)
        at java.lang.Class.getMethod(Class.java:1784)
        at sun.launcher.LauncherHelper.validateMainClass(LauncherHelper.java:544)
        at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:526)
        Caused by: java.lang.ClassNotFoundException: oracle.dbtools.raptor.utils.IExceptionHandler
        at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
        … 7 more

        C:\Users\179818>

      3. set environment variable in windows and invoke SQLCI got this error, but when navigated to the respective path and invoked SQLCI got connected (but with few warnings)

        C:\Users\179818>sql rajesh/[email protected]
        Error: A JNI error has occurred, please check your installation and try again
        Exception in thread “main” java.lang.NoClassDefFoundError: oracle/dbtools/raptor/utils/IExceptionHandler
        at java.lang.Class.getDeclaredMethods0(Native Method)
        at java.lang.Class.privateGetDeclaredMethods(Class.java:2701)
        at java.lang.Class.privateGetMethodRecursive(Class.java:3048)
        at java.lang.Class.getMethod0(Class.java:3018)
        at java.lang.Class.getMethod(Class.java:1784)
        at sun.launcher.LauncherHelper.validateMainClass(LauncherHelper.java:544)
        at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:526)
        Caused by: java.lang.ClassNotFoundException: oracle.dbtools.raptor.utils.IExceptionHandler
        at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
        … 7 more

        C:\Users\179818>d:

        D:\>cd app\sqlcl

        D:\app\sqlcl>cd bin

        D:\app\sqlcl\bin>sql rajesh/[email protected]
        Dec 24, 2015 7:25:25 PM java.util.prefs.WindowsPreferences
        WARNING: Could not open/create prefs root node Software\JavaSoft\Prefs at root 0x80000002. Windows RegCreateKeyEx(…) returned error code 5.

        SQLcl: Release 4.2.0.15.349.0706 RC on Thu Dec 24 19:25:25 2015

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

        Connected to:
        Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
        With the Partitioning, OLAP and Data Mining options

        [email protected]>

      4. thatjeffsmith Post
        Author
  2. Jeff,

    just downloaded the new build, and got this error while connecting. please help.

    C:\Users\179818>sql /nolog
    Error: A JNI error has occurred, please check your installation and try again
    Exception in thread “main” java.lang.NoClassDefFoundError: oracle/dbtools/raptor/utils/IExceptionHandler
    at java.lang.Class.getDeclaredMethods0(Native Method)
    at java.lang.Class.privateGetDeclaredMethods(Class.java:2701)
    at java.lang.Class.privateGetMethodRecursive(Class.java:3048)
    at java.lang.Class.getMethod0(Class.java:3018)
    at java.lang.Class.getMethod(Class.java:1784)
    at sun.launcher.LauncherHelper.validateMainClass(LauncherHelper.java:544)
    at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:526)
    Caused by: java.lang.ClassNotFoundException: oracle.dbtools.raptor.utils.IExceptionHandler
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    … 7 more

Leave a Reply

Your email address will not be published. Required fields are marked *