Oracle SQLcl, Why?

thatjeffsmith SQL Developer 19 Comments

Tell Others About This Story:

Oracle SQL*Plus has been around since 1985. It does what it does, and it does it well.

The top new TV show was MacGyver - STILL LOVE THAT SHOW.

The top new TV show was MacGyver – STILL LOVE THAT SHOW.

SQL Developer has been around since 2006. It’s become the de facto IDE and GUI for the Database.

There was an opportunity to build something new that could take the command line interface and enhance it to meet the needs of today’s database professional.

So really the question that our team was faced with wasn’t ‘Why?’ it was..

Why not?

New folks to Oracle aren’t exactly thrilled when they discover most of the help docs and training materials are written to a command-line interface that is completely foreign to them.

Writing a simple SELECT * FROM query often leads to a result-set that trails off the screen and is near-impossible to read.

Easier to read query results, just one little thing you'll fall in love with when using SQLcl.

Easier to read query results, just one little thing you’ll fall in love with when using SQLcl.

Basic things like recalling previous statements or getting help with your query or building new commands to make verbose things like a Create Table as SELECT or esoteric things like generating the DDL for an object – we could make those things available and so much easier.

So why not build that?

And that’s what we’ve done. Hopefully this isn’t a surprise seeing as I’ve been talking about SQLcl for more than a year now.

SQLcl Video || SlideDeck || Forums || Downloads

We’re starting to wind down the Early Adopter process. It’s going to be real, soon.*

* Soon – meaning I can’t say exactly when because legal, because other things, but you’ll see it go GA sooner versus later.

To the bigger questions some of you have been asking – when will this ship with the database and clients, what happens to SQL*Plus itself, etc., a few notes:

  • SQL*Plus isn’t going away
  • SQlcl will be available with your future Database and Client installs
  • As with ALL of our tools – use the ones that make sense for the task at hand

I’m a big fan of choice. Our customers pay more than a little for their database licenses. They shouldn’t have to be limited to just a few, or even one, interface to their Database.

I’m not going to tell you which interface is the best one for you, but I’ll tell you what you need to know so you can make the right decision for yourself.

And who doesn’t like having choices? I usually have a piece of pecan and apple pie at Thanksgiving- just like I normally use both SQL Developer AND SQLcl most days.

Look for more news, demo’s, and a chance to provide feedback directly to us at KScope16 and Oracle Open World 2016.

Tell Others About This Story:

