TECHNOLOGY: Command Line Administration

 

The Modern Command Line

By Jeff Smith

 

SQLcl is a new Java-based command-line interface for Oracle Database.

The command-line batch query tool SQL*Plus has been the de facto interface for working with Oracle Database for 30 years. And although newer graphical user interfaces for Oracle Database have become popular and have evolved, the feature set for SQL*Plus has remained fairly static—until now.

The new take on SQL*Plus, SQLcl, is based on the script engine in Oracle SQL Developer and is attached to a Java-based command-line interface. In addition to delivering a more modern way of working on the command line, SQLcl also introduces new commands and features missing from SQL*Plus itself.

This article shows how to install SQLcl, connect to your database, and take advantage of the five most compelling features in SQLcl. It assumes that you are familiar with SQL*Plus and Oracle SQL Developer.

Getting Started

Download SQLcl from the Oracle SQL Developer product page on the Oracle Technology Network.

SQLcl supports connections via EZConnect, TNS, LDAP, TWO_TASK, and more—and all without an Oracle client installed or configured.

For example, I can connect to my local Oracle Database 12c pluggable database (PDB) by using the following EZConnect syntax:

'sql user@//server:port/service' 

If you have a TNSNAMES.ORA file available, you can also reference connections defined there. To make the file available to SQLcl, set your TNS_ADMIN environment variable.

For the examples in this article, I’ll be using the sample HR schema included with Oracle Database.

Top Five

There are many features in SQLcl that experienced SQL*Plus users will want to explore. This article looks at the five most compelling ones.

Feature 1: An inline editor. In SQL*Plus, when you want to edit the text of the current query or PL/SQL block, you need to launch an external editor with the EDIT command. In the modern world, a command-line interface can allow for basic keyboarding, such as using arrow keys and the Backspace key.

With that in mind, in SQLcl you can now use your arrow keys to move back, up, down, and all around your text directly at the cursor and can also hold down the Backspace key to delete your query beyond the current line of your buffer.

As you arrow up through the text, the current line is marked with an asterisk by the line number. When your edits are complete, you can use the Ctrl+R key sequence to execute the entire text.

Additionally, there are quick navigation keys, such as:

• Ctrl+W. Go to the top of the buffer.
• Ctrl+S. Go to the bottom of the buffer.
• Ctrl+A. Go to the start of a line.
• Ctrl+E. Go to the end of a line.

These commands are documented in the Help text for the EDIT command. At a SQLcl command prompt, execute HELP EDIT to see the full command instructions.

The default editor for SQLcl is this inline editor, but if you prefer another, you can set SQLcl to use it instead.

Feature 2: History. It’s likely that you will want to rerun a query you have already executed or run a variation of one of your queries. Many SQL*Plus users figured out that they could access previous queries by taking advantage of rlwrap. This wasn’t set up by default, however, and it was available only to Linux and UNIX users. In SQLcl your queries and scripts are recorded and ready for playback by default.

Just as in Oracle SQL Developer, SQLcl stores the previous 100 statements or scripts.

Additionally, your query history is maintained from one session to the next and older queries age out as the history limit of 100 entries is met.

You can access your query history in two ways:

1. Cycle through the history by using your up and down arrow keys.
2. Access the full history list with the HISTORY command.

In addition to the HISTORY command, the following commands provide more information and operations:

• HISTORY USAGE. View the history usage.
• HISTORY TIME. View the time spent executing each statement.
• HISTORY CLEAR. Clear the history.

As you use the arrow keys to navigate the history list, SQLcl will paint the text of the query at the command prompt. After you have recalled a statement, you can also edit it by using the arrow keys.

Feature 3: Formatting. In SQL*Plus you write custom code to transform your query output into comma- or tab-delimited format. In SQLcl you can return your query output in the desired format by using the SET SQLFORMAT command.

The formats supported in SQLcl include

• ANSICONSOLE
• CSV
• Delimited
• Fixed-width
• HTML
• INSERT statements
• JSON
• SQL*Loader
• Text
• XML

Here’s an example that returns a query to JSON format:

SQL>set sqlformat json

SQL>select * from hr.employees fetch first 1 rows only;

{"items":[
{"employee_id":198,"first_name":"Donald","last_
name":"OConnell","email":"DOCONNEL","phone_
number":"650.507.9833","hire_date":"21-JUN-99","job_id":"SH_
CLERK","salary":2600,"manager_id":124,"department_id":50}]}

Note that the FETCH FIRST support in Oracle Database 12c is required for this query.

When you define a format, all query output will be formatted as requested. To disable the formatting, run the set sqlformat command without a format.

Next Steps    DOWNLOAD the SQLcl command-line tool.”   LEARN more about SQLcl.

Feature 4: Tab completion. Given a contest between more typing or less typing, less typing should be the clear winner. In addition to the inline editor feature and the HISTORY command, SQLcl gives you another opportunity to do a little less typing. If you need to include a table or a column in a query, press the Tab key for assistance.

For example, in the following, after typing “where D,” I press the Tab key.

SQLcl automatically completes the text to the lowest common match of available columns in the HR.DEPARTMENTS table.

I type “N” and press the Tab key again.

SQLcl automatically completes the typing up to DEPARTMENT_NAME.

Feature 5: New commands. Several new SQLcl features and commands extend what’s available in SQL*Plus. New commands for SQL*Plus users include

• ALIAS
• APEX
• BRIDGE
• CD
• CTAS
• DDL
• FORMAT
• HISTORY
• INFORMATION
• LOAD
• NET
• REPEAT
• SSHTUNNEL
• TNSPING

Run HELP <command> to print the full text of each command description, along with examples of how to use it.

For example, for the CTAS command, enter

help CTAS

 

and SQLcl returns information about CTAS.

---

Jeff Smith is a senior principal product manager in Oracle’s Database Development Tools group and responsible for Oracle SQL Developer and Oracle SQL Developer Data Modeler. He specializes in database tools and development environments.

 

출처 : http://www.oracle.com/technetwork/issue-archive/2015/15-sep/o55sql-dev-2692807.html