Oracle SQLcl Slidedeck: Overview of our new command line interface

thatjeffsmith SQL Developer 40 Comments

Tell Others About This Story:

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 40

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

  2. 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

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  3. 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
    —-

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  4. 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.

    1. thatjeffsmith Post
      Author
  5. 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

    1. thatjeffsmith Post
      Author

      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/oracle@orclservice_test(Emily’s Desktop)
      Connected
      SQL>

  6. 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?

    1. thatjeffsmith Post
      Author
      1. 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.

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

    1. thatjeffsmith Post
      Author
  8. 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 ?

    1. thatjeffsmith Post
      Author

      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.

    1. thatjeffsmith Post
      Author
      1. 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.

  9. 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…..

    1. thatjeffsmith Post
      Author
  10. 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)

  11. 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

  12. 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?

    1. thatjeffsmith Post
      Author

      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.

    2. thatjeffsmith Post
      Author
  13. 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…

  14. 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!

    1. thatjeffsmith Post
      Author

      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.

    1. thatjeffsmith Post
      Author

Leave a Reply

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