Comments 19

    1. thatjeffsmith Post
      Author
  1. SQLcl: Release 4.2.0.16.131.1023 RC

    I had problems with sqlcl searching for files named tnsnames.* in the users home directory. If you have a backup file tnsnames.ora.0, it may use this instead of tnsnames.ora ! Is this the expected behaviour !

    SQdeveloper allows the use of wallet credentials with the jdbc connector
    jdbc:oracle:oci:/@, can this be done within sqlcl ?

    $ sql -verbose -oci /@XXXX

    SQLcl: Release 4.2.0.16.131.1023 RC on Tue Jun 21 16:45:20 2016

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

    Jun 21, 2016 4:45:20 PM oracle.dbtools.raptor.utils.TNSHelper checkForTns
    INFO: Checking for tnsnames.* in :/home/myaccount
    Jun 21, 2016 4:45:20 PM oracle.dbtools.raptor.utils.TNSHelper getTNSEntries
    INFO: Using TNS info from :/home/myaccount
    Jun 21, 2016 4:45:21 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
    INFO: Attempting to connect using URL= “jdbc:oracle:oci8:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) …
    Jun 21, 2016 4:45:21 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS logConnectionURL
    INFO: Attempting to connect using URL= “jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) …
    Jun 21, 2016 4:45:22 PM oracle.dbtools.raptor.newscriptrunner.SQLPLUS getConnection
    WARNING: ‘OCI’ jdbc connection failed, trying jdbc thin…
    USER =
    URL = jdbc:oracle:oci8:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=XXXXX) (PORT=XXXX)) (CONNECT_DATA =(SERVICE_NAME=XXXX)))
    Error Message = ORA-01017: invalid username/password; logon denied
    USER =
    URL = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=XXXXX) (PORT=XXXX)) (CONNECT_DATA =(SERVICE_NAME=XXXX)))
    Error Message = ORA-01017: invalid username/password; logon denied
    Username? (RETRYING) (‘/@XXXX’?)

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author
  2. Thanks for this, I have a question, I am able to set the title heading of my sqlplus cmd.exe window with the following “host title &[email protected]&_i_conn [sid=&mysid spid=&_i_spid inst=&_i_inst host=&_i_host cpid=&_i_cpid start=&_i_startup]”

    This doesn’t work in sqlcl, is there a way to do the same there please. Thanks
    Anthony

  3. Will it be possible to generate something like ‘schema/database diff’ through cl??
    i think it would be great. Right now i’m using a script i created using dbms_metada/dbms_metadata_diff to generate diff’s through command line in order to automate the deployment between Int and QA db’s.

    i believe if this is possible to be done in a ‘native’ through command-line, a lot more people using agile methodologies would adopt this form of deployment/version control.
    when db is ready for deploy, after all tests, just diff de db’s and you’re ready to go.

    1. thatjeffsmith Post
      Author
      1. even better, would be a tool/set of tools, to address this questions. Or even some guidelines from oracle on versioning//deploying in agile methodologies, ‘best practices’.

        but, as this is not the proper place to discuss this, right now, let me just thank you for the great work you and your team have been putting into oracle sql developer.

        regards.

        1. thatjeffsmith Post
          Author
  4. It’s easy to tell why not 🙂

    SQL*Plus is the standard, the measure of all things and it will stay so until Oracle tells us to run installation/upgrade scripts via SQLcl.
    Have you ever had problems with scripts developed in Toad that didn’t work in SQL*Plus, e.g. because of missing semicolons? We had more than once, mostly scripts from contractors.
    Scripts will go into test/production via SQL*Plus without exception, DBAs won’t allow scripts that are not testet in SQL*Plus because it is the least common denominator, available everywhere and proven to be reliable. Therefore I would have to test my scripts in SQL*Plus after developing them in SQLcl.

    I don’t dislike new tools (I prefer a graphical editor if the alternative is vi 🙂 ) but in this case the benefit doesn’t make up for the necessity to prove the correct execution in SQL*Plus.

    Regards
    Marcus

    1. thatjeffsmith Post
      Author

      We won’t tell you what tools to use.

      And not sure about Toad, what’s that?

      Our Database QA team is running all of the 12cR2 regression and test scripts through SQLcl. SQLcl is still in EA as we find and fix all bugs to make sure that SQL*Plus scripts run as expected in SQLcl.

      If you don’t want to change, no one is expecting, or even asking you to.

      You’re only being given a new choice.

      1. It’s not that I don’t want to change, I tried SQLcl at home for private projects. SQL*Plus is not user friendly and I think that SQLcl would be an enhancement, but it’s company policy that every change to the db has to be done via scripts in SQL*Plus. Introducing a new tool without a good reason** is next to impossible. The inertia of existing ways to do something is similar to that of a crude carrier at maximum speed. You give me a new choice but …

        ** Good reasons like replacing an apparently little known tool named T…d with SQL Developer because of licence costs.

        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author

      SDCLI exposes SQL Dev GUI components like the Cart or Tools > Database Export. Not interactive, basically launches a headless SQLDev at a CLI to do a single thing, and then closes.

      SQLcl is a command-line query interface – truly interactive.

      1. Got it. Thanks. I really was off the mark. I attended your presentation at OOW15 as well on this 🙂

        Maybe you should call it SQL++ 🙂

        (just kidding)

        1. thatjeffsmith Post
          Author

Leave a Reply

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