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

SQLcl: Optimizing Your History Lists

3 Mins Read

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.

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

9 Comments

  1. Norbert Kiesel Reply

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

  2. Niko Reply

    Hey Jeff,

    same error as Rajeshwaran, Jeyabal after the latest deploy the new build.

    • Rajeshwaran, Jeyabal

      Jeff – what error stack do you need ? do the log i posted doesn’t help ?

    • thatjeffsmith Post Author

      My developer tells me you’re trying to use a Java 6 JRE and we need at least a Java 7 JRE

    • Rajeshwaran, Jeyabal

      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>

    • Rajeshwaran, Jeyabal

      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/oracle@ora10g
      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/oracle@ora10g
      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

      rajesh@ORA10G>

  3. Rajeshwaran, Jeyabal Reply

    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

Write A Comment