Oracle SQL Developer Command Line (SQLcl)
Date: April 2019
This is a list of Frequently Asked Questions on SQLcl. Additional questions can be submitted through the SQLcl Forum.
What is SQLcl?
SQLcl is a command-line interface for Oracle Database that combines the power of SQL*Plus and SQL Developer.
How do I get started?
Download the .ZIP from OTN. Extract it. Start a terminal/cmd session and run the ‘SQL’ program in the \bin directory. If you have at least a Java Runtime Engine (JRE) 8 on your machine, you’ll be ready to go.
Does it support TNSNames.ORA files for making connections?
Yes. It will search for your TNS files just as SQL Developer does. Use the SHOW TNS command to get see the current TNS status for your SQLcl session.
Can I still use the C[HANGE] command to update the contents of the buffer?
Yes. You can of course also use the new in-line editor.
Does it still support & and && variable substitution?
Yes.
And what about formatting columns with the COL[UMN] command?
Again, yes. In fact, everything you have loved about SQL*Plus for the past 30 years is still available in SQLcl.
How does the History feature work?
Each statement or script you execute is saved as an item in the history. These remain there until it ages out. The history is limited to 100 items. The history persists between SQLcl sessions.
What is ANSICONSOLE?
ANSICONSOLE is one of the SQLFORMAT options. It auto-sizes columns to ‘best fit’ the data based on the max-size of a value for a given page of query results. This can make reading the results of your queries much easier without resorting to using the COL[UMN] command to pre-size columns for your queries.
How can I find out about new commands?
SQLcl supports all of the SQLPlus commands, and has added new ones. You can see the new ones highlighted when you run the ‘HELP’ command.
Will SQLcl ship with Oracle Database?
SQLcl is included with SQL Developer, and SQL Developer ships with all Oracle Database installs. So, yes.
Does it require an Oracle Client?
No. SQLcl is a java applications and by default uses Oracle’s JDBC driver to connect to Oracle Database. However, you can force a ‘thick’ connection via your Oracle Client by using the –oci flag on your connect string.
How do I access the SQL History?
Use your UP or DOWN Arrow keys. Or, use the HISTORY command to get a list of availalble commmands. Use HISTORY <#> to place a history item into your SQLcl buffer.
Can I use SQLcl to Startup or Shutdown my database?
Yes.
Do you have any examples for the new SCRIPT command and how to harness the power of JavaScript with SQLcl?
Yes, check these code samples out on our GitHub project.
Can I use SQLcl commands in SQL Developer?
Yes, remember that SQLcl IS SQL Developer, just with a CLI vs a GUI. You can for example use the DDL and INFO commands in SQL Developer's worksheet, running as a script (F5).