Oracle SQL*Plus has been around since 1985. It does what it does, and it does it well.
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.
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.
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.
19 Comments
Jeff, would you consider to inlcude 2FA (two factor authentication) in the next version of SQLcl?
AFAIK, two factor authentication with RADIUS should already work – have you tried it?
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’?)
SQL*Plus and SQL Developer do the same thing actually in terms of the tnsname file name wildcarding.
Yes, wallet connections should work. Does this help?
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 &_i_user@&_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
I love sqlcl !
However I can not backgroud the script, see below
——————–
[jichen@sjcpsql01-dev sqlcl]$ /opt/sqlcl/bin/sql lXXX/XXX@//lcdemo.tlcinternal.com:1521/lcdemo.XXX.com @phase1.sql&
[1] 3560
[jichen@sjcpsql01-dev sqlcl]$
[1]+ Stopped /opt/sqlcl/bin/sql XXX/XXX@//lcdemo.tlcinternal.com:1521/lcdemo.XXX.com @phase1.sq
Meaning that I can not automate this script.
Can you please help ?
Jimmy
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.
Will it? Maybe. Just not today. That requires a lot more code than just the script engine required to run query statements.
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.
We agree. It’s on our roadmap. Stay tuned.
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
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.
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.
When they see what you have to offer and you’re the CTO, let me know if you need help setting those new Corp policies 🙂
Hey Jeff Will this replace SDCLi? I’m a bit confused.
Cheers
Graeme
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.
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)
That was my first inclination, but…yeah.