This is a similar version of the content I presented to some internal folks here at Oracle last week. I thought you might enjoy it as well.

I’ve ‘optimized’ it for Slideshare – meaning that there are NO animated GIFs, which is sad, but it does read better this way. There’s a YouTube video inside that does have some live demo if you prefer to watch instead of read.

Let us know if you have a any questions, and be sure to get the latest update, which we just pushed out last week.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

48 Comments

  1. Jeff –

    Posted this question in asktom this morning . Then I saw the active discussion here . Thought of posting it here.

    I am trying to test sqlci on a ubuntu 14.0.4 .
    I am able to connect to the database using EZconnect . However , when I issue sql command , I am getting the following error.


    lsb_release -a

    No LSB modules are available.
    Distributor ID: Ubuntu
    Description: Ubuntu 14.04.4 LTS
    Release: 14.04
    Codename: trusty

    SQLcl: Release 18.4 Production on Wed Mar 27 12:28:04 2019

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

    Last Successful login time: Wed Mar 27 2019 12:28:50 -04:00

    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
    and Real Application Testing options

    SQL> select * from dual;
    Exception in thread "main" java.lang.InternalError: java.lang.reflect.InvocationTargetException
    at java.desktop/sun.font.FontManagerFactory$1.run(FontManagerFactory.java:86)
    at java.base/java.security.AccessController.doPrivileged(Native Method)
    at java.desktop/sun.font.FontManagerFactory.getInstance(FontManagerFactory.java:74)
    at java.desktop/sun.font.SunFontManager.getInstance(SunFontManager.java:247)
    at java.desktop/sun.font.FontDesignMetrics.getMetrics(FontDesignMetrics.java:265)
    at java.desktop/sun.font.FontDesignMetrics.getMetrics(FontDesignMetrics.java:251)
    at java.desktop/sun.awt.SunToolkit.getFontMetrics(SunToolkit.java:582)
    at java.desktop/sun.awt.HeadlessToolkit.getFontMetrics(HeadlessToolkit.java:330)
    at oracle.dbtools.db.ResultSetFormatter.getFontMetricsInternal(ResultSetFormatter.java:269)
    at oracle.dbtools.db.ResultSetFormatter.getFontMetrics(ResultSetFormatter.java:219)
    at oracle.dbtools.db.SQLPLUSCmdFormatter.rset2sqlplus(SQLPLUSCmdFormatter.java:1317)
    at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:328)
    at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:302)
    at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:147)
    at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:70)
    at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:798)
    at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:709)
    at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:83)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:404)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:230)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:404)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:415)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1249)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:491)
    Caused by: java.lang.reflect.InvocationTargetException
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
    at java.desktop/sun.font.FontManagerFactory$1.run(FontManagerFactory.java:84)
    ... 25 more
    Caused by: java.lang.NullPointerException
    at java.desktop/sun.awt.FontConfiguration.getVersion(FontConfiguration.java:1262)
    at java.desktop/sun.awt.FontConfiguration.readFontConfigFile(FontConfiguration.java:225)
    at java.desktop/sun.awt.FontConfiguration.init(FontConfiguration.java:107)
    at java.desktop/sun.awt.X11FontManager.createFontConfiguration(X11FontManager.java:719)
    at java.desktop/sun.font.SunFontManager$2.run(SunFontManager.java:367)
    at java.base/java.security.AccessController.doPrivileged(Native Method)
    at java.desktop/sun.font.SunFontManager.(SunFontManager.java:312)
    at java.desktop/sun.awt.FcFontManager.(FcFontManager.java:35)
    at java.desktop/sun.awt.X11FontManager.(X11FontManager.java:56)
    ... 30 more

  2. Is there an option in DDL to create directories that does noe exist when saving files?
    I’m trying to write object-DDL to a file like this:

    var ddl_path = “C:/path”;

    //loop the results
    for (i=0; i < ret.length; i++) {
    var row = ret[i];
    sqlcl.setStmt("DDL "+row.OWNER+"."+row.OBJECT_NAME+" SAVE "+ddl_path+"/"+row.PROJECT+"/"+row.OWNER+"/"+row.OBJECT_TYPE+"/"+row.OBJECT_NAME+".sql");
    sqlcl.run();
    }

    And I get path does not exist for different object types if I haven't already created the directory for all object types from a schema.

    • Bjørn Myklebust

      I also experience problems when I try to specify the type of the object I’m saving. It doesn’t seem to recognize this option.
      From the alphabetic list of sqlcl commands:

      DDL [object_name [type] [SAVE filename]]

      E.g.:
      sqlcl.setStmt(“DDL “+row.OWNER+”.”+row.OBJECT_NAME+” “+row.OBJECT_TYPE+” SAVE “+ddl_path+”/”+row.PROJECT+”/”+row.OWNER+”/”+row.OBJECT_TYPE+”/”+row.OBJECT_NAME+”.sql “);

      If I remove the first +row.OBJECT_TYPE+ from this command it runs ok. But it doesn’t produce files for TABLE PARTITION, INDEX PARTITION etc.

    • Bjørn Myklebust

      I’m trying to export all objects of all object types we have in the database, for each schema, and store these in a directory structure. This is then pushed to git:

      sid
      schema-1
      dimension
      function
      index
      materialized view
      package
      procedure
      sequence
      table
      view
      schema-2… etc

  3. I got the same error at an idle sqlcl prompt:

    SQL> Exception in thread “AWT-EventQueue-0” java.lang.ClassCastException: sun.java2d.HeadlessGraphicsEnvironment cannot be cast to sun.awt.Win32GraphicsEnvironment
    at sun.awt.windows.WToolkit$3.run(Unknown Source)
    at java.awt.event.InvocationEvent.dispatch(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$500(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)

  4. Keerthi Teja Tailam Reply

    Hi Jeff

    I observed a very small issue with SQLCL. when we are keeping it inactive for long time I see below error
    SQL> Exception in thread “AWT-EventQueue-0” java.lang.ClassCastException: sun.java2d.HeadlessGraphicsEnvironment cannot be cast to sun.awt.Win32GraphicsEnvironment
    at sun.awt.windows.WToolkit$5.run(Unknown Source)
    at java.awt.event.InvocationEvent.dispatch(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$300(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)
    And its just error but i still see command line is connected to DB.
    Its not a big one, but just anyway reporting.

    Regards
    Teja

  5. Dave Venus Reply

    Can you also post your slide deck to blog? We can’t access slideshare.
    Thanks!

  6. This is the best piece of software 🙂
    To make it even better please add cygwin support for the rest of us. I just don not like to patch 3-rd party sw everytime I download new version.

    Modify “sql” shell script (quick and dirty solution I use):

    —-
    # cygwin classpath patch
    cygwin=false
    case “`uname`” in
    CYGWIN*) cygwin=true;;
    esac

    if $cygwin; then
    CPLIST=$(cygpath -pw “$CPLIST”)
    fi
    —-

  7. Are there any open-source usage examples available that we could have a look at?

    Speaking of open-source, are any parts of SQLcl open source?

  8. CSV is great but tab delimited is better. We have data with embedded commas, and quotes etc thus we like tab delimited. I hope you will implement tab delimited instead of just csv.

    • thatjeffsmith

      You can have any delimiter you want, with text quoted or not. Just use SQL Developer and the grid > export feature. Set to ‘delimited’ and set your options.

  9. Great, it worked for me after setting the environment variable. Thanks a lot Jeff!

  10. Jeff:

    I’m not able to connect to the database using the connect string which is configured in LDAP. Is LDAP supported in SQLCL?

    Thanks,
    Senthil

    • thatjeffsmith

      Yes

      set LDAPCON jdbc:oracle:thin:@ldap://XYZ.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ldapcdc,dc=lcom

      $export LDAPCON=jdbc:oracle:thin:@ldap://XYZ.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ldapcdc,dc=lcom
      $sql /nolog
      SQLcl: Release 4.1.0 Beta on Fri Feb 20 10:37:02 2015
      Copyright (c) 1982, 2015, Oracle. All rights reserved.
      SQL> connect barry/[email protected]_test(Emily’s Desktop)
      Connected
      SQL>

  11. Herb Swift Reply

    I just set this up today. I’m excited to explore its json format feature. Two things:

    1) I noticed that the json output does not currently escape the ‘”‘ embedded in data.

    2) I haven’t tried yet, but how would I go about customizing the sql prompt?

    • thatjeffsmith

      for example, put this in your login.sql script

      SET sqlprompt “_user ‘@’ _connect_identifier >”

    • Herb Swift

      Wow, that was fast! Thanks for replying.
      I have that in my glogin.sql for both my 11g client and my 12c client. But sqlcl still comes up just SQL>. I tried copying glogin.sql to sqlcl, sqlcl/bin, and sqlcl/lib, just in case it was that easy. But it didn’t change anything. Then I took your advice literally. I created login.sql in sqlcl/bin, and voila! Thanks again.

  12. Thanks for answering Jeff,

    If that import command runs over jdbc that will be fine, even dirty : )

    I hope that solution may simplify cases of exp/imp over inconsistent versions of Oracle (9 to 11). Execution speed is not the matter.

    Thank you again for your answers. Your blog is definitely in my bookmarks.

  13. Hi Jeff,

    Sqlcl looks great, i think i’ll have some use cases with it.

    I worked on a similar personnal project (that i use at work too) that aimed to deploy easily sql operations over shell scripts WITHOUT having to install Oracle Client.

    So thank you and your team for that !

    What about the same approach to exp/imp commands ?

    • thatjeffsmith

      imp – we build a quick and dirty IMPORT command that will read a local CSV file and import it to a table for you
      exp – you can run
      set sqlformat csv
      spool file.csv
      select * from hr.employees;
      spool off

      If you need REAL imp and exp support – use DataPump or SQL*Loader. They are multi-threaded and designed to support large amounts of data for production purposes.

      Quick and dirty, then we got ya taken care of.

  14. I’ve been working on an Oracle command-line tool, some of SQLcl ideas seem similar. Unfortunately I haven’t got much time to spend on it last months… But if anyone wanted to have a look, here it is, enjoy: https://github.com/kmehkeri/oraora

    • kmehkeri

      Of course. Mine is just a small hobby project 🙂 SQLcl looks nice, at least from the presentation, I’m going to take a closer look into it.

  15. I use Cygwin and ammore fimiliar with Unix/Linux style than DOS command. This does not seem to be usable in unix/linux?
    Would be awsome in portred to Unix/Linux…..

    • thatjeffsmith

      Port? It’s all java, so it will run wherever java 7 will run. It works just fine on Oracle Linux.

  16. Latest sqlcl is throwing exception when I use ctas command.

    If the source table is prefixed with schema name.. then I get following
    exception.

    Example: ctas schema.tablename dest_tablename

    Mar 20, 2015 2:57:04 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
    SEVERE: oracle.dbtools.raptor.newscriptrunner.commands.CTAS.handleEvent(CTAS.java:72)
    java.lang.NullPointerException
    at oracle.dbtools.raptor.newscriptrunner.commands.CTAS.handleEvent(CTAS.java:72)
    at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:369)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:195)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:267)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:180)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:208)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:257)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:701)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:320)

  17. I’d like to deploy SQLcl on Linux host where I do not have rights to upgrade JAVA version. How to create a package of SQLcl + JAVA7or8 and make the deployment of SQLcl totally independent from the JAVA version on server?
    Thank you,
    vr

  18. Okay, I love the ddl command (how would you invoke ddl for a schema or tablespace….I have historically used dbms_metadata.get_ddl) and the information command is not too shabby either.

    I have been looking up the various things with the help command too. Where should question be posted as a matter of practice?

    • thatjeffsmith

      that’s what we’re using under the covers, the DMBS_METADATA package.

      to extend it to other objects, which I WANT, we’d have to either parse to guess the object type, or search the DD for it. One is a bit unreliable, the other is expensive.

      But I want to have and eat my cake. So keep your fingers crossed.

  19. A good name for the tool would be SQL*PlusPlus: sql++ or sqlpp

    🙂

  20. On the second slide you say the “preceding” can’t be used etc – I think it should be the “following”, shoudln’t it? 🙂

    I especially like the DBA slide at the end, though…

  21. Jeff, Great idea. I use SQL*Plus with Gvim (and SQL Developer with Gvim) and they both excel at different tasks. This kind of reminds me of how Microsoft released Powershell to provide more up to date technology offering to people whom use command environments. I will kick the tires.

    Thanks!

    • thatjeffsmith

      yeah, except we ALWAYS had a command line interface. now we’re just trying to make that even better. let us know what you think! it’s still beta, and we have tons of bugs to fix yet.

    • I am kicking the tires and giving it a test drive. So far, so good.

Write A Comment